Access query from Excel

starl

Administrator
Joined
Aug 16, 2002
Messages
5,969
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
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.

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

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Tracy, I played with adjusting the recorded code (Excel XP) and came up with this:
Code:
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

Administrator
Joined
Aug 16, 2002
Messages
5,969
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,478
Messages
5,601,900
Members
414,482
Latest member
morkar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top