Celelond
06-16-2011, 07:11 AM
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?: pray2:
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
Thanks allot
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?: pray2:
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
Thanks allot