Multiple-step OLE DB operation generated errors

[jjc99]

Hi all

I am attempting to connect to an SQL Server database via VBA. I am using ADO.

My code is as follows

Private Sub Connect_to_SQLServer()
Dim oCon As ADODB.Connection
Dim oRS As ADODB.Recordset
Set oCon = New ADODB.Connection

oCon.ConnectionString = "Data Source=PC-001\SQLEXPRESS;Initial Catalog=MYDBNAME;Integrated Security=True"

oCon.Open
Set oRS = New ADODB.Recordset
oRS.ActiveConnection = oCon
oRS.Source = "Select * From tbl_News"
oRS.Open

oRS.Close
oCon.Close
If Not oRS Is Nothing Then Set oRS = Nothing
If Not oCon Is Nothing Then Set oCon = Nothing
End Sub


However, when I run this code I get the following error:

Run-time error '-2147217887 (80040e21)':

Multiple-step OLE DB operation generated errors. Check each OLE db status value, if available. No work was done.


Does anyone know how to go about solving this issue.

Any help would be greatly appreciated.

[hansup]

Which code line is highlighted when you choose debug from the error dialog?

I'm curious whether the line is oCon.Open, because your connection string doesn't look like the samples I see at connectionstrings.com. That site shows 2 alternatives for trusted connections:

Integrated Security=SSPI
Trusted_Connection=True

Yours uses ...

Integrated Security=True

I don't know if that can work.

Also, I don't know whether you can use a dash in the server name without using quotes. I would try that piece this way:

oCon.ConnectionString = "Data Source='PC-001\SQLEXPRESS';" & _
"Initial Catalog=MYDBNAME;Trusted_Connection=True"

[jjc99]

Thanks for your help.

I needed to use an OLEDB connection string as follows;

Provider=SQLNCLI10;Server=PC-001\SQLEXPRESS;Database=MYDBNAME; Trusted_Connection=yes;