Solved

Type Mismatch Error 13 after conversion to Access 20010 from 2003

Posted on 2014-12-24
26
166 Views
Last Modified: 2014-12-25
Hi Experts,
I have just converted a 2003 Access application to 2010 and when I began testing encountered Type Mismatch Error 13 in the following code between Message Test 3 and Message Test 4.
Dim DB_020 As Database
MsgBox "Test 1"
Dim RS_00_EMAIL_EXPORT_DATA_020 As Recordset
MsgBox "Test 2"
Set DB_020 = CurrentDb
MsgBox "Test 3"
Set RS_00_EMAIL_EXPORT_DATA_020 = DB_020.OpenRecordset("00_EMAIL_EXPORT_DATA, dbOpenTable")
MsgBox "Test 4"

Has the required code changed and if so can you suggest what it should be?

I am also attaching a file containing the VBA References from the 2003 version and the converted 2010 version.  Are there any that are missing / incorrect?

Thanks.
Bob Collison
EE-Question-VBA-References.docx
0
Comment
Question by:Bob_Collison
  • 12
  • 7
  • 6
  • +1
26 Comments
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 340 total points
Comment Utility
have a look at here

https://support.microsoft.com/kb/181542?wa=wsignin1.0

it says you should use full qualified object reference... like this:

Dim adoRS As ADODB.Recordset
Dim daoRS As DAO.Recordset
0
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 340 total points
Comment Utility
i see you both reference to ADO & DOA

so, you need to be specific in your code...

Dim RS_00_EMAIL_EXPORT_DATA_020 As Recordset
>>>
Dim RS_00_EMAIL_EXPORT_DATA_020 As ADO.Recordset
or
Dim RS_00_EMAIL_EXPORT_DATA_020 As DOA.Recordset
0
 

Author Comment

by:Bob_Collison
Comment Utility
Hi Experts,
Thanks to both of you for the information.

However I'm not sure whether I just need both ADO and DOA References / code as I don't know what the difference / reason is for each of them.

I also am not sure what the References are for each of these.

Could you possibly explain / advise?

Thanks.
Bob Collison
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 140 total points
Comment Utility
You have a typo here
Set RS_00_EMAIL_EXPORT_DATA_020 = DB_020.OpenRecordset("00_EMAIL_EXPORT_DATA, dbOpenTable")
should likely be
Set RS_00_EMAIL_EXPORT_DATA_020 = DB_020.OpenRecordset("00_EMAIL_EXPORT_DATA", dbOpenTable)

Your error is a 'type mismatch'
This is it's most likely cause -- with the misplaced quote you have no type of Open, and an invalid table too
0
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 340 total points
Comment Utility
not sure but I guess access users are using DAO for ages...
ADO adds some performance but lacks of some flexibility... there are lots of debates here & there...

maybe just sticking to DAO and get familiar with ADO is best way...

have a look at this ms forum

also "ADO Compared with RDO and DAO"

and this "Choosing between DAO and ADO"
0
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 340 total points
Comment Utility
+1 Nick67

How come I missed that part :)
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 140 total points
Comment Utility
Could you possibly explain / advise?
ADO does many of the same jobs DAO does, but uses a different syntax and is better geared for an internet/WAN type of situation.  ADO allows you to pull data from a source, disconnect from it, work with your data and then push it back to its source while checking if things had changed since you pulled it.  DAO is much more steadfastly connected.

ADO was the default library for data access in Access 2002 and is the required technology for Access Data Projects (.adp) which have now been deprecated (many folks are grieved by this!)  ADO does all data processing on the server end.  None of the data comes to Access to be processed.

MS has since reversed direction and made DAO paramount in Access again.  So why use ADO at all?  IF you have the required SQL Server skills and access, it can be faster and more efficient.  And there are certain esoteric tasks that can ONLY be accomplished with ADO.  But, bottom line, it is not the technology to put your primary focus on for use in Access
0
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 340 total points
Comment Utility
i guess that typo


You have a typo here
Set RS_00_EMAIL_EXPORT_DATA_020 = DB_020.OpenRecordset("00_EMAIL_EXPORT_DATA, dbOpenTable")
should likely be
Set RS_00_EMAIL_EXPORT_DATA_020 = DB_020.OpenRecordset("00_EMAIL_EXPORT_DATA", dbOpenTable)

is not the main issue here...

just remove ado reference and run your application... if it works then you are good...
if not, leave ado remove dao and try again...

if it fails again, add ado or doa in front of all your objects to get rid of that error... but first get familier with those technologies and get familiar where and how to use which one...
0
 
