ADOXCatalog.Tables.Append (Table) generates error

Enigmachrysalis

Active Member
Joined
Apr 13, 2009
Messages
352
Office Version
  1. 365
Platform
  1. Windows
This code errors at the bold red line below. I lifted this code from a functional example provided by MVP SydneyGeek regarding the use of ADO.
I tried to manipulate his code to my purposes, but I think something in the appending of the columns is driving mine haywire. He only had eight or so columns in his example. Is it the data types perhaps?

Option Explicit
Public Const TARGET_DB As String = "BENEFITS.mdb" 'Changed from "DB_test1.mdb"

Sub CreateDB_And_Table()

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim sDB_Path As String

sDB_Path = ActiveWorkbook.Path & Application.PathSeparator & TARGET_DB

If Dir(sDB_Path, vbNormal) <> "" Then
MsgBox TARGET_DB & " already exist in " & ActiveWorkbook.Path, vbInformation
Exit Sub
End If

' 'delete the DB if it already exists
' On Error Resume Next
' Kill sDB_Path
' On Error GoTo 0

'create the new database
Set cat = New ADOX.Catalog
cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & sDB_Path & ";"

'create the table
Set tbl = New ADOX.Table
tbl.Name = "tblBenefits"
tbl.Columns.Append "ID", adVarChar
tbl.Columns.Append "DeptID", adInteger
tbl.Columns.Append "BU", adVarChar
tbl.Columns.Append "Area", adVarChar
tbl.Columns.Append "Comp Freq", adVarChar
tbl.Columns.Append "Annual Rt", adDouble
tbl.Columns.Append "Salary Group", adCurrency
tbl.Columns.Append "Empl Class", adVarChar
tbl.Columns.Append "SARP Elig Date", adDate
tbl.Columns.Append "IC%", adDouble
tbl.Columns.Append "401(k) election %", adDouble
tbl.Columns.Append "Deferred Comp election % (Salary)", adDouble
tbl.Columns.Append "Deferred Comp election % (Bonus)", adDouble
tbl.Columns.Append "Currency", adVarChar

cat.Tables.Append tbl

Set cat = Nothing

End Sub
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Forum statistics

Threads
1,223,270
Messages
6,171,103
Members
452,379
Latest member
IainTru

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top