Create a Access table using Excel

[Celelond]

Hi,

I am using Excel 2007, and need to create a table in an Access 2003 db.

What I am doing is checking before I submit information from Excel to the db that the table exists, if it does not, to create it.

I have found allot of code online to create a table using vba from within Access, but the only code I found to create it from the excel side did not seem to work with 2007 .
This is the code I found.

I was wondering if someone might be able to help?

[VBA]Public Function CreateTable(ByVal Tablename As String)
Dim oConn As ADODB.Connection
Dim oConnCatalog As ADOX.Catalog
Dim oConnTable As ADOX.Table
Call ConnectDB ' This just opens a connection
Set oConnCatalog = New ADOX.Catalog
Set oConnTable = New ADOX.Table
With adoxTable
.Name = Tablename
.Columns.Append "Date", adDate
.Columns.Append "userName", adVarWChar, 100 ' change as required
.Columns.Append "Advisor_EIN", adInteger
.Columns.Append "Manager_Name", adVarWChar, 100
.Columns.Append "Version_Code", adVarWChar, 100
.Columns.Append "Mobile", adVarWChar, 100
.Columns.Append "Collected", adCurrency
.Columns.Append "Reference", adInteger
.Columns.Append "Week", adVarWChar, 100
.Keys.Append "PrimaryKeyItemID", adKeyPrimary, "ItemID"
End With
oConnCatalog.Tables.Append (adoxTable)
End Function[/VBA]


Thanks allot

[Kenneth Hobs]

You need to open your database first. See: http://vbaexpress.com/forum/showthread.php?t=24883

[Celelond]

Thanks for the fast reponce, I was using this to connect. If it does not work this way could you please explain why as I would very much like to understand it as best I can.
[VBA]ublic Function ConnectDB()
Set oConn = New ADODB.Connection
With oConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Data Source") = databaseName
.Properties("Jet OLEDBatabase Password") = "DAPS"
.Open
End With
If oConn.State = adStateClosed Then
MsgBox "Unable to Connect"
End If
End Function[/VBA]

Thanks allot