LVL 40

Assisted Solution

by:Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger) earned 20 total points
Comment Utility
DAO (Microsoft DAO Library) was specifically created for Access, and thus allows you to easily get to all the features of Access, and uses a terminology that is closer to Access. As an example, you can use a QueryDef object to work with the definition of a Query. In most situations, if your are using only data that comes from Access in your application, using DAO makes it easier to code in Access. One exception is you are coding for the Web. DAO came before Microsoft saw the potential of the Web, and does not have the necessary features to work in a disconnected mode.

ADO (Microsoft ActiveX Data Objects Library) was designed as a more universal object library. You can use it with almost any modern database (SQL Server, Oracle, etc.). Because it is universal, there are features in Access that you cannot use with it. The terminology is also universal. You won't see the word Query in ADO classes, because the equivalent or queries are called views or stored procedures in other databases. If you are dealing with data from different type of databases or if you are coding for the web, ADO is usually a better solution than DAO.

You can also mix both in the same application, doing the pure Access stuff with DAO and going outside of Access with ADO. But this usually make things confusing. Both libraries have a lot in common, but with little differences. They both have Recordset classes, that do not behave exactly the same way. So working on an application that uses both, you have to be very careful when you look at code that was already written, in order to make the difference between ADO and DAO code.
0
 

Author Comment

by:Bob_Collison
Comment Utility
Hi Experts,
Thanks for the explanation.

Can you advise which References are applicable to DAO and ADO (in those I am using) so that I can try each way as you suggest?

It appears that my best bet as a 'standard' is ADO. Does this make sense?

Thanks.
Bob Collison.
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 140 total points
Comment Utility
First:
Was this how this line appeared in the code
Set RS_00_EMAIL_EXPORT_DATA_020 = DB_020.OpenRecordset("00_EMAIL_EXPORT_DATA, dbOpenTable")

Have you fixed the typo if that was accurate?
Did the error then go away?
0
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 340 total points
Comment Utility
no :) use DAO! remove ADO reference and compile & run your app first...

did you check this "Choosing between DAO and ADO"

Since Access 2003, DAO has returned to be the default library in Access. This includes Access 2007. The ADO library is no longer referenced when creating a new .MDB or .ACCDB in A2007. In Access 2007 Microsoft has included more features in DAO (ACEDAO) to allow you to work with the new features of the enhanced database engine (.ACCDB)

ypur code probably was working with DAO and now added reference to ADO and suddenly lots of problems show up...

just remove reference of ADO and you should be fine...
0
 

Author Comment

by:Bob_Collison
Comment Utility
Hi Experts,

I have changed the code to the following with the References for Access 2010 as per my attachment and it works correctly with the following code.
Dim DB_020 As Database
MsgBox "Test 1"
Dim RS_00_EMAIL_EXPORT_DATA_020 As DAO.Recordset
MsgBox "Test 2"
Set DB_020 = CurrentDb
MsgBox "Test 3"
Set RS_00_EMAIL_EXPORT_DATA_020 = DB_020.OpenRecordset("00_EMAIL_EXPORT_DATA")
MsgBox "Test 4"

I still would like to know what Reference provides the DAO / ADO functions.

Thanks.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 340 total points
Comment Utility
follow that link: "Choosing between DAO and ADO" ^^^ @ ID: 40516793

still i did not get my answer: what happens if you just remove ADO reference and run your app! without changing any line of code!
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 140 total points
Comment Utility
I still would like to know what Reference provides the DAO / ADO functions.
Look in your image at the very beginning!
The 4th item is the Microsoft DAO 3.6 Object Library
The 5th item is the Microsoft ActiveX Data Objects (ADO) 2.5 library

DAO object have priority over ADO objects of the same name
The 4th item provides you with access to all the objects, constants and methods of DAO
The 5th item does the same for ADO
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 140 total points
Comment Utility
So
Set RS_00_EMAIL_EXPORT_DATA_020 = DB_020.OpenRecordset("00_EMAIL_EXPORT_DATA")
You have fixed any typo that was there and removed the optional Type string

from the help
In a Microsoft Jet workspace, if you don't specify a type, DAO attempts to create the type of Recordset with the most functionality available, starting with table. If this type isn’t available, DAO attempts a dynaset, then a snapshot, and finally a forward-only type Recordset object.
So you are getting either a dbOpenTable or dbOpenDynaset as a result.
And it works!
0
 

Author Comment

by:Bob_Collison
Comment Utility
Hi Experts,

