1

I have an Access 2010 front end connecting to a SQL Server 2014 back end. I've got SSMS 2014 installed on my machine, and everything was working fine. (Running Win7 64-bit with 64-bit Office.)

Long story short, in trying to trouble shoot an end user problem, I attempted to install AccessDatabaseEngine_x64.exe from Microsoft. (I ended up getting it from downloads.com because the MS link wouldn't download it - I should have taken that as a clue.)

Installing that didn't help the issue, so I uninstalled and rebooted my machine. Now, when my Access application is attempting to connect to the SQL Server via this code:

Dim ConfigRS As New ADODB.Recordset
Dim SQLString As String
SQLString = "SELECT <fields> " & _
            "FROM <table> " & _
           "WHERE <condition>"
ConfigRS.Open Source:=SQLString, ActiveConnection:=CurrentProject.Connection

I usually get this error (server name, starts with 'c', blacked out), though sometimes it works just fine:

enter image description here

I can browse the linked tables from within Access, I can select from the tables in SSMS with no problem, I just cannot open an ADODB query via code.

The only thing that's changed today is the installation and uninstallation of AccessDatabaseEngine.

  • Does anyone know of specific files I need remove to finish removing all traces of AccessDatabaseEngine?
  • Would it be easier to reinstall SSMS to ensure all the proper drivers are in place?
  • Does specifying cursorType and LockType after ActiveConnection help? – Kentaro Oct 30 '15 at 17:49
  • Unfortunately, no. I've tried most variations of the two (I only need read-only access, so anything would be acceptable), and that had no impact on it. Thanks for the suggestion. – FreeMan Oct 30 '15 at 20:29
  • How about replacing activeconnection with connection strings? or if it's DSN, open a connection using Connection object and use it in place of active conn? – Kentaro Oct 30 '15 at 20:32
  • I appreciate the suggestions, @Kentaro. The issue is that this was working just fine, until I installed AccessDatabaseEngine_x64.exe. That leads me to believe that my method was sound and that the install broke it. If, though, you think making the changes will make it work, even with the remains of that install lurking about, I'll pursue that option. – FreeMan Nov 2 '15 at 13:13
  • If the Access can see the tables and run a statement against the SQL server, it's using the right conmection string. But it's Only in your VBA, things arent working, correct? Then the first variable to check, logically, would be the connection strngs for the vba. Wouldnt you say? – Kentaro Nov 2 '15 at 13:27
1

Since Access can successfully connect to the SQL Server and execute queries against it, the connection string the Access is using must be correct. The connection made through ADO, on the other hand is relying on CurrentProject.Connection property, which we don't know what it is, and fails unexpectedly. Inside AccessDataBaseEngine_X64.exe seems to have no ODBC drivers, and Access works with or without AccessDataBaseEngine_X64.exe installed. So the DSN the Access is using didn't change, therefore the problem must be the connection string used in ADO.

So if you use the same connection string as that of Access, the problem should go away.

As for why it started behaving erratically all of sudden I can't fully comment on that. But what Microsoft states about the CurrentProject.Connection property might give you a clue (the relevant section in bold).

Use the Connection property to refer to the Connection object of the current Microsoft Access project (.adp) or Access database object. You can use the Connection property to call methods on the Connection object such as BeginTrans and CommitTrans.

Note: The Connection property actually returns a reference to a copy of the ActiveX Data Object (ADO) connection for the active database. Thus, applying the Close method or in anyway attempting to alter the connection through the Connection object’s methods or properties will have no affect on the actual connection object used by Microsoft Access to hold a live connection to the current database. Since the Connection property is the main Shape provider connection, the following information is necessary when using this property.

  1. MSDataShape uses Recordset.CursorLocation = adUseClient. Do not set CursorLocation prior to assigning a recordset to CurrentProject.Connect.
  2. MSDataShape uses Recordset.CursorType = adOpenStatic. Do not set CursorType prior to assinging a recordset to CurrentProject.Connection.
  3. MSDataShape accepts Recordset.LockType = adLockOptimistic, adLockBatchOptimistic, or adLockReadOnly (default). If set to adLockPessimistic, it is changed to adLockOptimistic.
  4. The shape connection does not support the all ADOX operation, specifically the Columns.Properties collection is not supported.
  5. In order to ensure that a shape connection will work correctly, the Command.CommandType must be set to adCmdTable.

Bonus:

  1. How to know what files were involved with AccessDatabaseEngine_x64.exe? (I was curious!)

    Use Orca

  • Thanks for the help! There was no must for the AccessDataBaseEngine_X64.exe, that was something that I'd found that was suggested to overcome a different issue that I was having. Specifying the connection string seems to have resolved both issues. – FreeMan Nov 4 '15 at 16:54
  • Glad to be a help. (and the answer edited to remove some potentially snarky expression) – Kentaro Nov 4 '15 at 16:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.