[Microsoft][ODBC Driver Manager] error

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
 
L

LongVacation

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
 
L

LongVacation

Persistent error pop-up:

Runtime error '1004' [Microsoft][ODBC Driver Manager] Data source name not
found and no default driver specified
 
L

LongVacation

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top