How to create table with an AUTOINCREMENT field using SQL?

[Guest_imported]

Hi All,

I want to create an Access table using SQL command.
The table include an AUTOINCREMENT field and a field with foreign key.

These two fields' datatype is Long, but I don't know how to define AUTOINCREMENT and a foreign key.

My SQL like:

strSql="Create table " & tab_name &_
"(" &_
"msgId Long AUTOINCREMENT Primary Key Not Null, " &_
"parentId Long Foreign Key Not Null, " &_
&quot"

Set adoConn = Server.CreateObject("ADODB.Connection&quot
adoConn.Open "Driver={Microsoft Access Driver (*.mdb)};" &_
"dbq=" & Server.MapPath("MyDB.mdb&quot

set adoRs = adoConn.execute(strSql)

It runs error. Can anyone know how to make the SQL command?

Thanks in advanced

Lianbo

 

[tlbroadbent]

Use Identity to create a an auto incrementing column.

Create table MyTable
(RecID Int Identity, PartNo varchar(16), ...)

You can specift an identity seed and increment value, also.

Create table MyTable
(RecID Int Identity(101,1), PartNo varchar(16), ...)
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.