Creating Indexes in DAO Code (2000)

Topic

New Reply

I am creating tables in a backend db in code using DAO –
Set fld = tbl.CreateField(strFieldName, dbText, 50)
tbl.Fields.Append fld
but I need to set the Primary key(s) and indexes. Can someone help with code to do this please.

Reply | Quote

Replies

Here is an example of DAO code to create indexes, with lots of comments. If you paste this code into a module, you can click on words and press F1 to get help on them.

Sub CreateIdx()
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Dim fld As DAO.Field

On Error GoTo ErrHandler

‘ Set reference to database and table
Set dbs = CurrentDb
Set tdf = dbs.TableDefs(“tblSomething”)

‘ Create a new index
Set idx = tdf.CreateIndex(“PrimaryKey”)
‘ Create field for key – it must exist in the table
Set fld = idx.CreateField(“Field1”)
‘ Add it to the index
idx.Fields.Append fld
‘ Make this index the primary index
idx.Primary = True
‘ Add it to the table
tdf.Indexes.Append idx

‘ Create another new index
Set idx = tdf.CreateIndex(“OtherKey”)
‘ Create two fields for this index and add them
‘ In other words, this is a composite index
Set fld = idx.CreateField(“Field2”)
idx.Fields.Append fld
Set fld = idx.CreateField(“Field3”)
idx.Fields.Append fld
‘ Make it a unique key (but not primary)
idx.Unique = True
‘ Add it to the table
tdf.Indexes.Append idx

ExitHandler:
‘ Clean up
Set fld = Nothing
Set idx = Nothing
Set tdf = Nothing
Set dbs = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

Reply | Quote

Thanks for the great reply

Reply | Quote