[Microsoft][ODBC Driver Manager] error

  • Thread starter Thread starter LongVacation
  • Start date Start date
L

LongVacation

Would anyone advice on why I am getting this error? Excel VBA code to extract
data from Access (see picture).

Thanks in advance.

Sub CreatePivotTableFromDB()
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim DBFile As Variant

On Error Resume Next
Application.DisplayAlerts = False
Sheets("PivotSheet").Delete
On Error GoTo 0

'Create a Pivot Cache
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)

'Connect to database and do query
DBFile = ThisWorkbook.Path & "\budget.mdb"
ConString = "ODBC;DSN=MS Acess Database;DBQ=" & DBFile

QueryString = "SELECT * FROM BUDGET"
With PTCache
.Connection = ConString
.CommandText = QueryString
End With

'add new worksheet
Worksheets.Add
ActiveSheet.Name = "PivotSheet"

'create pivot table<===========Error at the line below
Set PT =
PTCache.CreatePivotTable(TableDestination:=Sheets("PivotSheet").Range("A1"),
TableName:="BudgetPivot")

'add fields
With PT
.PivotFields("DEPARTMENT").Orientation = xlRowField
.PivotFields("MONTH").Orientation = xlColumnField
.PivotFields("DIVISION").Orientation = xlPageField
.PivotFields("BUDGET").Orientation = xlDataField
.PivotFields("ACTUAL").Orientation = xlDataField
End With
End Sub

zmnmo0.jpg
 
I kept getting this persistent pop-up on the error

Runtime error '1004' [Microsoft][ODBC Driver Manager] Data source name not
found and no default driver specified
 
Persistent error pop-up:

Runtime error '1004' [Microsoft][ODBC Driver Manager] Data source name not
found and no default driver specified
 
Many apologies. I didn't mean to keep re-posting. Appreciate if someone can
tell me how to delete these redundant posts.

Thanks in advance.
 
Back
Top