Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Retrieve Data to Pivot Table "On Demand" | Excel Worksheet Functions | |||
Run a VB code when you click on the data field of a Pivot Table | Excel Discussion (Misc queries) | |||
Lookup data in a variable table & retrieve data from a pivot table | Excel Worksheet Functions | |||
Excel 2000 Pivot table using offline OLAP CUB as the data source | New Users to Excel | |||
Pivot Table Data Refresh error after upgrade from 2000 to 2003 | Excel Programming |