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

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

see my post 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

Thank you for reading. Happy Learning !

Please leave a reply if you like the article or if you need any help on excel

%d bloggers like this: