| re: Create Key Field in VBA
<DaveGriffiths70@gmail.com> wrote in message
news:1145634206.910799.248050@e56g2000cwe.googlegr oups.com...[color=blue]
>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[/color]
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
| 
