Query error after converting

I'm running Access 2003.

I have a working database.  It seems as if the database is in Access
2000 format, because when I go to Convert, the 'Convert to Access 2000'
option is grayed out.

I need to convert this database to both Access 2002-2003 and Access 97.

I have this part of VB code, and it works fine in the current version,
as well as in Access 2002-2003 (after converting to Access 2002-2003).


Dim rstRoutes As Recordset
Dim strSQL As String

......

strSQL = "SELECT DISTINCT [Route] " & _
         "FROM Highways_Info " & _
         "WHERE Highways_Info.District = " & DistrictBox.Value

Set rstRoutes = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

......

However, after converting from 2000 to 97, the code generates a
run-time error '13' : Type Mismatch.  The last line, where I try to
open the recordset is where it crashes.

DistrictBox is a combo box from a form.  It contains a numerical value.
The form is open, and when debugging, DistrictBox.Value does have a
valid value.

No changes were made in the two databases, other than the conversion
from Access 2000 to 97, using Access' built-in tool.  It seems as if
the problem lies with Access, not my code.  

Can anyone help?

0
truleuneek
10/11/2006 5:44:37 PM
access.conversion 3037 articles. 0 followers. Follow

3 Replies
788 Views

Similar Articles

[PageSpeed] 6

I'm actually surprised that it works in Access 2003, since Access 2003 has 
references set to both ADO and DAO by default.

Recordset is an object in both models. To ensure that you're getting the 
correct Recordset object, you need to use Dim rstRoutes As DAO.Recordset (or 
Dim rstRoutes As ADODB.Recordset if you want an ADO recordset, although the 
rest of your code is using DAO)

However, Access 97 by default only has a reference to DAO, so that shouldn't 
be the cause of your problem. Still, with any code module open, select Tools 
| References from the menu bar. Is there a selected reference for Microsoft 
ActiveX Data Objects 2.x Library (any version)? If so, uncheck it and see 
whether it works.

For what it's worth, you really don't need to worry about converting it to 
Access 2002-2003 unless you're planning on converting it to an MDE. The 
Access 2000 file format is actually the default for Access 2003 (and Access 
2002, for that matter)

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


<truleuneek@gmail.com> wrote in message 
news:1160588677.537558.142890@h48g2000cwc.googlegroups.com...
> I'm running Access 2003.
>
> I have a working database.  It seems as if the database is in Access
> 2000 format, because when I go to Convert, the 'Convert to Access 2000'
> option is grayed out.
>
> I need to convert this database to both Access 2002-2003 and Access 97.
>
> I have this part of VB code, and it works fine in the current version,
> as well as in Access 2002-2003 (after converting to Access 2002-2003).
>
>
> Dim rstRoutes As Recordset
> Dim strSQL As String
>
> .....
>
> strSQL = "SELECT DISTINCT [Route] " & _
>         "FROM Highways_Info " & _
>         "WHERE Highways_Info.District = " & DistrictBox.Value
>
> Set rstRoutes = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
>
> .....
>
> However, after converting from 2000 to 97, the code generates a
> run-time error '13' : Type Mismatch.  The last line, where I try to
> open the recordset is where it crashes.
>
> DistrictBox is a combo box from a form.  It contains a numerical value.
> The form is open, and when debugging, DistrictBox.Value does have a
> valid value.
>
> No changes were made in the two databases, other than the conversion
> from Access 2000 to 97, using Access' built-in tool.  It seems as if
> the problem lies with Access, not my code.
>
> Can anyone help?
> 


0
Douglas
10/11/2006 8:58:31 PM
That worked.  Thanks.

If you have the time and desire, can you tell me what the problem was?

The solution helped, but it won't really help me in the future unless I
understand this mistake and learn from it.

Thanks again.


Douglas J. Steele wrote:
> I'm actually surprised that it works in Access 2003, since Access 2003 has
> references set to both ADO and DAO by default.
>
> Recordset is an object in both models. To ensure that you're getting the
> correct Recordset object, you need to use Dim rstRoutes As DAO.Recordset (or
> Dim rstRoutes As ADODB.Recordset if you want an ADO recordset, although the
> rest of your code is using DAO)
>
> However, Access 97 by default only has a reference to DAO, so that shouldn't
> be the cause of your problem. Still, with any code module open, select Tools
> | References from the menu bar. Is there a selected reference for Microsoft
> ActiveX Data Objects 2.x Library (any version)? If so, uncheck it and see
> whether it works.
>
> For what it's worth, you really don't need to worry about converting it to
> Access 2002-2003 unless you're planning on converting it to an MDE. The
> Access 2000 file format is actually the default for Access 2003 (and Access
> 2002, for that matter)
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> <truleuneek@gmail.com> wrote in message
> news:1160588677.537558.142890@h48g2000cwc.googlegroups.com...
> > I'm running Access 2003.
> >
> > I have a working database.  It seems as if the database is in Access
> > 2000 format, because when I go to Convert, the 'Convert to Access 2000'
> > option is grayed out.
> >
> > I need to convert this database to both Access 2002-2003 and Access 97.
> >
> > I have this part of VB code, and it works fine in the current version,
> > as well as in Access 2002-2003 (after converting to Access 2002-2003).
> >
> >
> > Dim rstRoutes As Recordset
> > Dim strSQL As String
> >
> > .....
> >
> > strSQL = "SELECT DISTINCT [Route] " & _
> >         "FROM Highways_Info " & _
> >         "WHERE Highways_Info.District = " & DistrictBox.Value
> >
> > Set rstRoutes = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
> >
> > .....
> >
> > However, after converting from 2000 to 97, the code generates a
> > run-time error '13' : Type Mismatch.  The last line, where I try to
> > open the recordset is where it crashes.
> >
> > DistrictBox is a combo box from a form.  It contains a numerical value.
> > The form is open, and when debugging, DistrictBox.Value does have a
> > valid value.
> >
> > No changes were made in the two databases, other than the conversion
> > from Access 2000 to 97, using Access' built-in tool.  It seems as if
> > the problem lies with Access, not my code.
> >
> > Can anyone help?
> >

0
truleuneek
10/11/2006 10:20:51 PM
If removing the reference to ADO solved the problem, then I repeat that I 
don't understand how it could have worked in Access 2003.

I already mentioned that Recordset is an object in both the ADO and DAO 
models. Access, like most Windows applications, makes use of external 
libraries for much of its functionality, and any external library it uses is 
listed in that References dialog. ADO and DAO are examples of two external 
libraries Access uses. When you refer to something in one of those external 
libraries, Access has to go looking for the referenced item. To do so, it 
generally goes through the libraries in the order in which they appear in 
the that dialog. In Access 2003, the default location of the ADO library is 
higher than the location of the DAO library. That means that if you simply 
say "Dim rstRoutes As Recordset", Access is going to find Recordset in the 
ADO library first, and stop looking any more. However, your code is using a 
DAO method to create the recordset (CurrentDb.OpenRecordset). If you try to 
assign a DAO recordset to a variable defined as an ADO recordset, you get 
errors. To resolve this, you need to "disambiguate" the reference as I 
showed before.

The list of objects with the same names in the 2 models is Connection, 
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties 
and Recordset


-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


<truleuneek@gmail.com> wrote in message 
news:1160605251.879328.63450@m7g2000cwm.googlegroups.com...
> That worked.  Thanks.
>
> If you have the time and desire, can you tell me what the problem was?
>
> The solution helped, but it won't really help me in the future unless I
> understand this mistake and learn from it.
>
> Thanks again.
>
>
> Douglas J. Steele wrote:
>> I'm actually surprised that it works in Access 2003, since Access 2003 
>> has
>> references set to both ADO and DAO by default.
>>
>> Recordset is an object in both models. To ensure that you're getting the
>> correct Recordset object, you need to use Dim rstRoutes As DAO.Recordset 
>> (or
>> Dim rstRoutes As ADODB.Recordset if you want an ADO recordset, although 
>> the
>> rest of your code is using DAO)
>>
>> However, Access 97 by default only has a reference to DAO, so that 
>> shouldn't
>> be the cause of your problem. Still, with any code module open, select 
>> Tools
>> | References from the menu bar. Is there a selected reference for 
>> Microsoft
>> ActiveX Data Objects 2.x Library (any version)? If so, uncheck it and see
>> whether it works.
>>
>> For what it's worth, you really don't need to worry about converting it 
>> to
>> Access 2002-2003 unless you're planning on converting it to an MDE. The
>> Access 2000 file format is actually the default for Access 2003 (and 
>> Access
>> 2002, for that matter)
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no private e-mails, please)
>>
>>
>> <truleuneek@gmail.com> wrote in message
>> news:1160588677.537558.142890@h48g2000cwc.googlegroups.com...
>> > I'm running Access 2003.
>> >
>> > I have a working database.  It seems as if the database is in Access
>> > 2000 format, because when I go to Convert, the 'Convert to Access 2000'
>> > option is grayed out.
>> >
>> > I need to convert this database to both Access 2002-2003 and Access 97.
>> >
>> > I have this part of VB code, and it works fine in the current version,
>> > as well as in Access 2002-2003 (after converting to Access 2002-2003).
>> >
>> >
>> > Dim rstRoutes As Recordset
>> > Dim strSQL As String
>> >
>> > .....
>> >
>> > strSQL = "SELECT DISTINCT [Route] " & _
>> >         "FROM Highways_Info " & _
>> >         "WHERE Highways_Info.District = " & DistrictBox.Value
>> >
>> > Set rstRoutes = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
>> >
>> > .....
>> >
>> > However, after converting from 2000 to 97, the code generates a
>> > run-time error '13' : Type Mismatch.  The last line, where I try to
>> > open the recordset is where it crashes.
>> >
>> > DistrictBox is a combo box from a form.  It contains a numerical value.
>> > The form is open, and when debugging, DistrictBox.Value does have a
>> > valid value.
>> >
>> > No changes were made in the two databases, other than the conversion
>> > from Access 2000 to 97, using Access' built-in tool.  It seems as if
>> > the problem lies with Access, not my code.
>> >
>> > Can anyone help?
>> >
> 


