Create Access Database Table by VBA [access table vba]

This method using for create a table by ADOX objects.
1. Open a Catalog object on the database you want to add a table to.
2. Create a new Table object.
3. Use the Append method of the Columns collection to add the field definitions (Column objects) to the Columns collection of the new Table object.
4. Append the new Table object to the Tables collection of the Catalog object.

It is not necessary to use the Create method to create Column objects for the field definitions before you append them to the Columns collection. The Append method can be used to both create and append the Column object. The following procedure creates a table named Contacts by using ADOX.

Sub CreateAccessTable(strDBPath As String)
Dim catDB As ADOX.Catalog
Dim tblNew As ADOX.Table
Set catDB = New ADOX.Catalog
’ Open the catalog.
catDB.ActiveConnection=”Provider=Microsoft.Jet.OLEDB.4.0;” & _
”Data Source=” & strDBPath
Set tblNew = New ADOX.Table
’ Create a new Table object.
With tblNew
.Name = “Contacts”
’ Create fields and append them to the
’ Columns collection of the new Table object.
With .Columns
.Append “FirstName”, adVarWChar
.Append “LastName”, adVarWChar
.Append “Phone”, adVarWChar
.Append “Notes”, adLongVarWChar
End With
End With

’ Add the new Table to the Tables collection of the database.
catDB.Tables.Append tblNew
Set catDB = Nothing
End Sub

Sub cmdCreateDB_Click()

Call CreateAccessDatabase(“C:/TestFolder/testDB.mdb”)

End Sub

You’ll have a access database file name “testDB.mdb” in “C:/TestFolder/” and four field (FirstName, LastName, Phone and Notes) in a table name “Contacts”