Access 2007: type mismatch when opening an ADO connection

Asked 31/5, 2011 at 17:2 Answered 18/1, 2013 at 8:7

Solved ms-access ms-access-2007

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.

Eli answered 31/5, 2011 at 17:2 Comment(7)

What if you replace rst.open sql, db, adopenkeyset, adlockoptimistic with rst.open sql, currentproject.connection, adopenkeyset,adlockoptimistic? Does that trigger the same error? – Chuchuah 31/5, 2011 at 17:59

@tim lentine, using the currentproject.connection directly in the rst.open call makes things work correctly. In fact it worked so well that at some point in the debugging process I switched back to the broken code above and it worked too.... (restoring to a "known-broken" copy of the database got it broken again, though.) I just don't know what internal bit might have gotten set to render the broken code functional. – Eli 31/5, 2011 at 20:13

Still trying to understand ... which version of ActiveX Data Objects Library are you using? – Mccurdy 31/5, 2011 at 22:12

@Hellion: I wish I had an answer for you. I've had this happen to me before as well which is why I suggested it. If you find out the root cause please post an answer. – Chuchuah 1/6, 2011 at 12:45

@Tim Lentine, if you want to re-post your comment as an answer, I'll go ahead and accept it, since it was your idea that led me to a suitable workaround: eliminate the 'db' variable and use CurrentProject.Connection directly. (No clue on the root cause, unfortunately.) – Eli 2/6, 2011 at 20:28

@Hellion, Just posted as an answer, but I feel kind of funny about it. I wish I had a reason for why it worked \ why you were getting the error in the first place, so don't feel like you have to accept my post as the answer. – Chuchuah 3/6, 2011 at 13:13

@TimLentine, I posted some new information that you may find useful if you run across this issue again.... :-) – Eli 6/6, 2011 at 19: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.

Eli answered 6/6, 2011 at 19:0 Comment(3)

that is very interesting. I wonder what would happen if you were to use late binding instead of setting a reference to ADO. I suspect that when the code is compiled on Windows 7 and executed on Windows XP that it would work properly. I also wonder if this is a Windows 7 issue OR if this is a 64 Bit issue. Either way, thanks for doing the research! – Chuchuah 7/6, 2011 at 13:11

@TimLentine, more info and links added to the answer. Looks like this is actually a known issue with W7 SP1's Data Access Components, you just can't find it when you're busy searching for "Run-time error 13 or 430". :-) (Also, late binding is indeed one of the official suggestions!) – Eli 8/6, 2011 at 13:36

+1 for great detail with later edits. Kudos to you for leading me straight to the kb article detailing this. I ended up installing their backward compatible typelib and recompiling against that; xp boxes now work again. – Maleki 21/12, 2011 at 19:46

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.

Chore answered 31/5, 2011 at 20:45 Comment(2)

Sorry, but I'm with HardCode on this one. If you're using ADO I'm not sure why you'd try to use the CurrentProject's connection. If you were using DAO I think it would make more sense. – Lardy 1/6, 2011 at 11:52

DAO is not usable in an ADP, which is what is involved in this question. – Romaine 3/6, 2011 at 3:20

Do you have the corresponding Micorsoft ActiveX Data Objects Library declared in your tools? Do you get any popup window in your code editor when writing down 'ADODB.', with all ADODB methods, objects and properties listed in the combobox?

After your comments, the problem is then that db is expecting an ADODB.connection object and currentProject.connection is from another object type! Really weird, is not it?

Could you please make some debugging with typeOf\typeName commands and check the exact nature of currentProject.connection. In my tests, I am getting the following results in the debugging window:

? typeOf currentproject.Connection is ADODB.Connection
True

? typeName(currentproject.Connection)
Connection

Which is logical, and I am not getting your bug ....

Mccurdy answered 31/5, 2011 at 20:46 Comment(7)

I do have it declared, and I do get the popups to complete ADODB.x methods and properties. – Eli 31/5, 2011 at 20:49

Then are you sure that the 'set db' line is causing the type mismatch? This one is reeeeaaaallyyyy weird! – Mccurdy 31/5, 2011 at 20:58

Given that the "Debug" button jumps directly to this highlighted line, and that commenting it out allows execution to proceed successfully, it seems pretty likely that this particular line is indeed the issue. – Eli 31/5, 2011 at 21:10

I did that very thing in response to a now-deleted answer, and the answers both came back as you anticipated ('true','connection'). I do have a workaround available now, which I'm going with for the moment rather than seriously re-examine the code, which is to do the following: dim db as ADODB.connection; set db = new ADODB.connection; set db = currentproject.connection That extra "set to new" seems to get the trick done. – Eli 31/5, 2011 at 21:40

I have already thought about this one, but, as the 'set db' instruction refers to an already existing connection object, going through the 'New' step sounds strange to me: you do not want to create a new instance of an ADODB.connection here, and my code works perfectly without this 'New' line...). I remember having similar doubts once, but I think it was with the ADOX objects. Anyway ... – Mccurdy 31/5, 2011 at 21:58

The code works perfectly fine without the "new" step everywhere except on WinXP with Access 2007, so I'm choosing to blame that particular combination. I have no idea what aspect of that combination is causing the problem, though. – Eli 1/6, 2011 at 14:17

To make it even more frustrating, after testing my workaround myself and pushing it out to my users to test, the very line of code that I added to make things work suddenly started causing a new error (error 430, I think it was.) At that point I threw my hands up and went with Tim lentine's implied suggestion of not bothering to declare a new variable, and just using currentproject.connection directly. So far so good.... – Eli 1/6, 2011 at 20:53

Try replacing:

rst.open sql, db, adopenkeyset, adlockoptimistic

with:

rst.open sql, currentproject.connection, adopenkeyset,adlockoptimistic?

Chuchuah answered 3/6, 2011 at 13:12 Comment(0)

In VBA editor go to the Tools-Preferences, and disable "MS ActiveX Data Objects 2.8 Library" then enable "MS ActiveX Data Objects 2.7 Library" works. For me worked, at least.

Redhanded answered 18/1, 2013 at 8:7 Comment(0)

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags