Beyond Excel: VBA and Database Manipulation

Mar 17 2015   10:36AM GMT

Create MS Access Database with Just Excel and VBA

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

Tags:
excel

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/

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: