The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.
Solved
Type Mismatch Error 13 after conversion to Access 20010 from 2003
Posted on 2014-12-24
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_02
MsgBox "Test 2"
Set DB_020 = CurrentDb
MsgBox "Test 3"
Set RS_00_EMAIL_EXPORT_DATA_02
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
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_02
MsgBox "Test 2"
Set DB_020 = CurrentDb
MsgBox "Test 3"
Set RS_00_EMAIL_EXPORT_DATA_02
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
12
7-
6
- +1
26 Comments
Active today
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
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
Active today
i see you both reference to ADO & DOA
so, you need to be specific in your code...
Dim RS_00_EMAIL_EXPORT_DATA_02
>>>
Dim RS_00_EMAIL_EXPORT_DATA_02
or
Dim RS_00_EMAIL_EXPORT_DATA_02
so, you need to be specific in your code...
Dim RS_00_EMAIL_EXPORT_DATA_02
>>>
Dim RS_00_EMAIL_EXPORT_DATA_02
or
Dim RS_00_EMAIL_EXPORT_DATA_02
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
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
You have a typo here
Set RS_00_EMAIL_EXPORT_DATA_02
should likely be
Set RS_00_EMAIL_EXPORT_DATA_02
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
Set RS_00_EMAIL_EXPORT_DATA_02
should likely be
Set RS_00_EMAIL_EXPORT_DATA_02
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
Active today
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"
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"
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
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
Active today
i guess that typo
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...
You have a typo here
Set RS_00_EMAIL_EXPORT_DATA_020 = DB_020.OpenRecordset("00_E MAIL_EXPOR T_DATA, dbOpenTable")
should likely be
Set RS_00_EMAIL_EXPORT_DATA_020 = DB_020.OpenRecordset("00_E MAIL_EXPOR T_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...
Active 4 days ago
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.
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.
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.
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.
First:
Was this how this line appeared in the code
Set RS_00_EMAIL_EXPORT_DATA_02
Have you fixed the typo if that was accurate?
Did the error then go away?
Was this how this line appeared in the code
Set RS_00_EMAIL_EXPORT_DATA_02
Have you fixed the typo if that was accurate?
Did the error then go away?
Active today
no :) use DAO! remove ADO reference and compile & run your app first...
did you check this "Choosing between DAO and ADO"
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...
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...
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_02
MsgBox "Test 2"
Set DB_020 = CurrentDb
MsgBox "Test 3"
Set RS_00_EMAIL_EXPORT_DATA_02
MsgBox "Test 4"
I still would like to know what Reference provides the DAO / ADO functions.
Thanks.
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_02
MsgBox "Test 2"
Set DB_020 = CurrentDb
MsgBox "Test 3"
Set RS_00_EMAIL_EXPORT_DATA_02
MsgBox "Test 4"
I still would like to know what Reference provides the DAO / ADO functions.
Thanks.
Active today
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!
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!
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
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
So
Set RS_00_EMAIL_EXPORT_DATA_02
You have fixed any typo that was there and removed the optional Type string
from the help
And it works!
Set RS_00_EMAIL_EXPORT_DATA_02
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!
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_10
I tried replacing ADO with DAO in the statement and it doesn't work.
What is the equivalent DAO statement?
Thanks.
Bob Collison
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_10
I tried replacing ADO with DAO in the statement and it doesn't work.
What is the equivalent DAO statement?
Thanks.
Bob Collison
Active today
what is happening if you use
Dim RS_21_ORGANIZATION_MSTR_10
>>>
Dim RS_21_ORGANIZATION_MSTR_10
what error fo you get (after removing reference to ado)
Dim RS_21_ORGANIZATION_MSTR_10
>>>
Dim RS_21_ORGANIZATION_MSTR_10
what error fo you get (after removing reference to ado)
Active today
see this article, it shows how to use ADO & DOA (equivalent statements)
Comparison of DAO and ADO Recordset Syntax
Comparison of DAO and ADO Recordset Syntax
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,
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
dbCon.Open
With cmd
.CommandType = adCmdStoredProc
.CommandText = "MyStoredProcedure"
.Parameters.Append .CreateParameter("@MyParam
.ActiveConnection = dbCon
.NamedParameters = True
Set rst = .Execute
End With
It's not a one-line alteration!
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,
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
dbCon.Open
With cmd
.CommandType = adCmdStoredProc
.CommandText = "MyStoredProcedure"
.Parameters.Append .CreateParameter("@MyParam
.ActiveConnection = dbCon
.NamedParameters = True
Set rst = .Execute
End With
It's not a one-line alteration!
Active today
it is not that bad :)
DAO
ADO
it is just one line
DAO
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblPeople", dbOpenDynaset)
' Set rs = CurrentDb.OpenRecordset("SELECT pkPeopleID, LastName FROM tblPeople", dbOpenDynaset)
ADO
Dim rs As New ADODB.Recordset
rs.Open "tblPeople", CurrentProject.Connection, adOpenStatic
'rs.Open "SELECT pkPeopleID, LastName FROM tblPeople", CurrentProject.Connection, adOpenStatic
it is just one line
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
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
Active today
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...
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...
@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_10
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!
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_10
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!
Active today
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...
- 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...
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
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
Featured Post
This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
With most software applications trying to cater to multiple user needs nowadays, the focus is to make them as configurable as possible. For e.g., when creating Silverlight applications which will connect to WCF services, the service end point usuall…
For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared 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…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Suggested Courses
Course of the Month8 days, 18 hours left to enroll
721 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.