G
george-v
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:=xlExternal)
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
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:=xlExternal)
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