ADO connection suddenly starts failing Intermittently

Question

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:

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?

Answer 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