Geek Answers Handbook

The requested operation requires an OLE DB Session object ... - Connecting Excel to SQL Server through ADO

I am trying to take Excel 2003 and connect it to SQL Server 2000 to run several dynamically generated SQL queries that eventually populate specific cells.

I try to do this through VBA through ADO (I tried from 2.8 to 2.0), but I get an error when setting a variable ActiveConnectionthat is inside the object ADODB.Connection. I need to solve this rather fast ...

The requested operation requires an OLE DB session object that is not supported by the current provider.

I honestly don’t know what this error means, and now I don’t care. How to get this connection for success 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

, - , . .

/. , Google , , , , ....

VBA "Sheet1" " Microsoft Excel". , . ?

+5
1

. , Con.Open, 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)

Con.Open

Set Cmd.ActiveConnection = Con   'Error occurs here.

Cmd.CommandText = SQL
Cmd.CommandType = adCmdText
+12

More articles:


All Articles