VBA Code - Retrieve SQL 2000 data -Pivot Table
 

I am trying to data stored in sql 2000 server to create Pivot Table - office
2003.
trying to create oledb provider using VBA code. Can anyone tell me what is
wrong with this code. When I open the spreadsheet, it gives me a message
"requested operation requires OLE DB session object, which is not supported
by the current prvider". Also please let me know whether the connection
string is correct or not.

Private Sub Workbook_Open()
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim cmd1 As ADODB.Command

Set conn = New ADODB.Connection

With conn
..ConnectionString = "OLEDB;Provider=sqloledb;User Id=xxx;Password=xxx;Data
Source=qqqqq;Initial Catalogue=PATS"
End With
Set cmd1 = New ADODB.Command
Set cmd1.ActiveConnection = conn
With cmd1
..CommandText = "Select RAIL.Adm_Facility, RAIL.County, RAIL.Race from RAIL"
..CommandType = adCmdText
..Execute
End With

Set rst = New ADODB.Recordset
Set rst.ActiveConnection = conn
rst.Open cmd1

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
Set PTCache.Recordset = rst
With PTCache
..CreatePivotTable TableDestination:=Range("A3")
End With

With ActiveSheet.PivotTables("CADATA")
..SmallGrid = False
With .PivotFields("Adm_Facility")
..Orientation = xlRowField
..Position = 1
End With

With .PivotFields("County")
..Orientation = xlColumnField
..Position = 1
End With


With .PivotFields("Race")
..Orientation = xlDataField
..Position = 1
End With

End With

conn.Close
Set cmd1 = Nothing
Set conn = Nothing
Set rst = Nothing
End Sub