Here's code from my db that creates table in backend.
Code:
Dim tdf As TableDef
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim db As DAO.Database
If IsNull(Me.tbxTestNum) Then
MsgBox "Must enter test number.", vbCritical, "Error"
Else
Set cn = New ADODB.Connection
'connect to the backend database
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source='" & gstrBasePath & "Data\LabData.accdb'"
'create the test table
cn.Execute "CREATE TABLE " & Me.tbxTestNum & " (LabNum text(12) PRIMARY KEY Not Null, method text(30) Not Null);"
'set table link
Set tdf = CurrentDb.CreateTableDef(Me.tbxTestNum)
tdf.SourceTableName = Me.tbxTestNum
tdf.Connect = "; DATABASE=" & gstrBasePath & "Data\LabData.accdb"
CurrentDb.TableDefs.Append tdf
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM BuildTable;", CurrentProject.Connection, adOpenStatic, adLockPessimistic
'must use DAO to set AllowZeroLength property, I don't allow zero length fields and Access defaults to Yes
Set db = DBEngine.OpenDatabase(gstrBasePath & "Data\LabData.accdb")
While Not rs.EOF
If rs!DataField <> "LabNum" And rs!DataField <> "method" Then
'create field in new table
cn.Execute "ALTER TABLE " & Me.tbxTestNum & " ADD COLUMN " & _
rs!DataField & " " & IIf(rs!DataType = "Boolean", "Bit", rs!DataType) & _
IIf(rs!DataType = "Text", "(" & rs!FieldSize & ")", "") & ";"
End If
If rs!DataType = "Text" Then
'change the AllowZeroLength default Yes to No
db.TableDefs(Me.tbxTestNum).Fields(rs!DataField).AllowZeroLength = False
End If
rs.MoveNext
Wend
rs.Close
cn.Close
db.Close
End If