How to create table in MS ACCESS database using Excel VBA code ?

Before running the code below, we would need to add the reference to Microsoft ActiveX Data Objects 2.x Library in the Excel VBA Editor.

see my blog on How to use VBA editor and add references to Excel VBA ?

--------------------------------------------------------------------------------------------------------------------------

Sub Create_Test_Table ()

'Define the variables to be used in the VBA code

Dim connectdb  As String, pathdb As String, connObj  As ADODB.Connection

'Assigning the database name and the connection path to the pathdb variable

pathdb = Sheet1.Range("A2").Value

'In the above case the reference is taken from Range A2 of sheet 1, while we can directly add the path in VBA code e.g.

'pathdb = "C:\Users\Default\Desktop\Test.accdb"    

'Connection reference and path

connectdb = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & pathdb & ";"

'Connecting to the database and creating new table

Set connObj = New ADODB.Connection

With connObj

.Open connectdb

.Execute "CREATE TABLE TEST_TABLE ([COL1] text(50) WITH Compression NULL, " & _

                 "[COL2] text(200) WITH Compression NULL, " & _

                 "[COL3] datetime NULL)"

End With

' Table with name "TEST_TABLE" will be added to the database, with 3 columns listed in above code

End Sub

--------------------------------------------------------------------------------------------------------------------------

You can add as many columns you want to add in the above code. This code can be directly copied to Excel VBA editor and executed with few tweaks like:

1. Changing the pathdb

2. Changing the required column names

3. Changing the required table name

If you want to learn about VBA, please visit my blog posts below :

What are objects, properties and methods in excel VBA ?

How to use comments in the Excel VBA Macro ?

How to use Excel Macro Recorder ?

and

more....

How to create database using Excel VBA ?

Creating data base using Excel VBA.

To create a database we would need MS Access application installed in out system. But now we can create a database using the following code, without using MS Access application. The make this happen, Microsoft had 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.

--------------------------------------------------------------------------------------------------------------------------

Sub createdb()

'defining filepath variable as string, this string will contain DB creation path and DB name
Dim filepath As String

'assigning the folder path and db name
    filepath = "C:\Users\Default\Desktop\Test.accdb"

'defining catalog variable as object, which will work to set the ADOX catalog
 Dim Catalog As Object

'assigning 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

End Sub

--------------------------------------------------------------------------------------------------------------------------

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.


You must be having few questions in your mind, about the above code. I tried to answer few below, if there are more, please feel free to reply on this blog.

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