Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-30-2008, 03:19 PM   #1
cpremo
Registered User
 
Join Date: Jun 2007
Posts: 50
Thanks: 0
Thanked 0 Times in 0 Posts
cpremo is on a distinguished road
Angry type mismatch - 13

I'm getting this error message on an Access 2007 run database that was coded originally in Access 2003. If I run the report on an Office 2003 PC no problem. If I do it on an Office 2007 PC I get the error.

Mirosoft had this Information

Code:
 
 
http://support.microsoft.com/kb/927678/en-us
 
 
Error message when you convert a database file from an earlier version of Access into an Access 2007 file: "Run-time error '13': Type mismatch"
View products that this article applies to.
Article ID : 927678 
Last Review : October 15, 2007 
Revision : 1.3 
On This Page
SYMPTOMS
CAUSE
WORKAROUND
Method 1
Method 2
Method 3
STATUS
SYMPTOMS
When you convert a database file from an earlier version of Microsoft Access into a Microsoft Office Access 2007 file, the application may stop responding if you try to run or compile code that uses Data Access Objects (DAO). Additionally, you may receive the following error message:
Run-time error 13: 
Type mismatch
Back to the top
CAUSE
This problem occurs because the order of database references is not preserved when the database file is converted.
Back to the top
WORKAROUND
To work around this problem, use one of the following methods:
Back to the top
Method 1
In the References dialog box, make sure that Microsoft Office 2007 Access database engine Object Library is higher in priority than Microsoft ActiveX Data Objects. To do this, follow these steps:1. Start Access 2007. 
2. Click the Microsoft Office Button, and then click Open. 
3. In the Open dialog box, locate and select <DatabaseName>, and then click Open. 
4. On the Database Tools menu, click Visual Basic in the Macro group to open Visual Basic Editor (VBE). 
Note You may also press ALT+F11 to open VBE. 
5. On the Tools menu, click References. 
6. In the References - <DatabaseName> dialog box, click Microsoft Office 2007 Access database engine Object Library. 
7. Click the Priority arrow to move Microsoft Office 2007 Access database engine Object Library above Microsoft ActiveX Data Objects, and then click OK. 
Back to the top
Method 2
If you must reference and use both DAO and ActiveX Data Objects (ADO) object libraries, dimension the objects explicitly as follows: Dim adoRS As ADODB.Recordset 
Dim daoRS As DAO.Recordset
Back to the top
Method 3
If you are not using an ADO object library, clear the reference to the library. To do this, follow these steps:1. Start Access 2007. 
2. Click the Microsoft Office Button, and then click Open. 
3. In the Open dialog box, locate and select <DatabaseName>, and then click Open. 
4. On the Database Tools menu, click Visual Basic in the Macro group to open VBE. 
Note You may also press ALT+F11 to open VBE. 
5. On the Tools menu, click References. 
6. In the References - <DatabaseName> dialog box, click to clear Microsoft ActiveX Data Objects, and then click OK.

This is the actual code that it Bombs out on:

Code:
 
Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Report_Open
    Dim rst As ADODB.Recordset
    Dim fld As ADODB.Field
    Dim ctl As Control
    Dim X As Integer
    
    Set rst = New ADODB.Recordset
    rst.Open "[Server NLM Files_Crosstab]", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    
    With rst
        For Each fld In rst.Fields
            For Each ctl In Me.Controls
                If TypeOf ctl Is TextBox And ctl.Name = X Then
                    If fld.Name = "NLM Name" Then
                        X = X - 1
                    End If
                    ctl.ControlSource = fld.Name
                    Exit For
                ElseIf TypeOf ctl Is Label And Right(ctl.Name, 2) = X Then
                    ctl.Caption = fld.Name
                End If
            Next ctl
            X = X + 1
        Next fld
    End With
Exit_Report_Open:
    Exit Sub
Err_Report_Open:
    MsgBox Err.Description & "  -  " & Err.Number
    Resume Exit_Report_Open
    
End Sub

cpremo is offline   Reply With Quote
Old 07-05-2008, 09:47 PM   #2
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,819 Times in 1,576 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Try going into the VBA Window and selecting TOOLS > REFERENCES and make sure your ADO reference is listed above the ACE Engine (the new DAO) reference. If not, move it up by using the up buttons on the right of the list box that shows the references.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 07-06-2008, 07:24 AM   #3
LPurvis
AWF VIP
 
LPurvis's Avatar
 
Join Date: Jun 2008
Location: North of England
Posts: 1,269
Thanks: 0
Thanked 33 Times in 33 Posts
LPurvis has a spectacular aura about LPurvis has a spectacular aura about LPurvis has a spectacular aura about
Interesting though - given that your ADO object references are already disambiguated (Dim rst As ADODB.Recordset) and that the rest not only compiled - but executed fine in 2003.

