by Bbholl (1 Submission)
Category: Databases/Data Access/DAO/ADO
Compatability: Visual Basic 5.0
Difficulty: Unknown Difficulty
Originally Published: Thu 27th April 2000
Date Added: Mon 8th February 2021
Rating: (1 Votes)
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"
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
No comments have been posted about This code creates an Access database file with three tables. All fields in the tables are set to Y. Why not be the first to post a comment about This code creates an Access database file with three tables. All fields in the tables are set to Y.