VBA CommandText "application-defined or object-defined error"

[goldfish]

Sub QueryUpdate()
Sheets("Totals").PivotTables("PivotTable1").PivotCache.CommandText = "SELECT * FROM Totals"
End SUB

but it gives me the error: "application-defined or object-defined error"

Any idea how I can make this work?

Thanks

 

[Norie]

Are you actually using an ODBC connection to a data source with a table called Totals?

 

[goldfish]

Yup, here is more

DBDir = "C:\Documents and Settings\aft\Q3\ReportTotals"
DBName = "Reports.mdb"
Querystr = "SELECT * FROM Totals"

With Sheets("AddressPivot").PivotTables("PivotTable1").PivotCache
    .Connection = Array( _
        Array("ODBC;DSN=MS Access Database;DBQ=" & DBDir & "\" & DBName & ";"), _
        Array("DefaultDir=" & DBDir & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;") _
    )
    .CommandText = Querystr
    .Refresh
End With

But I get an error when it gets to the .commandtext line. Even the simplified version I posted above didn't work.

Thanks

 

[Norie]

I'm sorry but I'm no expert in this sort of thing - the code appears, to me anyway, OK and so does the SQL.

Have you considered not using a pivot table?

Access has various methods of summarizing data.