Access - Create Customers Table With Data Access Objects (DAO)

This macro creates a customers table in MS Access database with DAO.

Access

4683
2618
0
0

Add to collection

1 Open MS Access.

2 Create a blank database.

3 Go to Database Tools tab > click on "Visual Basic" button or hit Alt + F11.

4 Go to Insert tab > click on "Module" or hit M.

5 Copy the VBA code from below.

6 Paste the code in the newly created module.

7 Go to Run tab > click on "Run Sub/UserForm" or hit F5.

8 That's it!

Code

Option Compare Database
'==========================================
' Create Customers Datatable With DAO
'==========================================
Sub createCustomersTableDAO()
    ' Set variables
    Dim db As Database
    Dim tbl As TableDef
    Dim fld As Field
    ' Set database
    Set db = CurrentDb
    ' Set table
    Set tbl = db.CreateTableDef
    tbl.Name = "tblCustomers"
    ' Set fields
    Set fld = tbl.CreateField("ID", dbLong)
    fld.Attributes = dbAutoIncrField
    tbl.Fields.Append fld
    Set fld = tbl.CreateField("Customer_Number", dbText, 255)
    tbl.Fields.Append fld
    Set fld = tbl.CreateField("First_Name", dbText, 255)
    tbl.Fields.Append fld
    Set fld = tbl.CreateField("Last_Name", dbText, 255)
    tbl.Fields.Append fld
    Set fld = tbl.CreateField("Full_Name", dbText, 255)
    tbl.Fields.Append fld
    Set fld = tbl.CreateField("Username", dbText, 255)
    tbl.Fields.Append fld
    Set fld = tbl.CreateField("Company_Name", dbText, 255)
    tbl.Fields.Append fld
    Set fld = tbl.CreateField("Job_Title", dbText, 255)
    tbl.Fields.Append fld
    Set fld = tbl.CreateField("Email", dbText, 255)
    tbl.Fields.Append fld
    Set fld = tbl.CreateField("Phone", dbText, 255)
    tbl.Fields.Append fld
    Set fld = tbl.CreateField("Country", dbText, 255)
    tbl.Fields.Append fld
    Set fld = tbl.CreateField("Country_Code", dbText, 255)
    tbl.Fields.Append fld
    Set fld = tbl.CreateField("City", dbText, 255)
    tbl.Fields.Append fld
    Set fld = tbl.CreateField("Postal_Code", dbText, 255)
    tbl.Fields.Append fld
    Set fld = tbl.CreateField("Gender", dbText, 255)
    tbl.Fields.Append fld
    Set fld = tbl.CreateField("isActivated", dbBoolean)
    tbl.Fields.Append fld
    Set fld = tbl.CreateField("Created_At", dbDate)
    tbl.Fields.Append fld
    Set fld = tbl.CreateField("Updated_At", dbDate)
    tbl.Fields.Append fld
    ' Append table
    db.TableDefs.Append tbl
    ' Release objects
    Set fld = Nothing
    Set tbl = Nothing
    Set db = Nothing
End Sub