5

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.

Community
  • 1
  • 1
Hellion
  • 1,740
  • 28
  • 36
  • What if you replace `rst.open sql, db, adopenkeyset, adlockoptimistic` with `rst.open sql, currentproject.connection, adopenkeyset,adlockoptimistic`? Does that trigger the same error? – Tim Lentine May 31 '11 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. – Hellion May 31 '11 at 20:13
  • Still trying to understand ... which version of ActiveX Data Objects Library are you using? – Philippe Grondier May 31 '11 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. – Tim Lentine Jun 01 '11 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.) – Hellion Jun 02 '11 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. – Tim Lentine Jun 03 '11 at 13:13
  • @TimLentine, I posted some new information that you may find useful if you run across this issue again.... :-) – Hellion Jun 06 '11 at 19:01

5 Answers5

6

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.

Hellion
  • 1,740
  • 28
  • 36
  • 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! – Tim Lentine Jun 07 '11 at 13:11
  • 1
    @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!) – Hellion Jun 08 '11 at 13:36
  • great research! Thanks for posting. :) – Tim Lentine Jun 08 '11 at 13:57
  • +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. – Lynn Crumbling Dec 21 '11 at 19:46
1

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.

HardCode
  • 6,497
  • 4
  • 31
  • 54
  • 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. – HK1 Jun 01 '11 at 11:52
  • DAO is not usable in an ADP, which is what is involved in this question. – David-W-Fenton Jun 03 '11 at 03:20
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.

Umid
  • 1
0

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 ....

Community
  • 1
  • 1
Philippe Grondier
  • 10,900
  • 3
  • 33
  • 72
  • I do have it declared, and I do get the popups to complete ADODB.x methods and properties. – Hellion May 31 '11 at 20:49
  • Then are you sure that the 'set db' line is causing the type mismatch? This one is reeeeaaaallyyyy weird! – Philippe Grondier May 31 '11 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. – Hellion May 31 '11 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. – Hellion May 31 '11 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 ... – Philippe Grondier May 31 '11 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. – Hellion Jun 01 '11 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.... – Hellion Jun 01 '11 at 20:53
0

Try replacing:

rst.open sql, db, adopenkeyset, adlockoptimistic

with:

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

Tim Lentine
  • 7,782
  • 5
  • 35
  • 40