Type Mismatch Error 13 after conversion to Access 20010 from 2003

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
Bob CollisonSystem ArchitectAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HainKurtSr. System AnalystCommented:
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
HainKurtSr. System AnalystCommented:
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
Bob CollisonSystem ArchitectAuthor Commented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Nick67Commented:
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
HainKurtSr. System AnalystCommented:
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
HainKurtSr. System AnalystCommented:
+1 Nick67

How come I missed that part :)
0
Nick67Commented:
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
HainKurtSr. System AnalystCommented:
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
Jacques Bourgeois (James Burger)PresidentCommented:
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
Bob CollisonSystem ArchitectAuthor Commented:
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
Nick67Commented:
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
HainKurtSr. System AnalystCommented:
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
Bob CollisonSystem ArchitectAuthor Commented:
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
HainKurtSr. System AnalystCommented:
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
Nick67Commented:
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
Nick67Commented:
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
Bob CollisonSystem ArchitectAuthor Commented:
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
HainKurtSr. System AnalystCommented:
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
HainKurtSr. System AnalystCommented:
see this article, it shows how to use ADO & DOA (equivalent statements)

Comparison of DAO and ADO Recordset Syntax
0
Nick67Commented:
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
HainKurtSr. System AnalystCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bob CollisonSystem ArchitectAuthor Commented:
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
HainKurtSr. System AnalystCommented:
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
Nick67Commented:
@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
HainKurtSr. System AnalystCommented:
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
Bob CollisonSystem ArchitectAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.