CODEWITHSUNDEEP
ms-accessvbaconnectionms-access-2010adodb
McRaven
McRaven

Reputation: 111

Open method for AdoDB.Connection object is not supported in Access 2010

Currently I'm working on an Access Database Application which was using ODBCDirect. After upgrading to Access 2010 I receive an error message that told me that ODBCDirect isn't supported anymore and that I have to change from the DAO to ADO in the corresponding source code parts each time I'm running the application. I found out that the origin of this error message was the source code that was responsible for the database connection which was making use of ODBCDirect.

I followed the tutorials about ADODB.Connection objects and the opening of them. I changed this code to the following simple code by using the ADODB.Connection object.

Now I'm receiving the new error message "(-2147467259) operation is not supported for this type of object".

I found out that the place where I was using the Open function of the ADODB.Connection Object is causing the new error message:

Global conWork As ADODB.Connection
...
Set conWork = New ADODB.Connection
...
conWork.ConnectionString = "ODBC;DRIVER={SQL Server};SERVER=someServer.x.y.z;Provider=Microsoft.ACE.OLEDB.12.0;UserID=user;Password=pw;Data Source=someServer.x.y.z; Trusted_Connection=yes;"
...

conWork.Open  //...causes the error msg "OPERATION IS NOT SUPPORTED FOR THIS TYPE OF OBJECT"

In the vba editor I have the Microsoft ADO 2.8 Library and the Microsoft ADO 2.8 RecordSet Library selected in the references-settings.

Upvotes: 0

Views: 2384

Answers (1)

HansUp
HansUp

Reputation: 97131

I'm unsure why your attempt fails, but there are several issues which look suspicious to me.

  1. Don't include ODBC; in your ADO connection string. Including that piece triggers a run-time error in my tests.
  2. I don't believe you should include both DRIVER={SQL Server} and Provider=Microsoft.ACE.OLEDB.12.0 in your connection string.
  3. Including both UserID=user;Password=pw and Trusted_Connection=yes seems wrong. Choose one or the other, not both.
  4. You must do Set conWork = New ADODB.Connection before you set its ConnectionString and call Open. I can't tell whether your full code does that; it should.
  5. I don't think you need the recordset library reference, but don't know whether including it contributes to the problem.

Maybe you would be better off to start from known-working ADO connection code. This code works in Access 2010 with the Microsoft ActiveX Data Objects 2.8 Library reference and successfully connects to my local SQL Server.

Dim conWork As ADODB.Connection
Dim strConnect As String
strConnect = "DRIVER={SQL Server};SERVER=HP64\SQLEXPRESS;Trusted_Connection=Yes;DATABASE=testbed"
Set conWork = New ADODB.Connection
conWork.ConnectionString = strConnect
conWork.Open

You can find more information regarding connection strings for SQL Server at ConnectionStrings.com.

Upvotes: 1

Related Questions