VB6/VBA Creating Access Database via Code

[Siddharth Rout]

Inspired by this thread, I finally decided to put together a seperate thread on this.

You can create an MDB Access database either with DAO or ADO. DAO is almost obselete now but some people still use it so I am including both the codes.

ADO

Set Reference to the following
Microsoft ActiveX Data Objects x.xx Library
Microsoft ADO Ext. x.xx for DDL and Security

Sub ADOSample()
    Dim adoCat As ADOX.Catalog, adoTable As ADOX.Table
    Dim tblCollection As Collection
    Dim Filenm As String, strConn As String
    
    '~~> MDB to be created. Change this to relevant path and filename
    Filenm = "C:\Temp\MyDatabase.mdb"
    
    '~~> Creating an instance of the ADOX-object.
    Set adoCat = New ADOX.Catalog
    '~~> Creating an instance of Collection-object.
    Set tblCollection = New Collection
    Set adoTable = New ADOX.Table
    
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & Filenm & ";"
    
    '~~> Deleting the existing copy of the database, if any.
    On Error Resume Next
    Kill Filenm
    On Error GoTo 0
     
    '~~> Create the MDB.
    adoCat.Create (strConn)
    
    '~~> Replace "Table1" with a name you like for your table
    tblCollection.Add "Table1"
     
    With adoTable
        '~~> Name the table.
        .Name = "Table1"
        '~~> Creating a field which also is the Primary Key
        .Columns.Append "ID", adInteger
        '~~> Set the Parent Catalog.
        .ParentCatalog = adoCat
        .Columns("ID").Properties("AutoIncrement").Value = True
        '~~> Append the PrimaryKey
        .Keys.Append "PrimaryKey", adKeyPrimary, "ID"
                
        '~~> Add rest of the fields... I have taken 4 types
        .Columns.Append "intField1", adInteger  '~~ number
        .Columns.Append "numField2", adNumeric  '~~ number, decimal
        .Columns("numField2").Precision = 2
        .Columns.Append "dateFiled3", adDate    '~~ Date
        .Columns.Append "txtFiled4", adWChar    '~~ text
    End With
    
    '~~> Finally add the Table to the MDB.
    adoCat.Tables.Append adoTable
    
    '~~> Release the objects from the memory.
    Set adoTable = Nothing
    Set tblCollection = Nothing
    Set adoCat = Nothing
     
    '~~> Inform user.
    MsgBox "New .MDB Created - '" & Filenm & "'", vbInformation
End Sub

DAO

Set reference to Microsoft DAO x.x Object Library

Sub DAOExample()
    Dim tdefMDB As TableDef, txtFieldone As Field
    Dim dateFieldone As Field, memoFieldone As Field, dbDatabase As Database
    Dim sNewDBPathAndName As String
    
    '~~> MDB to be created. Change this to relevant path and filename
    sNewDBPathAndName = "C:\Temp\MyDatabase.mdb"
    Set dbDatabase = CreateDatabase(sNewDBPathAndName, dbLangGeneral, dbEncrypt)
 
    '~~> Create new TableDef (I am creating a table Table1)
    Set tdefMDB = dbDatabase.CreateTableDef("Table1")
 
    '~~> Add fields to MDB
    '~~> for eample I am creating One text field, 1 date field and
    '~~> 1 memo field. Amend as applicable
    Set txtFieldone = tdefMDB.CreateField("txtField1", dbText, 20)
    Set dateFieldone = tdefMDB.CreateField("dateField1", dbDate)
    Set memoFieldone = tdefMDB.CreateField("memoField1", dbMemo)
    
    '~~> Append the field objects to the TableDef
    tdefMDB.Fields.Append txtFieldone
    tdefMDB.Fields.Append dateFieldone
    tdefMDB.Fields.Append memoFieldone
 
    '~~> Save TableDef definition by appending it to TableDefs collection.
    dbDatabase.TableDefs.Append tdefMDB
    
    '~~> Inform user.
    MsgBox "New .MDB Created - '" & sNewDBPathAndName & "'", vbInformation
End Sub

Hope this helps...