0
Douglas
10/11/2006 11:12:15 PM
Reply:

Similar Artilces:

GP 10 WorkFlow Error 05-27-10
Hi I am receiving an error in GP 10 WorkFlow. At the end the workflow fails with the error. "An error has occurred in the Approval Status". Would be very helpful if any lead could be provided. I googled around but no solution was specified to this error. Kindly Advise. Following is the Content of the Event Log Action: SubmitForApproval Current User LogOnName: FZE\Administrator Input parameters: <RequestObjects> <ArrayOfBusinessObjectSubmissionInformation xmlns:xsi="http:// www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www...

Error Message #31
One of my spreadsheets will not open. I get an error message that says: Program error. what causes this and can I retrieve my spreadsheet? But other workbooks will open ok? If yes, the first thing I'd do is try to find a voluteer that could try to open it on their pc. If that didn't work, sometimes you can recover your file by opening in saving in a later version of excel. Other times, openoffice can open files that excel can't. http://www.openoffice.org, a 60-65 meg download or a CD Firefli wrote: > > One of my spreadsheets will not open. I get an error message that...

error in importing data
Sorry the error is 0x8004032d ...

Runtime Error 2001 you canceled the previous operation
Dear all Eperts I have write VBA code like this Private Sub calenstart_Click() Me.Filter = "startdate='" & calenstart.Value & "'" Me.FilterOn = True End Sub when i click on my calendar it show message error Runtime Error 2001 you canceled the previous operation I can't fix it, what something wrong. Help me Thanks The message means that the filter string is not valid. Consequently, the next operation (applying the filter) fails. Presumably startdate is the name of a date/time field. Here are some reasons why it could fail: a) you used ' inste...

Converting XLS file to QIF or to OFX
How do I safely and securely convert an excel file (xls) to a QIF or OFX file? "dreamchaser" wrote: > How do I safely and securely convert an excel file (xls) to a QIF or OFX file? In Excel, save the file to CSV and ustilise iCreateOFX Basic from: http://icreateofx.co.uk/Convert-CSV-to-OFX to convert the saved CSV file to OFX. ...

Exchange 2003 SP1 Install Error
When I try to install E2003 SP1 I get the error: EventType exchangesetuperror, P1 6_5_7226_0, P2 update, P3 microsoft exchange messaging and collaboration services, P4 install, P5 e0070101, P6 _null_, P7 1, P8 NIL, P9 NIL, P10 NI The log says it cannot find a file but I can't tell which file it's looking for. Any ideas how to fix this? Thanks ...

error 2147217865
I am experiencing an Error message on my second station when trying to post a transaction, usually the first or second after I open Store Operation Pos. My main data is sitting on first station. This is the error msg in Full. Error 214721865 Cannot Drop the Table # Temp Because it does not exist in System Cat Log (Source: Microsoft OLE DB Provider for SQL Server) (SQL State:40502 Native Error 3701) Select GetDate ( ) As Current Date Time I am unable to find this error on customer source Can you post cause and RESOLUTION ---------------- This post is a suggestion for Microsoft, and Micros...

