- Joined
- Aug 16, 2002
- Messages
- 6,082
- Office Version
- 365
- Platform
- Windows
I'm running this from Excel, but it is a query to an access db, so I thought I'd post it here. The code is mostly recorded - I just changed it so that it looked for the path of the db.
when I try to run it, it highlights the .createpivottable line, but the error is "[Microsoft][ODBC Microsoft Access Driver] Syntax error in query. Incomplete query clause."
now, if I run it without the path modifciations - no problems.
what's going on????
Code:
Sub ConnectForPivot()
Dim wkbPivot As Workbook
Dim pth As String
pth = ThisWorkbook.Path
Set wkbPivot = Workbooks.Add
With wkbPivot.PivotCaches.Add(SourceType:=xlExternal)
.Connection = "ODBC;DSN=MS Access Database;DBQ=" & pth & "\progresstrackerData.mdb;DefaultDir=" & pth & _
";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT qryAllPatients.name, qryAllPatients.gender, qryAllPatients.age, qryAllPatients.prob1, qryAllPatients.prob2, qryAllPatients.prob3, qryAllPatients.prob4, qryAllPatients.prob5, qryAllPatients.prob" _
, _
"6, qryAllPatients.prob7, qryAllPatients.prob8, qryAllPatients.prob9, qryAllPatients.prob10, qryAllPatients.var1, qryAllPatients.var2, qryAllPatients.var3, qryAllPatients.var4, qryAllPatients.var5" & Chr(13) & "" & Chr(10) & "FRO" _
, _
"M '" & pth & "\progresstrackerData'.qryAllPatients qryAllPatients" _
)
.CreatePivotTable TableDestination:=Range("A1"), TableName:="PivotTable1" ', DefaultVersion:=xlPivotTableVersion10
End With
wkbPivot.ShowPivotTableFieldList = True
wkbPivot.Activate
End Sub
when I try to run it, it highlights the .createpivottable line, but the error is "[Microsoft][ODBC Microsoft Access Driver] Syntax error in query. Incomplete query clause."
now, if I run it without the path modifciations - no problems.
what's going on????