Create MS Access Database with Excel and VBA

Post date: Mar 17, 2015 3:03:11 PM

We don't need MS Access to create MS Access databases. Excel's VBA can create them. Here is sample code:

Dim oCatalog As Object

Set oCatalog = CreateObject("ADOX.Catalog")

oCatalog.Create "provider='Microsoft.ACE.OLEDB.12.0';" & _

"Data Source=C:\NewDB.accdb"

Run this code and a new MS Access database called NewDB.accdb will be created, ready for new tables and new data.

Line one declares a generic object variable oCatalog .

Line two uses the CreateObject method to turn our oCatalog object into the specified class: "ADOX.Catalog"

NOTE! This is an example of "Late Binding". Late binding allows us to create objects without adding reference libraries. We lose Intellesense but or code is more portable and robust. See reference link for more on late binding.

NOTE! I'm using ADOX, not ADODB. ADOX is an extension to ADO that supports creating databases.

Line Three executes the catalog's create method using an appropriate connection string

References:

Create Object: https://msdn.microsoft.com/en-us/library/office/aa220083(v=office.11).aspx

ADOX: https://msdn.microsoft.com/en-us/library/windows/desktop/ms675532(v=vs.85).aspx

Late Binding: https://sites.google.com/site/beyondexcel/project-updates/untitledpost

Microsoft.ACE.OLEDB Download: http://www.microsoft.com/en-us/download/details.aspx?id=13255

Create Catalog Method: https://msdn.microsoft.com/en-us/library/windows/desktop/ms676498(v=vs.85).aspx

Connection Strings: https://www.connectionstrings.com/ace-oledb-12-0/