Access 2007: type mismatch when opening an ADO connection

Question

I have an Access ADP file. I upgraded the back-end database to point to a SQL 2005 server instead of a SQL 2000 server and changed the database connection information appropriately. The file runs perfectly fine on my own system, running Windows 7 (64-bit) and Access 2007. On the target systems running Windows XP and Access 2007, the primary functionality of the database blows up almost immediately with a "Run-time error '13': Type Mismatch" error.

At first I thought I was suffering from the same problem as described in this question over here, where the default definition of a connection is DAO but the database is using an ADO object. However, in reviewing the code, every instance of a connection is specifically declared as "ADODB.Connection".

The code in question that causes the error is this:

Public Sub Tools()
dim db as ADODB.Connection
dim sql as String

sql = "Select SSPatch from tblPlastech"
set db = CurrentProject.Connection           ' THIS LINE CAUSES THE TYPE MISMATCH ERROR
dim rst as ADODB.RecordSet
set rst = New ADODB.RecordSet

rst.open sql, db, adOpenKeyset, adLockOptimistic
gsSSpath = rst!sspath
QUOTES = Chr(34)
rst.Close
set rst = Nothing
db.Close
set db = Nothing

End Sub

Can anyone shed a bit of light on the issue? Right now I'm stumped.

Answer 1

Here's what I finally found out that appears to be relevant:

On 64-bit Windows 7 Pro, the Microsoft MDAC Component Checker tool tells me that I am running MDAC version "UNKNOWN", with file versions of either 6.1.7600.16385, or 6.1.7601.17514 (which by a strange coincidence match up very closely with the Windows version number). On 32-bit Windows XP, on the other hand, Component Checker says I'm running version "MDAC 2.8 SP1 ON WINDOWS XP SP3", with file versions of 2.81.1132.0 or 2.81.3012.0, which look like proper MDAC version numbers.

If I change the "broken" code while I'm on XP and thereby force a recompile, the exact same code that produced the run-time error (either the type mismatch 13 error mentioned above, or a run-time error 430) will start working (and keep working when I copy it around to other XP boxes, or to my Windows 7 box). If I change the code on my Windows 7 box and redistribute that to an XP box, it breaks, despite the fact that every reference in the list of references is identically named, and points to an identical file in an identical disk location.

Edit: Apparently this version numbering is due to Windows Vista/7 using "WDAC" instead of "MDAC", and the specific problem of code compiled on Win7 SP1 being broken when run on downlevel OSes is a known issue, referenced at support.microsoft.com kb article 2517589 and at this post on technet. Switching to late binding, installing a KB fix on the downlevel systems, or linking in "back-compatible" versions of ADO are the suggested fixes.

Edit 2: The fix that I have settled on at this point is to go ahead and do all my development work (with early binding) on my Win7SP1 box, and then recompile the whole app on a WinXP box before deploying it to my users.

Answer 2

You are better off just completing the ADO Connection object and connecting to SQL Server that way. Set the ConnectionString property of the connection object and open it. Don't bother using CurrentProject.Connection. All you are trying to do in that case is declare a connection for a connection that already exists. Just declare the ADO connection fully and use it as it would be used from a VB or C++ application using ADO.