by Hema (4 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: Visual Basic 5.0
Difficulty: Unknown Difficulty
Originally Published: Fri 8th August 2003
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
To create Access database at runtime
API Declarations
'-----------Decalarations----------------
Dim ws As Workspace 'obj for workspace
Dim DB As Database 'obj for database
'----------------------------------------
VBcoders
1)Set a reference to the
Microsoft DAO 2.5/3.5 compatibility (Project-> Reference->)
2)copy the following code in a new project
3) Call the procedure "Create_database" in the form_load
4) A database is created with one table. Similarly add as many table as u want to the database
'Procedure: To Create a Database at runtime
Sub Create_Database()
Set ws = DBEngine.Workspaces(0) 'set the DB engine workspace
Set DB = ws.CreateDatabase(App.Path & "\Sampledb.mdb", dbLangGeneral) 'createdatabase
Set DB = ws.OpenDatabase(App.Path & "\Sampledb.mdb") 'open database
Create_CardHolder_Table 'call create table
MsgBox "DB Successfully Created!!"
End Sub
'Procedure: To create a table at runtime
Public Sub Create_CardHolder_Table()
Dim i As Integer
'------Cardholder--------------
Dim TDCardHolder As TableDef 'obj for tabledef
Dim FLDcardHolder(7) As Field 'obj for fields
Dim CardIDIndex As Index 'obj for index
Dim CardIDFLD As Field 'obj for filed to set the index for a field
''------------Create Cardholder Table--------------------
'create a table
Set TDCardHolder = DB.CreateTableDef("Cardholder")
'create a field 'ID' of Text datatype with the filed size of 8
Set FLDcardHolder(0) = TDCardHolder.CreateField("ID", dbText, 8)
'set required as true as this is Primary key FLDcardHolder(0).Required = True
'set allow zero lenghth to false
FLDcardHolder(0).AllowZeroLength = False
'Similarly do for other fields
'datatype can be dbtext for text fileds
'dbdate for date fileds
'dblong for long datatype etc.
'check the filed type in msdn help for more datatypes
Set FLDcardHolder(1) = TDCardHolder.CreateField("Name", dbText, 25)
FLDcardHolder(1).Required = True
FLDcardHolder(1).AllowZeroLength = False
Set FLDcardHolder(2) = TDCardHolder.CreateField("DateofIssue", dbDate)
FLDcardHolder(2).Required = False
Set FLDcardHolder(3) = TDCardHolder.CreateField("Gender", dbText, 6)
FLDcardHolder(3).Required = True
FLDcardHolder(3).AllowZeroLength = False
Set FLDcardHolder(4) = TDCardHolder.CreateField("DateofBirth", dbDate)
FLDcardHolder(4).Required = True
Set FLDcardHolder(5) = TDCardHolder.CreateField("Address", dbText, 60)
FLDcardHolder(5).Required = False
FLDcardHolder(5).AllowZeroLength = True
Set FLDcardHolder(6) = TDCardHolder.CreateField("Pincode", dbText, 6)
FLDcardHolder(6).Required = False
FLDcardHolder(6).AllowZeroLength = True
Set FLDcardHolder(7) = TDCardHolder.CreateField("Phone", dbText, 12)
FLDcardHolder(7).Required = False
FLDcardHolder(7).AllowZeroLength = True
'append all fields to the table
For i = 0 To 7
TDCardHolder.Fields.Append FLDcardHolder(i)
Next i
'create an index for the filed 'ID'
Set CardIDIndex = TDCardHolder.CreateIndex("ID")
CardIDIndex.Primary = True 'set primary key as true for making it as primary key
CardIDIndex.Unique = True 'set unique as true for making any filed as unique
'append the index filed to the table's index
Set CardIDFLD = CardIDIndex.CreateField("ID")
CardIDIndex.Fields.Append CardIDFLD
TDCardHolder.Indexes.Append CardIDIndex
'append the table to the database
DB.TableDefs.Append TDCardHolder
'reset all reference variables to nothing
Set CardIDIndex = Nothing
Set CardIDFLD = Nothing
Set TDCardHolder = Nothing
For i = 0 To 7
Set FLDcardHolder(i) = Nothing
Next i
End Sub
