Better way to test for empty Recordset
 

I have this code:

'code..............
With cm
.ActiveConnection = cn
.CommandText = DesktopReportQuery
.CommandType = adCmdStoredProc
Set pm1 = .CreateParameter(InputField, adInteger,
adParamInput)
.Parameters.Append pm1
.Parameters(InputField) = ReportMonth
Set pm2 = .CreateParameter(InputField, adInteger,
adParamInput)
.Parameters.Append pm2
.Parameters(InputField) = ReportYear
End With

rs.Open cm

Set PC = ThisWorkbook.PivotCaches.Add
(SourceType:=xlExternal)
Set PC.Recordset = rs
Set PT = PTSheet.PivotTables.Add(PivotCache:=PC,
TableDestination:=PTSheet.Range("A9"))

'code...............

It's always worked fine. However, I eventually ran into a
situation where the Recordset is empty. So the pivot table
creation fails. So I decided simply to use an If structure
to first test if the Recordset was empty, like this:

With cm
.ActiveConnection = cn
.CommandText = DesktopReportQuery
.CommandType = adCmdStoredProc
Set pm1 = .CreateParameter(InputField, adInteger,
adParamInput)
.Parameters.Append pm1
.Parameters(InputField) = ReportMonth
Set pm2 = .CreateParameter(InputField, adInteger,
adParamInput)
.Parameters.Append pm2
.Parameters(InputField) = ReportYear
End With

if rs.EOF Then
'code to skip the creation of the pivot table
Else
Set PC = ThisWorkbook.PivotCaches.Add
(SourceType:=xlExternal)
Set PC.Recordset = rs
Set PT = PTSheet.PivotTables.Add(PivotCache:=PC,
TableDestination:=PTSheet.Range("A9"))
End if

It works if rs.EOF is True. But if there IS data in the
Recordset, when it gets to the statement to put the pivot
table in Cell A9, I get this error:

'One or More Accessor Flags were Invalid'

Huh? The only difference is that it has to first test if
rs.EOF is True. How can that cause this error?

Better yet, is there a better way to run my query and then
abort the creation of the pivot table if the recordset is
empty?

tod

Better way to test for empty Recordset
 

Tod

See if this applies

http://www.google.com/groups?threadm... ng.google.com


--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Tod" wrote in message
...
I have this code:

'code..............
With cm
.ActiveConnection = cn
.CommandText = DesktopReportQuery
.CommandType = adCmdStoredProc
Set pm1 = .CreateParameter(InputField, adInteger,
adParamInput)
.Parameters.Append pm1
.Parameters(InputField) = ReportMonth
Set pm2 = .CreateParameter(InputField, adInteger,
adParamInput)
.Parameters.Append pm2
.Parameters(InputField) = ReportYear
End With

rs.Open cm

Set PC = ThisWorkbook.PivotCaches.Add
(SourceType:=xlExternal)
Set PC.Recordset = rs
Set PT = PTSheet.PivotTables.Add(PivotCache:=PC,
TableDestination:=PTSheet.Range("A9"))

'code...............

It's always worked fine. However, I eventually ran into a
situation where the Recordset is empty. So the pivot table
creation fails. So I decided simply to use an If structure
to first test if the Recordset was empty, like this:

With cm
.ActiveConnection = cn
.CommandText = DesktopReportQuery
.CommandType = adCmdStoredProc
Set pm1 = .CreateParameter(InputField, adInteger,
adParamInput)
.Parameters.Append pm1
.Parameters(InputField) = ReportMonth
Set pm2 = .CreateParameter(InputField, adInteger,
adParamInput)
.Parameters.Append pm2
.Parameters(InputField) = ReportYear
End With

if rs.EOF Then
'code to skip the creation of the pivot table
Else
Set PC = ThisWorkbook.PivotCaches.Add
(SourceType:=xlExternal)
Set PC.Recordset = rs
Set PT = PTSheet.PivotTables.Add(PivotCache:=PC,
TableDestination:=PTSheet.Range("A9"))
End if

It works if rs.EOF is True. But if there IS data in the
Recordset, when it gets to the statement to put the pivot
table in Cell A9, I get this error:

'One or More Accessor Flags were Invalid'

Huh? The only difference is that it has to first test if
rs.EOF is True. How can that cause this error?

Better yet, is there a better way to run my query and then
abort the creation of the pivot table if the recordset is
empty?

tod