Script error in IE8
When I am trying to use the general search function in Win XP sp3 (all recent updates) from the start menu, I get the following error message: An error has occurred in the script on this page Line: 38 Char: 5 Error: permission denied Code: 0 URL: http://ie.search.msn.com/sa/5_12_0_512943/srchasst.js Do you want to continue running scripts on this page? Yes or No What could I do to correct this? "jiger" <jiger@discussions.microsoft.com> wrote in message news:54E2513A-6273-41CA-A429-46C1F995B995@microsoft.com... > [Subject: Script error in IE8] ...

Convert Column to row with variable data
Hi I'm using Excel 2k and I have a spreadsheet that looks like this Name Address Fred 21 Blah St London Sue Tower 50 London EC2 and need it to look like this Name Address 1 Address2 Address3 Fred 21 Blah St London Sue Tower 50 London EC2 I have a macro that can convert from column to row but only for a set number of columns. Is there any way to account for the variable amount of data for each address ? Any help much appreciated Thanks David David: I suggest the following formulae - copied down as necessary: C2: =IF(...

This query not giving correct results
I am trying to find the date when we had the most rainfall out of 3234 records, so with the first query to get the maximum rainfall in a month I get 110 records with one null and one '0' value. So this query is saying that out of 3234 records there has only been 108 days when we had rain. We probaly had more than that in one year never mind in 10 years. This cannot be right because we had 24 days of rain in November 2009 but the query only shows 16 for that month!. So how does it actually work? SQL for this below: SELECT Max(tblWXDataLeeds.ReadingDate) AS MaxOfReadin...

Office assistant error...
Hi, Having an issue on a standardised platform of XP with Office 2000 (SR2) and Outlook 2003. Users experiencing an error when starting Office Assistant - "The office assistant could not be started." The recommendation is to repair the office installation. Have attempted this as well as a reinstallation of Office 2000 and Outlook 2003, all to no avail. The error is sporadic but is occuring often enough to warrant this post! Anybody out there got some suggestions? Cheers, Darren Kukulka Systems Manager Beacon Technology ...

error messages
Every time I use my email, I get an error message that I need to close and send report to microsoft. I have tried the process of eliminationg add-ons and remove third party addons . I have also ran the complete scan to locate the problem but the problem still exists. "Cottontop" <Cottontop@discussions.microsoft.com> wrote in message news:FE8C3F3F-FD22-4D34-9A8E-48C23567FFAD@microsoft.com... > Every time I use my email, I get an error message that I need to close and > send report to microsoft. I have tried the process of eliminationg > add-ons >...

How to convert privatekey(string) to XML
Hi all, I need to convert privatekey toXML format. I read privatekey from file. Here is how: -------------------------------- Dim myStream As System.IO.StringWriter = New System.IO.StringWriter Dim testfile As String = "c:\RSAprivate.txt" Dim objStreamReader As System.IO.StreamReader objStreamReader = System.IO.File.OpenText(testfile) Dim readfile As String = objStreamReader.ReadToEnd() TextBox1.Text = readfile objStreamReader.Close() -------------------------------- and the privatekey looks like this: -----BEGIN RSA PRIV...

Contract
When I attempt to Invoice a Contract, I get the following error: <description>The service address of the contract is invalid.</description><details>The service address is invalid, this contract can not be set to invoice state.</details> Does anyone know what the issue/error could be? Hi Kathy, Not sure about service address, but check if the contact address and bill-to address for the contract has been set. To invoice a contract both these addresses are required. Hope this helps! Sam _______________ Inogic Innovative Logic Innovative solutions for your SME E...

Error in Detailed Sales Report
Dear All, Have you encountered a strange scenario wherein in your shop you have sold 1 item in that particular invoice/receipt # but when it got connected and process the WS401 and when you run the detailed report in HQ level , it was added up with some items which is not in the original shop transactions. Example.. Store A Trx # item Qty 1023 apple 2 in HQ Store Trx# Item Qty A 1023 apple 2 A 1023 orange 3 A 1023 banana 2 Which in the store A transaction, those items where not in that trasaction but somehow when it was connected and process...

Backup error Access Denied
I have been recieving the following error message for a while and can't figure out how to fix it. It is only happening on one mailbox. "Backup - \\MBMAIL\Microsoft Exchange Mailboxes Access denied to file Jhon Doe [jdoe]Top of Information StoreSync IssuesServer FailuresMail Delivery (failure jdoe@xyz.com)." Help Please Can you access the mailbox from a mail client? If so, you can use mdbvu to look at the Top of the Information store folder, and delete the message in there. If you cannot access the mailbox, then I have seen it where some users think they are ...

