Autonumber fields

[Jim Jones]

Some more needed for a VBA novice!

In my code, I create tables by using CREATE TABLE in a DoCmd.RunSQL
statement. This has worked well - until today when I need an Autonumber
field. What's the best way of creating a table with the first field as an
autonumber?

Any help would be much appreciated - as ever!

Jim Jones
Botswana

 

[Allen Browne]

Use COUNTER as the field type in your string.

This kind of thing:
CREATE TABLE Table1
(Table1ID COUNTER CONSTRAINT PrimaryKey PRIMARY KEY, Field2 TEXT(50);

For a list of the field type names to use in DDL queries as compared to the
Access interface, DAO, and ADOX, this link contains a reference comparision:
Field type names
at:
http://allenbrowne.com/ser-49.html

 

[RoyVidar]

Some more needed for a VBA novice!

In my code, I create tables by using CREATE TABLE in a DoCmd.RunSQL
statement. This has worked well - until today when I need an
Autonumber field. What's the best way of creating a table with the
first field as an autonumber?

Any help would be much appreciated - as ever!

Jim Jones
Botswana

The following should work

docmd.RunSQL "CREATE TABLE mytable (id COUNTER, test TEXT(25))"

For more info, lookup the COUNTER datatype in this article (watch for
linebreaks in the link)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp

Some of these needs to be executed through ADO, for instance

Currentproject.Connection.Execute _
"CREATE TABLE mytable (id INT IDENTITY, test Varchar(25))"

 

[Jim Jones]

Allen

Many thanks indeed. I was unaware of COUNTER as a field type.

Jim Jones

 

[Jim Jones]

Roy

Many thanks indeed - thanks also for the website reference.

Jim Jones