Enigmachrysalis
Active Member
- Joined
- Apr 13, 2009
- Messages
- 352
- Office Version
- 365
- Platform
- 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
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