Installation of KB970430 Failed, Error 800B0100
Tried to install KB970430 automatically and manually and failed. Received error 800B0100 which took me to System Update Readiness Tool and in to an apparent circle. Using Windows Vista. jtm wrote: > Tried to install KB970430 automatically and manually and failed. Received > error 800B0100 which took me to System Update Readiness Tool and in to an > apparent circle. Using Windows Vista. Suggest you clean boot Vista and then try to run the System Update Readiness Tool (CheckSUR) again: How to troubleshoot a problem by performing a clean boot in Windows Vista or in...

"cannot empyt clipboard" error
I am getting the cannot empty clipboard error after every cut/paste or drag/ drop. I'm running Windows XP & Excel 2003. Is there anything to fix this? ...

An error occurred while trying to promote the e-mail. Try again
I have an email that I sent using Outlook (didn't use the CRM Email option). I now want to promote the email into CRM so that we have a permanent record of the email associated wioth the contact. I open my sent items, select the email and click the 'Promote E-Mail to CRM Activity' button in SFO. I get the error "An error occurred while trying to promote the e-mail. Try again" No matter how many times I 'try again' I get the same message. Can anyone tell me what I'm doing wrong and how I can get the email into CRM? Cheers Graeme SFO doesn't have any...

OFXIE12029 Error
My bank sync no longer works. Nor can I epay bills. I get the following error message: An internet communication error occurred. "Please try your call again later. (OXIE12029)." I have never had this problem before. I called my bank and they believe it is a MS IE / MS Money problem. All other sync's work. Any ideas? Greg In microsoft.public.money, Greg wrote: >My bank sync no longer works. Nor can I epay bills. I get >the following error message: An internet communication >error occurred. "Please try your call again later. >(OXIE12029)." I h...

Query Problem in Test.
I keep getting errors on the WHERE part of my query. I'm sure it's something simple. I don't know if you will need the whole code to see what the problem is. It is lengthy, so I will start with just where the problem is. Set rs = DBEngine(0)(0).OpenRecordset("SELECT T2.Distance, T1.* FROM (Church AS T1 INNER JOIN qryChurchZip1 ON T1.ChurchID = qryChurchZip1.ChurchID) INNER JOIN DistanceQuery AS T2 ON qryChurchZip1.Zip5 = T2.ZIPCode WHERE " & strWhere) ' See if found none If rs.RecordCount = 0 Then MsgBox "No Churches meet your crite...

Services error
I cannot start about 3 service on my exchange server5.5 and it gives this error when trying to start the information store - 'The Microsoft Exchange Information Store service returned service specific error 4294966767' have any body experienced this and what can i do? Please see the following article: 246272 XADM: Service Specific Error 4294966767 Appears, Information Store Stops http://support.microsoft.com/?id=246272 -- Hope that helps. ------------------------- Jaclynn Hiranaka Enterprise Messaging Support This posting is provided "AS IS" with no warranties, and c...

Outlook 2003 Usepecified error
Hi, I am having real problems trying to resolve an issue with outlook 2003 with Word as the e-mail editor. I am trying to forward an e-mail with about 5 pdf attachments that come to about 3mb in size, when I try to send the e-mail I get an "Unspecified error" If I turn off the word as the e-mail editor it works ok and sends the e-mail ok. I have also changed the text from html to plain text but i then get a 2nd error which I have managed to get rid of but readind the kb base article 870707. I have also tried running the detect and repair along with installing the latest s...

Compile error: User-defined type not defined
HiThis error popped up on this line of code:Dim db as DatabaseApart from the obvious self contradiction (defined .. not defined),what can possibly be wrong with this statement. I have used it beforein numerous other projects?Stapes On Mar 7, 1:29 pm, "Stapes" <steve.sta...@gmail.com> wrote:> Hi>> This error popped up on this line of code:>> Dim db as Database>> Apart from the obvious self contradiction (defined .. not defined),> what can possibly be wrong with this statement. I have used it before> in numerous other projects?>> StapesDepending o...

Query is making a nuts
Hi Using Access 2007 I have a table and two of the fields (Status and OrigStatus) have, among other possible entries, the words "Member," "Customer" or "Request." I am trying to create a query that only displays records which do NOT have "Member," "Customer" or "Request" in either field. But it won't work! I have tried putting each word in its own Criteria column in each field using syntax such as: <>"Member" with no success. I have also tried <>"Member" OR "Customer" OR "...