I have the following code referencing ADO like this so I can't Just remove the ADO Reference.  I believe that this is the only ADO Statement in my Application although it is used many times for different Tables.
Dim RS_21_ORGANIZATION_MSTR_100 As New ADODB.Recordset

I tried replacing ADO with DAO in the statement and it doesn't work.

What is the equivalent DAO statement?

Thanks.
Bob Collison
0
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 340 total points
Comment Utility
what is happening if you use

Dim RS_21_ORGANIZATION_MSTR_100 As New ADODB.Recordset
>>>
Dim RS_21_ORGANIZATION_MSTR_100 As New DAO.Recordset

what error fo you get (after removing reference to ado)
0
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 340 total points
Comment Utility
see this article, it shows how to use ADO & DOA (equivalent statements)

Comparison of DAO and ADO Recordset Syntax
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 140 total points
Comment Utility
It is much, much more than a statement, I am afraid
What is the equivalent DAO statement?

This is DAO
Dim db as  DAO.Database
Dim rs as DAO.Recordset
Set db = Currentdb
Set rs = db.OpenRecordset("Select something from someTable;",dbOpenDynaset,dbSeeChanges)


This is ADO ( for opening a stored procedure which is different, but to give you an idea)
Dim dbCon As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
dbCon.ConnectionString = "Provider=SQLNCLI11;SERVER=MyServer\myInstance;DATABASE=TI_Data;Integrated Security=SSPI"
dbCon.Open
With cmd
    .CommandType = adCmdStoredProc
    .CommandText = "MyStoredProcedure"
    .Parameters.Append .CreateParameter("@MyParameter", adInteger, adParamInput, Me.JobID, 0)
    .ActiveConnection = dbCon
    .NamedParameters = True
    Set rst = .Execute
End With


It's not a one-line alteration!
0
 
LVL 51

Accepted Solution

by:
HainKurt earned 340 total points
Comment Utility
it is not that bad :)

DAO
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblPeople", dbOpenDynaset)
' Set rs = CurrentDb.OpenRecordset("SELECT pkPeopleID, LastName FROM tblPeople", dbOpenDynaset)

Open in new window


ADO
Dim rs As New ADODB.Recordset
rs.Open "tblPeople", CurrentProject.Connection, adOpenStatic
'rs.Open "SELECT pkPeopleID, LastName FROM tblPeople", CurrentProject.Connection, adOpenStatic

Open in new window


it is just one line
0
 

Author Comment

by:Bob_Collison
Comment Utility
Hi Expert,

Thanks for your link to the syntax.  I'll have to do some reading and testing.

Unfortunately I have to go out soon so I won't be back to you probably for a couple of days.

Thanks again for everything so far.
Bob Collison
0
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 340 total points
Comment Utility
i just dont get what are you trying to do here...
your app was working with just DAO, right? and now there are two refeernces (after upgrade) and there are issues...
removing one reference (which is ADO) should fix everything...
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 140 total points
Comment Utility
@HainKurt
Clearly not:
I have the following code referencing ADO like this so I can't Just remove the ADO Reference.  I believe that this is the only ADO Statement in my Application although it is used many times for different Tables.
 Dim RS_21_ORGANIZATION_MSTR_100 As New ADODB.Recordset


Very clearly, the app used a mix of DAO and ADO and requires both references.
I think the only issue was the typo.

@Bob Collison
The conversion of ADO to DAO code is possible, but not trivial and may or may not lead to increased or decreased performance.

If it ain't broke anymore, I wouldn't try to fix it!
0
 
LVL 51

Assisted Solution

by:HainKurt
HainKurt earned 340 total points
Comment Utility
what i see is this:

- references may stay
- no code change required other than adding "DAO." or "ADO." in front of some objects to prevent confusion (and you should know by now which one is which :)

not a big deal...
0
 

Author Comment

by:Bob_Collison
Comment Utility
Hi Experts,
I have completed testing of a small Application with the code you have suggested which is all DAO as I don't have any connections to the Web or via ODBC.

With regard to the References.  During conversion from 2003 to 2010 it appears that the Microsoft DAO 3.5 Object (DAO) Library was de-selected leaving just the Microsoft ActiveX Data Objects (ADO) 2.5 Library.  To facilitate my using the DAO Option only I deselected the ADO and selected the DAO references.  Everything appears to work correctly.

Thanks for your patience and assistance.
Bob Collison
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

zlib is a free compression library (a DLL) on which the popular gzip utility is built.  In this article, we'll see how to use the zlib functions to compress and decompress data in memory; that is, without needing to use a temporary file.  We'll be c…
A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now