Help needed in creating a table inside db usingVBA

[tony007]

Hi experts. i wrote this code that supposed to create table inside the access db but when i run it i

get this error:

compile error:
user -defined type not defined for Dim db as Database


It is a command button that calls CreateDatabase. I be happy if some one help me fix this. Thanks



My code:

Option Compare Database



Public Sub CreateDatabase()

' Routines to create the tables.

CreateTablePlayers

'CreateTablePenalties

' Routine to create relations for the tables.

CreateRefInt

End Sub



Private Sub CreateTablePlayers()

' Local variables

Dim db As Database

Dim tbl As TableDef

Dim fld As Field

Dim idx As Index

Set db = CurrentDb()

Set tbl = db.CreateTableDef("Players")

' Adding fields

Set fld = tbl.CreateField("playerno", dbInteger, 0)

fld.Required = True

tbl.Fields.Append fld

Set fld = tbl.CreateField("name", dbText, 25)

fld.Required = True

tbl.Fields.Append fld



Set fld = tbl.CreateField("initials", dbText, 3)

fld.Required = True

tbl.Fields.Append fld

Set fld = tbl.CreateField("birth_date", dbDate, 0)

fld.Required = False

tbl.Fields.Append fld

Set fld = tbl.CreateField("sex", dbText, 1)

fld.Required = True

tbl.Fields.Append fld

Set fld = tbl.CreateField("joined", dbInteger, 16)

fld.Required = False

tbl.Fields.Append fld

Set fld = tbl.CreateField("leagueno", dbText, 4)

fld.Required = False

tbl.Fields.Append fld



' Adding primary key

Set idx = tbl.CreateIndex("Players_PK")

idx.Primary = True

idx.Unique = True

Set fld = idx.CreateField("playerno")

idx.Fields.Append fld

tbl.Indexes.Append idx



' Add Table to the database

db.TableDefs.Append tbl

End Sub



' Adding Referential Integrity

Private Sub CreateRefInt()

' Local variables

Dim db As Database

Dim fld As Field

Dim rel As Relation

Set db = CurrentDb()

Set rel = db.CreateRelation("PlayersPenaltiesRel", "Players", "Penalties")

rel.Attributes = dbRelationUpdateCascade

Set fld = rel.CreateField("playerno")

fld.ForeignName = "playerno"

rel.Fields.Append fld

db.Relations.Append rel

End Sub



Private Sub Command1_click()
CreateDatabase
End Sub

[Br1an_g]

looks like you just need to add the relative reference file.

whilst in vba, go into:

Tools>References and select:

Microsoft DAO x.x Object Library

the x.x will differ depending on installed version of access.

HTH

[tony007]

Many thanks to u . Problem got solved. thanks