"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

"acessor flags" creating pivot table from recordset
 

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" wrote in message
...
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