Creating a table using VBA

[Lally3]

Hello all,

I have type the following code in a new module. Whenever I try to access that particular module I get the following error message:

"User-defined type not defined"

I have tried to define the type, but I have been unsuccessful. How can I go about to amend this problem.

Any help will be helpful.

Thank You

Jagdeep Singh

Sub NewDatabase()
Dim wspDefault As Workspace, dbs As Database
Dim tdf As TableDef, fld1 As Field, fld2 As Field
Dim idx As Index, fldIndex As Field

Set wspDefault = DBEngine.Workspaces(0)
' Create new, encrypted database.
Set dbs = wspDefault.CreateDatabase("Newdb.mdb", _
dbLangGeneral, dbEncrypt)
' Create new table with two fields.
Set tdf = dbs.createtableDef("Contacts&quot
Set fld1 = tdf.CreateField("ContactID", dbLong)
fld1.Attributes = fld1.Attributes + dbAutoIncrField
Set fld2 = tdf.CreateField("ContactName", dbText, 50)
' Append fields.
tdf.Fields.Append fld1
tdf.Fields.Append fld2
' Create primary key index.
Set idx = tdf.CreateIndex("PrimaryKey&quot
Set fldIndex = idx.CreateField("ContactID", dbLong)
' Append index fields.
idx.Fields.Append fldIndex
' Set Primary property.
idx.Primary = True
' Append index.
tdf.Indexes.Append idx
' Append TableDef object.
dbs.TableDefs.Append tdf
dbs.TableDefs.Refresh
Set dbs = Nothing
End Sub

 

[beetee]

1) make sure the Microsoft DAO 3.6 option is checked in Tools:References from the Microsoft Visual Basic menu.

2) you may need to preceed DAO related items with a DAO. prefix, e.g. Dim MyField as DAO.Field

 

[Dor100]

Is there a way to not have to always use that DAO. prefix?

 

[beetee]

I think it may have to do with the order the references appear in your tools:references menu.

I'm guessing that if ADO is first, it will assume ADO objects for objects of the same name.

If DAO is first, it will assume DAO.

 

[Lally3]

Thank You. I can now create tables.