There are occasions in your code where you're relying on an implicit type conversion.
However VBA code very often has this.
Where I've found it prone to fail implicit conversion is indeed between typed bound data values (usually recordset values) and variables (or literals).
The difference here is that you're comparing a numeric value against a string - but that's known at compile time. (You don't mention when you get your error - or upon which line it occurs - both are truly vital pieces of information that you should always look to include).

I notice you're expecting your controls to, potentially, have numeric names?
That's something I'd discourage personally. It's trivialy to include (and so then allow for) a text prefix - and I'd say you already have that in at least some cases. Your controls have a numeric ordinal position in their collection already - and IMO naming them numerically just introduced the possibility for innacurate referncing.

Anyway - more information if you can...
And might as well be explicit in your comparisons...
e.g.
instead of occasions like
ctl.Name = X
having
ctl.Name = CStr(X)

(Your Control declaration should be fine - being impossible to assign an optional library above the Access one, but you could still declare it as
Dim ctl as Access.Control).

Incidentally - is this an ADP or an MDB (/ACCDB)?
For your needs (preparing a report to receive crosstab data on the fly) you likely don't need a full recordset.
So your line
rst.Open "[Server NLM Files_Crosstab]", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
could be
rst.Open "[Server NLM Files_Crosstab]", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
which is analogous to
Set rst = CurrentProject.Connection.Execute("[Server NLM Files_Crosstab]")
which could even be limited - e.g.
Set rst = CurrentProject.Connection.Execute("SELECT * FROM [Server NLM Files_Crosstab] WHERE 1=0")
Obviously - Jet has still had to parse and execute the crosstab to get the results, but at least doesn't bother filling the recordset with the data.

Cheers.

__________________
Leigh Purvis | Microsoft Access MVP |
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
LPurvis is offline   Reply With Quote
Old 04-26-2011, 05:53 AM   #4
mnissl
Newly Registered User
 
Join Date: Apr 2011
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
mnissl is on a distinguished road
Re: type mismatch - 13

Although this original post is already nearly three years old, I have now a similar problem:

I have an ADP project that is using ADO and was running fine under Access 2003.

I'm now using Access 2007 and I get this runtime error 13 "Type mismatch" when I try to do this:

Dim c as ADODB.Connection
Set c = CurrentProject.Connection

I would like to develop my application under Windows 7 and deliver it to the customer where the combinations of Windows XP/7 and Access 2007 full/runtime version are in use.

As explained in KB927678, there is an issue with the order of databases reference.

And as mentioned at an MSDN forum (as I have less than 10 posts yet, I may not post the link here ), disabling and enabling the references is important when making the transition to 2007.

But one more observation I made is: you have to create the ADE file using Windows XP and Access 2007 if you want the file to work using Windows XP and the runtime version of Access 2007. When I create the ADE file under Windows 7 and Access 2007, it won't work under Windows XP and the runtime version of Access 2007: "Type mismatch" in the above mentioned example.

I assume this also has to do with the fact that Windows 7 ships with ADO 6 whereas Windows XP uses 2.8.

Is there I way I can develop under Windows 7 and support both Windows XP/7 and Access 2007 full/runtime version?
mnissl is offline   Reply With Quote
Old 04-26-2011, 06:03 AM   #5
LPurvis
AWF VIP
 
LPurvis's Avatar
 
Join Date: Jun 2008
Location: North of England
Posts: 1,269
Thanks: 0
Thanked 33 Times in 33 Posts
LPurvis has a spectacular aura about LPurvis has a spectacular aura about LPurvis has a spectacular aura about
Re: type mismatch - 13

Would this be Windows 7 with SP1 installed?
__________________
Leigh Purvis | Microsoft Access MVP |
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
LPurvis is offline   Reply With Quote
Old 04-26-2011, 06:07 AM   #6
mnissl
Newly Registered User
 
Join Date: Apr 2011
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
mnissl is on a distinguished road
Re: type mismatch - 13

Quote:
Originally Posted by LPurvis View Post
Would this be Windows 7 with SP1 installed?
Yes. I have also applied the latest service packs and updates to Office 2007 through Windows Update.
mnissl is offline   Reply With Quote
Old 04-26-2011, 06:33 AM   #7
LPurvis
AWF VIP
 
LPurvis's Avatar
 
Join Date: Jun 2008
Location: North of England
Posts: 1,269
Thanks: 0
Thanked 33 Times in 33 Posts
LPurvis has a spectacular aura about LPurvis has a spectacular aura about LPurvis has a spectacular aura about
Re: type mismatch - 13

Yep. In this case Win 7 SP1 is not a good bit of news.
http://social.msdn.microsoft.com/For...?prof=required
It's extremely disappointing.

Specifically the quote:
If you have to re-compile your application on SP1, there are several workarounds:
  1. Request a package of KB983246 and install it on your customers' machines. Re-compiled application should work after the package is installed.
  2. Re-write your application to use later binding to ADO, or use interfaces with name xxx_deprecated.
  3. Keep an old version of ADO typelib (i.e., msado28.tlb) (copy from Windows 7 RTM), then compile your application with the old typelib, instead of the one in your system.

__________________
Leigh Purvis | Microsoft Access MVP |
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
LPurvis is offline   Reply With Quote
The Following User Says Thank You to LPurvis For This Useful Post:
mnissl (04-28-2011)
Old 04-28-2011, 03:00 AM   #8
mnissl
Newly Registered User
 
Join Date: Apr 2011
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
mnissl is on a distinguished road
Re: type mismatch - 13

Quote:
Originally Posted by LPurvis View Post
Yep. In this case Win 7 SP1 is not a good bit of news.
Well, I forgot to add that I use Windows 7 64 bit ... so, indeed this issue applies to my constellation. Thanks so much for this hint! Searching for "access 2007 vba type mismatch currentproject.connection" would have never pointed me to the KB article that you mentioned!

Quote:
Originally Posted by LPurvis View Post
Keep an old version of ADO typelib (i.e., msado28.tlb) (copy from Windows 7 RTM), then compile your application with the old typelib, instead of the one in your system.
I would like to opt for this solution. I thought I simply copy msado28.tlb from another machine (without SP1), put it into the folder where I keep the adp file and reference to this tlb file from the "External References" dialog. Alas, when I select the file, it won't show up in the references list.

Is this due to the fact that the original tlb is still registered in the system? Will I have to replace msado28.tlb at its original place (C:\Program Files (x86)\Common Files\System\ado\) so that I can use it from within Access?
mnissl is offline   Reply With Quote
Old 04-28-2011, 03:37 AM   #9
LPurvis
AWF VIP
 
LPurvis's Avatar
 
Join Date: Jun 2008
Location: North of England
Posts: 1,269
Thanks: 0
Thanked 33 Times in 33 Posts
LPurvis has a spectacular aura about LPurvis has a spectacular aura about LPurvis has a spectacular aura about
Re: type mismatch - 13

Have you replaced and registered it?

Start > Run
RegSvr32 "C:\Program Files (x86)\Common Files\System\ado\msado28.tlb"

ADO doesn't necessarily use the matching component though. It's described here:
http://msmvps.com/blogs/access/archi...o-because.aspx

That shouldn't be a problem though, if you re-register the files you've copied over.

Cheers.
__________________
Leigh Purvis | Microsoft Access MVP |
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
LPurvis is offline   Reply With Quote
Old 04-28-2011, 03:53 AM   #10
mnissl
Newly Registered User
 
Join Date: Apr 2011
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
mnissl is on a distinguished road
Re: type mismatch - 13

Quote:
Originally Posted by LPurvis View Post
Have you replaced and registered it?
I have not. I know about registering ActiveX components to be known to the system. But that way the operating system tells the applications which class IDs are available. Applications do not have to bother about file system paths because they ask the OS to instantiate the objects.

So what is then the intention of the browse button in the "References" dialog of VBA? What happens if you browse for a tlb file?
mnissl is offline   Reply With Quote
Old 05-06-2011, 04:29 AM   #11
LPurvis
AWF VIP
 
LPurvis's Avatar
 
Join Date: Jun 2008
Location: North of England
Posts: 1,269
Thanks: 0
Thanked 33 Times in 33 Posts
LPurvis has a spectacular aura about LPurvis has a spectacular aura about LPurvis has a spectacular aura about
Re: type mismatch - 13

The type library must be registered with windows. But not every registered library is listed in the VBA references dialog. The Browse button allows you to select any such references. (They're then added to the references list for that project... while it's selected).

__________________
Leigh Purvis | Microsoft Access MVP |
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
LPurvis is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Wierd type mismatch poporacer Forms 9 05-13-2008 08:55 AM
Type mismatch error belsha Modules & VBA 0 04-28-2004 08:39 AM
Outlook automation type mismatch pbaldy Modules & VBA 2 12-11-2003 01:49 PM
Type mismatch Mechele Modules & VBA 2 07-25-2003 10:45 AM
Type Mismatch on recordset StephenS Modules & VBA 3 05-30-2000 08:50 AM




All times are GMT -8. The time now is 06:55 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World