Create Database with ADO / ADO Create Database

Create Microsoft Access Database programmatically using ADO

Programmers who have used DAO will definitely miss the CreateDatabase in ADO. Here is one method to do the same using ADO.

For doing this you need to include the ADO Extensions library in the project reference.


This library consists of classes and methods to handle the Schema related activities like creation of database, table etc

The following code uses the Catalog object of the ADOX to create a new Microsoft Access database

Sub Create_DB_and_Table_Using_ADOX()

Dim oDB As ADOX.Catalog
Dim sDBPAth As String

Dim sConStr As String
Dim oCn As ADODB.Connection
Dim oCM As ADODB.Command

' ------------------------
' Set the Path and Connection String
' ------------------------
sDBPAth = "c:\Temp\MyAccounts.mdb"
sConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBPAth & ";"

' ------------------------
' Create New ADOX Object
' ------------------------
Set oDB = New ADOX.Catalog
oDB.Create sConStr

Set oCn = New ADODB.Connection
oCn.ConnectionString = sConStr
oCn.Open

Set oCM = New ADODB.Command
oCM.ActiveConnection = oCn
oCM.CommandText = "Create Table Users (" & _
"[EmpNo] Decimal(6), " & _
"[EmpName] Text(150), " & _
"[JoinDate] Date " & _
")"
oCM.Execute
' ------------------------
' Release / Destroy Objects
' ------------------------
If Not oCM Is Nothing Then Set oCM = Nothing
If Not oCn Is Nothing Then Set oCn = Nothing
If Not oDB Is Nothing Then Set oDB = Nothing


' ------------------------
' Error Handling
' ------------------------
Err_Handler:
If Err <> 0 Then
Err.Clear
Resume Next
End If
End Sub

Though I used the ADODB Query deliberately to create a Table in the new database, you can try it with the Table object in ADOX