Create Key Field in VBA

[DaveGri...@gmail.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

[Anthony England]

<DaveGri...@gmail.com> wrote in message
news:1145634206.9...@e56g2000cwe.googlegroups.com...

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

[Dave G @ K2]

brilliant - thanks a million, works perfectly