"acessor flags" creating pivot table from recordset

I'm using ADO to create a recordset which I'm trying to use to create a
pivot table. The code is below. I get error "One or more accessor flags
were invalid" on the CreatePivotTable line. I know the recordset has data.

TIA,

Wolfie

Dim rsData As ADODB.Recordset
Dim szConnect As String
Dim szSQL As String
Dim objPivotCache As PivotCache
Dim objPivotTable As PivotTable

' Create connection string
szConnect = "Provider=SQLOLEDB; Data Source=MyServer;;User ID=myuser; " & _
"password=mypassword"

' Create the SQL statement
szSQL = Worksheets("SQL_ActPlan").Range("A100").Value

' Create the Recordset object and run the query.
Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Make sure we got records back
If Not rsData.EOF Then
' Use the record set for the pivot table
Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal)
Set objPivotCache.Recordset = rsData2
With objPivotCache
.CreatePivotTable TableDestination:="R7C1", _
TableName:="PivotTable1", ReadData:=True
End With
rsData2.Close
End If

The issue here was in the following line

If Not rsData.EOF Then

Apparently this code does something to the recordset so that the pivotcache
is unable to read it. Freaky right!? I commented it out and it now works.
Apparently testing BOF and RecordCount causes the same problem.

The credit for this find goes to the Wrox Excel VBA books (which are
excellent) and their brilliant authors.

Wolfie


"Wolfie" <[email protected]> wrote in message
news:[email protected]...
> I'm using ADO to create a recordset which I'm trying to use to create a
> pivot table. The code is below. I get error "One or more accessor flags
> were invalid" on the CreatePivotTable line. I know the recordset has
> data.
>
> TIA,
>
> Wolfie
>
> Dim rsData As ADODB.Recordset
> Dim szConnect As String
> Dim szSQL As String
> Dim objPivotCache As PivotCache
> Dim objPivotTable As PivotTable
>
> ' Create connection string
> szConnect = "Provider=SQLOLEDB; Data Source=MyServer;;User ID=myuser; " &
> _
> "password=mypassword"
>
> ' Create the SQL statement
> szSQL = Worksheets("SQL_ActPlan").Range("A100").Value
>
> ' Create the Recordset object and run the query.
> Set rsData = New ADODB.Recordset
> rsData.Open szSQL, szConnect, adOpenForwardOnly, _
> adLockReadOnly, adCmdText
>
> ' Make sure we got records back
> If Not rsData.EOF Then
> ' Use the record set for the pivot table
> Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal)
> Set objPivotCache.Recordset = rsData2
> With objPivotCache
> .CreatePivotTable TableDestination:="R7C1", _
> TableName:="PivotTable1", ReadData:=True
> End With
> rsData2.Close
> End If
>