Using a Variable in a SQL statment for a pivot table

Question

I am trying to use a macro that will pull information from a Access data base to create a Pivot table.  In the code i need to add a filter.  I tried to make this filter dynamic but it looks like it is not passing the informaiton correctly.  My variable work correctly up until this point.  Here is the code i have:

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)

            .Connection = Array(Array( _

            "ODBC;DSN=MS Access Database;DBQ=G:\BLA\BLA\TSS_TRACKING.mdb;DefaultDir=G:\BLA" _

            ), Array("t\TSS;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"))

            .CommandType = xlCmdSql

            .CommandText = Array( _

            "SELECT 4_WEEK_TRACKING.CALL_CENTER, 4_WEEK_TRACKING.U_NAME, 4_WEEK_TRACKING.TRANSFER, 4_WEEK_TRACKING.DWEEK, 4_WEEK_TRACKING.COUNT" & Chr(13) & "" & Chr(10) & "FROM G:\BLA\BLA\TSS_TRACKING" _

            , _

            ".4_WEEK_TRACKING 4_WEEK_TRACKING" & Chr(13) & "" & Chr(10) & "WHERE (4_WEEK_TRACKING.CALL_CENTER=filter_name)" _

            )

            .CreatePivotTable TableDestination:=ActiveSheet.Cells(3, 1), _

            TableName:=center_name, DefaultVersion:=xlPivotTableVersion10

        End With

The section in bold if the variable.  When i run this i get the error message [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1

Any ideas why this is not working?

Answer 1

Ok - several things here

1: Do you need to create the pivot table every time or just refresh the data - if you just need to refresh then don't use PivotCaches.add

2: I know the array stuff comes out of the macro recorder but it is not needed at all and just makes the code harder to read

3: When adding a variable to a string you need to use a format such as: strSQL = "Base SQL String Goes Here " & Variable_Here & "Close string if necessary"

With all that in mind I would suggest that your code needs to be something along the lines of:

Dim strConn as string, strSQL as string

strConn = "ODBC;DSN=MS Access Database;DBQ=G:\BLA\BLA\TSS_TRACKING.mdb;DefaultDir=G:\BLAt\TSS;DriverId=25;FIL=MSAccess;MaxBufferSize=2048;PageTimeout=5;"

strSQL = "SELECT CALL_CENTER, U_NAME, TRANSFER, DWEEK, [COUNT] FROM G:\BLA\BLA\TSS_TRACKING.4_WEEK_TRACKING WHERE CALL_CENTER='" &filter_name & "'"

With thisworkbook

    with .PivotCaches(1)

        .SourceType:=xlExternal)

        .Connection = strConn

        .CommandType = xlCmdSql

        .CommandText =  strSQL

    end with

    with .pivottables(center_name)

        .refreshtable

    end with

   End With

---

Rgds Geoff