By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 1,221 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

Create Key Field in VBA

P: n/a
I can create a table in VBA using standard DAO, but I have a problem
with my ID field. I make it a long integer, that bit's easy, but I
can't see how to make that field the primary key field, with
autonumber, and with new values set to random. Can anyone help on this
please.

Thanks
Dave

Apr 21 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
<Da*************@gmail.com> wrote in message
news:11**********************@e56g2000cwe.googlegr oups.com...
I can create a table in VBA using standard DAO, but I have a problem
with my ID field. I make it a long integer, that bit's easy, but I
can't see how to make that field the primary key field, with
autonumber, and with new values set to random. Can anyone help on this
please.

Thanks
Dave

Public Sub CreateTestDb()

On Error GoTo Err_Handler

Dim strPath As String
Dim wks As DAO.Workspace
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim idx As DAO.Index

strPath = "C:\MyTest.mdb"

If Dir(strPath) <> "" Then
Kill strPath
End If

Set wks = DBEngine.CreateWorkspace("Jet", "Admin", "", dbUseJet)

Set dbs = wks.CreateDatabase(strPath, dbLangGeneral)

Set tdf = dbs.CreateTableDef("tblTest")

Set fld = tdf.CreateField("ID", dbLong)
fld.Attributes = dbAutoIncrField
tdf.Fields.Append fld

Set idx = tdf.CreateIndex("PrimaryKey")
idx.Primary = True

Set fld = idx.CreateField("ID")
idx.Fields.Append fld

tdf.Indexes.Append idx
Set fld = Nothing

Set fld = tdf.CreateField("F1", dbText, 255)
fld.Required = True
fld.AllowZeroLength = False
tdf.Fields.Append fld

dbs.TableDefs.Append tdf

dbs.TableDefs.Refresh

tdf.Fields("ID").DefaultValue = "GenUniqueID()"

Exit_Handler:

If Not idx Is Nothing Then
Set idx = Nothing
End If

If Not fld Is Nothing Then
Set fld = Nothing
End If

If Not tdf Is Nothing Then
Set tdf = Nothing
End If

If Not dbs Is Nothing Then
dbs.Close
Set dbs = Nothing
End If

If Not wks Is Nothing Then
wks.Close
Set wks = Nothing
End If

Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub
Apr 21 '06 #2

P: n/a
brilliant - thanks a million, works perfectly

Apr 21 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.