Requested operation requires an OLE DB Session object... - Connecting Excel to SQL server via ADO

Question

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...

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?

Answer 1

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