Requested operation requires an OLE DB Session object… – Connecting Excel to SQL server via ADO

I’m attempting to take Excel 2003 and connect it to SQL Server 2000 to run a few dynamicly generated SQL Queries which ultimately filling certain cells.

I’m attempting to do this via VBA via ADO (I’ve tried 2.8 to 2.0) but I’m getting an error while setting the ActiveConnection variable which is inside the ADODB.Connection object. I need to resolve this pretty quick…

  • Sorting nvarchar column as integer
  • DROP CASCADE in Sql Server
  • How to find highest values within groups?
  • SQL Server: sys.master_files vs. sys.database_files
  • SQL Server Count is slow
  • SQL Server Conditional Mailing Address Formatting
  • Requested operation requires an OLE DB Session object, which is not supported by the current provider.

    I’m honestly not sure what this error means and right now I don’t care. How can get this connection to succeed so that I can run my queries?

    Here is my VB code:

    Dim SQL As String, RetValue As String
    SQL = " select top 1 DateTimeValue from SrcTable where x='value' " 'Not the real SQL
    RetValue = ""
    
    
    Dim RS As ADODB.Recordset
    Dim Con As New ADODB.Connection
    Dim Cmd As New ADODB.Command
    
    Con.ConnectionString = "Provider=sqloledb;DRIVER=SQL Server;Data Source=Server\Instance;Initial Catalog=MyDB_DC;User Id=<UserName>;Password=<Password>;"
    Con.CommandTimeout = (60 * 30)
    
    
    Set Cmd.ActiveConnection = Con   ''Error occurs here.
    
    ' I'm not sure if the rest is right. I've just coded it. Can't get past the line above.
    Cmd.CommandText = SQL
    Cmd.CommandType = adCmdText
    
    Con.Open
    Set RS = Cmd.Execute()
    
    If Not RS.EOF Then
    
        RetValue = RS(0).Value
        Debug.Print "RetValue is: " & RetValue
    
    End If
    Con.Close
    

    I imagine something is wrong with the connection string but I’ve tried over a dozen variations. Now I’m just shooting in the dark….

    Note/Update: To make matters more confusing, if I Google for the error quote above, I get a lot of hits back but nothing seems relevant or I’m not sure what information is relevant….

    I’ve got the VBA code in “Sheet1” under “Microsoft Excel Objects.” I’ve done this before but usually put things in a module. Could this make a difference?

    One Solution collect form web for “Requested operation requires an OLE DB Session object… – Connecting Excel to SQL server via ADO”

    You have not opened your connection yet. I think you need a Con.Open before you assign it to the Command object.

    Con.ConnectionString = "Provider=sqloledb;DRIVER=SQL Server;Data Source=Server\Instance;Initial Catalog=MyDB_DC;User Id=<UserName>;Password=<Password>;"
    Con.CommandTimeout = (60 * 30)
    
    Con.Open
    
    Set Cmd.ActiveConnection = Con   'Error occurs here.
    
    Cmd.CommandText = SQL
    Cmd.CommandType = adCmdText
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.