Pivotcache recordcount (Excel 2003)

Topic

New Reply

Hi everybody:

I seem to be stuck on when / how to get the actual number of records returned in my pivotcache. I’m developing an Excel report generator that allows users to input various parameters for the SQL string that gets data from an Access database. So, I need to be able to test whether their parameters generate data before creating the pivot table. The code below gives me a recordcount of 0 no matter how many records are actually returned. If I try getting the record count after creating the pivtot table, the pivotcache object seems to go out of scope. Please tell me how to get this to work!

Thank you, in advance, for your help.

Set PC = ThisWorkbook.PivotCaches.Add(xlExternal)

SQL_str = “SELECT qODBC_std.* FROM qODBC_std” & strWHERE & “;”
With PC
.Connection = “ODBC;DSN=MS Access Database;” & _
“DBQ=H:KJRBalances_be.mdb; DefaultDir=H:KJR ;DriverID=25;” _
& “FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;”
.CommandType = xlCmdSql
.CommandText = SQL_str
lngRecs = .RecordCount ‘always returns0!
if lngRecs = 0 then
msgbox “Sorry, your selections do not return data. Please try again.”
goto exit_sub
end if
End With

Reply | Quote

Replies

I seem to recall you can test for EOF.

Reply | Quote

Figured it out. The trick is not to call the recordcount property from the pivot cache directly, but from the pivot table after it is created, thus:

Set PT = oSheet.CreatePivotTable(.Range(“A3”), “pvtReport”)
PT.Refresh
lngRecs = PT.PivotCache.RecordCount

Apparently, the recordcount only counts records that are displayed, not that are returned by the sql.

Everything is easy when you know how!

Reply | Quote

Sorry, I meant

set PT = PC.CreatePivotTable

not osheet.CreatePivotTable

Reply | Quote

For the future: you can edit your own posts to correct errors or add information, by clicking the Edit Post button .

Reply | Quote