• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• This code creates an Access database file with three tables. All fields in the tables are set to Y

This code creates an Access database file with three tables. All fields in the tables are set to Y

This code creates an Access database file with three tables. All fields in the tables are set to "Yes (Duplicates Ok)" except for the "ID"

Rate This code creates an Access database file with three tables. All fields in the tables are set to Y

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

Dim db As Database

Set ws = DBEngine.Workspaces(0)
Set db = ws.CreateDatabase(App.Path & "\Database.mdb", dbLangGeneral)
    
Dim dbsNorthwind As Database
Dim tdfNew As TableDef
Dim tdfNew2 As TableDef
Dim tdfNew3 As TableDef
Dim idxNew As Index
Dim idxLoop As Index
Dim fldLoop As Field
Dim prpLoop As Property

Set dbsNorthwind = OpenDatabase(App.Path & "\Database.mdb")

Set tdfNew = dbsNorthwind.CreateTableDef("Name")
    tdfNew.Fields.Append tdfNew.CreateField("ID", dbLong)
    tdfNew.Fields("id").Attributes = tdfNew.Fields("id").Attributes + dbAutoIncrField
    tdfNew.Fields.Append tdfNew.CreateField("First Name", dbText)
    tdfNew.Fields.Append tdfNew.CreateField("Last Name", dbText)
    dbsNorthwind.TableDefs.Append tdfNew
    
Set tdfNew2 = dbsNorthwind.CreateTableDef("Phone Number")
    tdfNew2.Fields.Append tdfNew2.CreateField("ID", dbLong)
    tdfNew2.Fields("id").Attributes = tdfNew2.Fields("id").Attributes + dbAutoIncrField
    tdfNew2.Fields.Append tdfNew2.CreateField("First Name", dbText)
    tdfNew2.Fields.Append tdfNew2.CreateField("Last Name", dbText)
    tdfNew2.Fields.Append tdfNew2.CreateField("Number", dbText)
    dbsNorthwind.TableDefs.Append tdfNew2

Set tdfNew3 = dbsNorthwind.CreateTableDef("Email Address")
    tdfNew3.Fields.Append tdfNew3.CreateField("ID", dbLong)
    tdfNew3.Fields("id").Attributes = tdfNew3.Fields("id").Attributes + dbAutoIncrField
    tdfNew3.Fields.Append tdfNew3.CreateField("First Name", dbText)
    tdfNew3.Fields.Append tdfNew3.CreateField("Last Name", dbText)
    tdfNew3.Fields.Append tdfNew3.CreateField("E-Mail", dbText)
    dbsNorthwind.TableDefs.Append tdfNew3

With tdfNew
    Set idxNew = .CreateIndex("IDIndex")
        idxNew.Fields.Append idxNew.CreateField("ID")
        idxNew.Primary = True
        .Indexes.Append idxNew
    Set idxNew = .CreateIndex("FirstNameIndex")
        idxNew.Fields.Append idxNew.CreateField("First Name")
        .Indexes.Append idxNew
    Set idxNew = .CreateIndex("LastNameIndex")
        idxNew.Fields.Append idxNew.CreateField("Last Name")
        .Indexes.Append idxNew
        
With tdfNew2
    Set idxNew = .CreateIndex("IDIndex")
        idxNew.Fields.Append idxNew.CreateField("ID")
        idxNew.Primary = True
        .Indexes.Append idxNew
    Set idxNew = .CreateIndex("FirstNameIndex")
        idxNew.Fields.Append idxNew.CreateField("First Name")
        .Indexes.Append idxNew
    Set idxNew = .CreateIndex("LastNameIndex")
        idxNew.Fields.Append idxNew.CreateField("Last Name")
        .Indexes.Append idxNew
    Set idxNew = .CreateIndex("NumberIndex")
        idxNew.Fields.Append idxNew.CreateField("Number")
        .Indexes.Append idxNew


With tdfNew3
    Set idxNew = .CreateIndex("IDIndex")
        idxNew.Fields.Append idxNew.CreateField("ID")
        idxNew.Primary = True
        .Indexes.Append idxNew
    Set idxNew = .CreateIndex("FirstNameIndex")
        idxNew.Fields.Append idxNew.CreateField("First Name")
        .Indexes.Append idxNew
    Set idxNew = .CreateIndex("LastNameIndex")
        idxNew.Fields.Append idxNew.CreateField("Last Name")
        .Indexes.Append idxNew
    Set idxNew = .CreateIndex("EmailIndex")
        idxNew.Fields.Append idxNew.CreateField("E-Mail")
        .Indexes.Append idxNew

    Debug.Print .Indexes.Count & " Indexes in " & _
        .Name & " TableDef"

    For Each idxLoop In .Indexes

        With idxLoop
            Debug.Print "    " & .Name

            Debug.Print "        Fields"
            For Each fldLoop In .Fields
                Debug.Print "            " & fldLoop.Name
            Next fldLoop

            Debug.Print "        Properties"

            For Each prpLoop In .Properties
                Debug.Print "            " & prpLoop.Name & _
                    " = " & IIf(prpLoop = "", "[empty]", _
                    prpLoop)
            Next prpLoop
        End With

    Next idxLoop

End With
End With
End With

dbsNorthwind.Close