• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• To create Access database at runtime

To create Access database at runtime

To create Access database at runtime

API Declarations


'-----------Decalarations----------------
Dim ws As Workspace 'obj for workspace
Dim DB As Database 'obj for database
'----------------------------------------



Rate To create Access database at runtime

• 1
• 2
• 3
• 4
• 5

(2(2 Vote))

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