In VB5, Microsoft Jet to Access 97, CREATE TABLE with AutoNumber PRIMARY KEY

[Helmut Forren]

I'm fairly familiar with SQL in general, as well as with
Access in general, but I haven't done too much SQL in VB5,
using the Microsoft Jet Engine (I assume), to access an
Access 97 database.

I MUST leave the database in Access 97, and I MUST add a
new table from my VB5 program. The primary key needs to
be of type Autonumber (Long Integer). This is all in a
program I've inherited.

I've gotten as far as the following code, to add a long
integer primary key that is NOT autonumbered or
AUTO_INCREMENT:

Public gdbRaceBase As Database
...
Dim strSQL As String
...
strSQL = "CREATE TABLE tblMaintenance _
(lMaintenance_id Integer NOT NULL)"
gdbRaceBase.Execute strSQL
strSQL = "ALTER TABLE tblMaintenance ADD CONSTRAINT _
lMaintenance_id_key PRIMARY KEY (lMaintenance_id)"
gdbRaceBase.Execute strSQL

So, the above creates the table with the primary key as
long integer. Now what SQL statement do I use, or how do
I modify the above, to make that key AutoNumber like it
would be were I allowed to simply modify the database from
the Access IDE?

Thanks very much,
Helmut

 

[Douglas J. Steele]

Rather than using the SQL ALTER TABLE approach, try using DAO's
CreateTableDef, CreateField and CreateIndex methods.

See http://www.mvps.org/access/tables/tbl0016.htm at "The Access Web" for an
example.

 

[Helmut Forren]

Thanks very much for your help, Doug. It has led me to a
trivial solution.

Note that I looked at the ref you gave, and it also
pointed to SQL DDL option at
http://support.microsoft.com/default.aspx?
scid=http://support.microsoft.com:80/support/kb/articles/q1
16/1/45.asp&NoWebContent=1

Here, I found a much more trivial solution to my problem,
consistent with other code already using SQL. (This is as
opposed to mixing SQL and DAO.)

First, it let me combine the field creation and the
setting as primary key. Second, it allowed me to set the
autonumber attribute via the "COUNTER" keyword. The
resulting code that works is:

Public gdbRaceBase As Database
...
Dim strSQL As String
...
strSQL = "CREATE TABLE tblMaintenance _
(lMaintenance_id COUNTER _
lMaintenance_id_key PRIMARY KEY)"
gdbRaceBase.Execute strSQL

Note, of course, thate the "COUNTER" keyword isn't
anywhere close to any standard SQL keyword I know of, such
as "AUTO_INCREMENT".

-Helmut

 

[Tim Ferguson]

Note, of course, thate the "COUNTER" keyword isn't
anywhere close to any standard SQL keyword I know of, such
as "AUTO_INCREMENT".

But it is documented in the help files; actually AUTOINCREMENT is a synonym
but it may have come in with Jet 3.6...


Tim F

 

[Helmut Forren]

AH YES!!!! Where are those help files, anyway?

It's NOT in the help I get when I click "Help" from VB5.

It's NOT in the help I get when I click "Help" from Access
2000 (that's installed and being used to LOOK at Access 97
files).

So where is the darned doc? If I had the doc SPECIFICALLY
for SQL DDL, like would be MUCH, MUCH easier!!

Thanks,
-Helmut

 

[Tim Ferguson]

So where is the darned doc? If I had the doc SPECIFICALLY
for SQL DDL, like would be MUCH, MUCH easier!!

Look for SQL reserved words, SQL and Jet datatypes, equivalences between
ANSI SQL and Jet SQL... For me, I can look up Access 2000 help; enter SQL
in the index and a logn way down is "Equivalent ANSI SQL Data types"...;
and find COUNTER and AUTOINCREMENT in the table and in the notes.

Hey, don't blame me -- I hate this new html-help stuff as much as everyone
else!

B Wishes



Tim F