To create a database we would need MS Access application installed in our system. But we can create a database using the following code, without using MS Access application.
To make this happen, Microsoft has created an Microsoft ActiveX Data Objects Extensions library for Data Definition Language and Security. It is in short called as ADOX.
You would need to add the references in Excel VBA editor, before using the ADOX.
‘define filepath variable as string, this string will contain DB creation path and DB name
Dim filepath As String
‘assign the folder path and db name
filepath = “C:\Users\Default\Desktop\Test.accdb”
‘define catalog variable as object, which will work to set the ADOX catalog
Dim Catalog As Object
‘assign the ADOX.Catalog object to our variable
Set Catalog = CreateObject(“ADOX.Catalog”)
‘Executing the code to create the MS Access database
Catalog.Create “Provider=Microsoft.Jet.OLEDB.4.0;” & _
“Jet OLEDB:Engine Type=4” & “;Data Source=” & filepath & “”
‘Done. Database with name “Test.accdb” has been created in the filepath location
you can directly copy paste the above code in VBA module, to create a new db. All you need to do is change the filepath value above. e.g.
filepath = “C:\Users\UserName\Desktop\New.accdb”
Also, you can assign the value to file path by entering the path in Excel cell. see example below:
filepath = Sheet1.Range(“A1”).Value
while in A1, we can give the path C:\Users\UserName\Desktop\New.accdb.
By assigning the filepath value from range(“A1”), we don’t need to open the VBA editor to change the path.
I have tried to answer few queries you might have, after going through the above code. For any more queries, you can reply or comment on this post.
What is ADOX.Catalog ?
For creation of the database, we need a catalog object provided by the ADOX library. In order to get access to the all the objects, you need to specify the the ADOX.catalog using the new catalog variable.
Which library references do we need to add ?
Microsoft ADO Ext. 2.7 for DDL and Security.
What is Microsoft.JET.OLEDB.4.0. ?
This code works as an provider for all the tasks related to MS Acess db
Thank you for reading. Happy Learning !