Access query from Excel

[starl]

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.

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????

 

[SydneyGeek]

Looks OK to me -- how about running a Debug.Print on the Connection string to see that you haven't doubled up or omitted a slash?
Probably something subtle going on...

Denis

 

[SydneyGeek]

Tracy, I played with adjusting the recorded code (Excel XP) and came up with this:

Sub Macro2()
    Dim sPath As String
    sPath = ActiveWorkbook.Path
    
    With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
        .Connection = "ODBC;DSN=MS Access Database;DBQ=" & sPath & "\Main Data.mdb;DefaultDir=" & sPath & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
        .CommandType = xlCmdSql
        .CommandText = "SELECT qryAllData.Project, qryAllData.GrossProfit, qryAllData.HalfYear FROM qryAllData qryAllData"
        .CreatePivotTable TableDestination:= _
        "'[External Pivot VBA.xls]Sheet4'!R3C1", TableName:="DataPivot", _
        DefaultVersion:=xlPivotTableVersion10
    End With
    With ActiveSheet.PivotTables("DataPivot").PivotFields("Project")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("DataPivot").PivotFields("HalfYear")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("DataPivot").AddDataField ActiveSheet.PivotTables( _
        "DataPivot").PivotFields("GrossProfit"), "Sum of GrossProfit", xlSum
End Sub

I gave the Pivot Table a generic name, turned each statement into a one-liner, and used a shorter field list than you did. Maybe the field list evaluates to a string that throws an error?
This code worked fine for me

Maybe you could try building up the SQL string in chunks, and feeding it as a variable to the .CommandText statement

Denis

 

[starl]

this is really stupid.. but the problem was my single quote... I was using a regular single quote, but the query requires this funky one.. looks italicized...
` <-trying to post it here
' <-normal one here

 

[SydneyGeek]

What was that about "something subtle"?
Glad you sorted it!

Denis