If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using ADOX to create a table having AutoNumber
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...b;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 |
#2
|
|||
|
|||
Using ADOX to create a table having AutoNumber
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). -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "genojoe" wrote in message ... 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...b;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 |
#3
|
|||
|
|||
Using ADOX to create a table having AutoNumber
"=?Utf-8?B?Z2Vub2pvZQ==?=" wrote in
: 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. 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 |
#4
|
|||
|
|||
Using ADOX to create a table having AutoNumber
Thanks
Your answer indirectly pointed me to the following site. http://support.microsoft.com/default...b;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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Add New Field to DB | Karen | Database Design | 7 | October 19th, 2005 08:03 PM |
Multiple Options Group | Patty Stoddard | Using Forms | 19 | August 4th, 2005 02:30 PM |
Seeking some expert advice. | HD87glide | Using Forms | 14 | March 23rd, 2005 10:11 PM |
Access 2000, autonumber fields | Zyberg74 | General Discussion | 3 | November 17th, 2004 04:24 PM |
Autonumber | Ally H. | General Discussion | 7 | August 27th, 2004 04:51 PM |