Using ADOX to create a table having AutoNumber

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to create a table using ADOX and make a field a Primary Key,
have it AutoNumber and set its start point at 100.

The following link (
http://support.microsoft.com/default.aspx?scid=kb;en-us;252908 ) shows me how
to create the table but does not show me how to handle the Autonumber needs.
I cannot find any source within Data Definition Language or ADOX that
discusses this specific topic. It seems to be a black hole. Can someone
give me a good reference source for using either ADOX or DATA Definition
Language with Access that goes beyond simple, generic examples?

I have searched the following without success:
C:\Program Files\Microsoft SQL Server\80\Tools\Books\mdacxml.chm
C:\Program Files\Common Files\Microsoft Shared\OFFICE11\1033\ADO210.CHM
ACMAIN11.CHM
VBAAC10.CHM
 
Carl Prothman shows how to set an field to Autonumber using ADOX at
http://www.carlprothman.net/Default.aspx?tabid=101#Q3

Unfortunately, there's a problem with his HTML, so it's almost illegible.

Here's what his code actually is:

Dim oCat As ADOX.Catalog
Dim oTable As ADOX.Table
Dim oColumn As ADOX.Column
Dim oKey As ADOX.Key

' Delete any previous temp file
On Error Resume Next
Kill ".\new35.mdb"
On Error GoTo 0

' Create a new database in 3.5 format (Access 97)
Set oCat = New ADOX.Catalog
oCat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\new35.mdb;" & _
"Jet OLEDB:Engine Type=4;"

' Create a new Table
Set oTable = New ADOX.Table
oTable.Name = "WebSite"
oCat.Tables.Append oTable

' Create a new AutoNumber ID Column
Set oColumn = New ADOX.Column
With oColumn
.Name = "WebSiteID"
.Type = adInteger
Set .ParentCatalog = oCat
' Must set before setting properties
.Properties("Autoincrement") = True
End With
oCat.Tables("WebSite").Columns.Append oColumn

' Create a new Primary Key for the table
Set oKey = New ADOX.Key
With oKey
.Name = "PrimaryKey"
.Type = adKeyPrimary
.RelatedTable = "WebSite"
.Columns.Append "WebSiteID"
End With
oCat.Tables("WebSite").Keys.Append oKey

Sorry, I don't have an answer for setting the starting point in ADOX (you
could insert a dummy record with a value of 99).
 
I would like to create a table using ADOX and make a field a Primary
Key, have it AutoNumber and set its start point at 100.
Click to expand...

It's easier in ADO:

adoSQL = "create table mytable (" & _
"MyCounter integer not null identity(100,1) " & _
" constraint primary key, " & _
"MyOtherField varchar(12) null " & _
")"

myconnection.execute adoSQL


Off hand the parameters for the IDENTITY() clause might be backwards, but
it's easy enough to look them up in help.

Best wishes


Tim F
 
Thanks

Your answer indirectly pointed me to the following site.

http://support.microsoft.com/default.aspx?scid=kb;en-us;275252

Still would like to find comprehensive documentation about ADOX.
Specifically, I would like to see a listing of all words that can be used
with Properties(). Had I been able to find that listing, solving my problem
would have been simple.

For Reference, here are two ways that you can set the starting point.

sRecID = 200 'recID is name of AutoNumber column.
sSQL = "ALTER TABLE RTF ALTER COLUMN recID COUNTER(" & sRecID & ",1)"
or
col.Properties("Seed").Value = sRecID
 
Back
Top