0

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.

Improve this question

1 Answer 1

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks for the reply! Unfortunately I forgot to write "Set conWork = New ADODB.Connection" into my question..I updated it now.
I changed my connectionString to: conWork.ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};SERVER=server.x.y.z;Provider=Microsoft.ACE.OLEDB.12.0;UserID=somebody;Password=password;Data Source=server.x.y.z;" now I am receiving the errormessage "Could not find installable ISAM"
You're attempting to connect to a database. Is that database SQL Server or MS Access? I'm confused.
Thanks for the help. Now I'm using the connectionString "Driver={Microsoft Access Driver (*.mdb)};Dbq=\\folder1\folder2\User1\Database.mdb; Uid=Admin;Pwd=password;" . I don't get any connection errors right now, but have problems with accessing storedProcedures (which are stored on the server) over an ADODB.Command object. I have posted the corresponding question right here -> stackoverflow.com/questions/31540879/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.