[Microsoft][ODBC Driver Manager] error
 

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:=xlExter nal)

'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

[Microsoft][ODBC Driver Manager] error
 

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

"LongVacation" wrote:

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:=xlExter nal)

'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

[Microsoft][ODBC Driver Manager] error
 

Persistent error pop-up:

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

"LongVacation" wrote:

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:=xlExter nal)

'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

[Microsoft][ODBC Driver Manager] error
 

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.