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
724 Views

Similar Articles

[PageSpeed] 19

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:

Exmerge error
I downloaded exmerge this morning. When I try to run it to play around with it, I get this message "This application has failed to start because EXCHMEM.dll was not found. Re-installing the application may fix this problem." I searched and found the .dll file on the machine. I do not want to re-install Exchange 2003. Can anyone shed some light on this? Thanks in advance. Jim Copy all the Exmerge files to the exchsrvr\bin directory. -- Mark Fugatt Exchange MVP http://www.exchangetrainer.com http://www.msexchange.org "J. Huber" <jimh@great!harvest.com> wrote in me...

Formula Error #2
mod(12,2.4) will not return a zero value This is simple roundoff error. If it is not acceptable then use: =12-2.4*(INT(12/2.4)) -- Gary''s Student - gsnu2007k "alaomair@gmail.com" wrote: > mod(12,2.4) will not return a zero value > Try =ROUND(mod(12,2.4),10) or nay number of decimals less than 15 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email <alaomair@gmail.com> wrote in message news:a416eaff-b6fe-475e-a4fe-de95e97bc365@m74g2000hsh.googlegroups.com... > mod(12,2.4) will not return a zero value ...

Error when saving workbook
Hi, We have a user where I work who has Excel 2003 on her computer. She is complaining that on certain excel documents which she has used in the past that when she goes to save them after making changes, she gets the error message: "This file was created using a later version of Excel. There is a remote possibility that certain workbook properties specific to the later version may be lost if you save the file in this version. If you want to preserve the original file, click Cancel, then rename the new version using the save as command." If she clicks the OK button and saves the f...

Switching to MFC in shared DLL brings runtime error
Hello, My Visual Studio 2005 solution is composed by many projects: an exe, some dynamic dlls and some static libs. All of them use MFC in a static library and I need to switch to MFC in a shared DLL. The compilation is fine but when I debug the application I got the error: "Debugger: An unhandled non-continuable exception was thrown during process load" No more hints, and it is not possible to execute any piece of code. I then analyzed my exe files with Dependency Walker to see if there are errors while loading dlls. I find out that in two of my dynamic dlls some function (but no...

Any way to convert column of numbers from positive to negative?
Hi all, Is there any way to convert a column of numbers that are positive to be negative? We create a text file from our general ledger program that has all the transactions for the past year, then import that file into Excel. We use the text to columns to convert the text file to a usable format where the debits are in one column and the credits in another. The problem is that all the numbers are shown as positive so when we net various figures the amount is not correct. Here's an example: DR-amount CR-amount 8,239.32 23,545.26 18,028.12 200,000.00 ...

Outlook error #60
Keep getting this error mesage "Outlook could not create the temp file. Check the temp environment variable" any idea how i can fix it regards Garth Outlook error <Outlook error@discussions.microsoft.com> wrote: > Keep getting this error mesage > > "Outlook could not create the temp file. Check the temp environment > variable" any idea how i can fix it What are the values for the %temp% and %tmp% environment variables? -- Brian Tillman [MVP-Outlook] ...

How to generate conditional pop-up error message?
I have a moderately complex sheet with a lot of cells where the user (usually me) can enter data. I would like to add checks to certain cells and generate a pop-up warning message if there is an error. The pop-up would be like a Comment or an Input Message, except that it would appear if a test failed, rather than when hovering over the cell or selecting it. Have a look at Data | Data Validation in Help Then come back with more questions Example, you have arrange that a cell can have only values "cat" or "dog", and that a message (the text of which you get to decide) po...

error adding new schema field 05-24-04
Hi, When I attempt to add a new schema field to an account in the Deployment Manager, I get the following error: "An error occured during the addition of the new field. The addition failed." In the event log, there were 2 entries: "dmlog:sp_repladdcolumn failed-timeout expired" "dmlog:failed to add new integer attribute (CFInumberofpcs) to Account Entity. Anyone got any ideas how to resolve this? Try the following recommendation from the Microsoft website SYMPTOM When you create a new schema field in Microsoft® Business Solutions CRM (Microsoft CRM) Deplo...

NEED HELP:Soap error: operation requested in the Soap message with soapAction isn't defined in the WSDL file.
Hi all, I need some help. We have an application which is using SOAP to relay messages between two applications. We are having a problem, which started to only occur intermittently, but now happens all of the time. When communicating between the apps, we receive this message... Soap error: The operation requested in the Soap message with soapAction <http://tempuri.org/FormAPI/action/COutlookForm.isCateringAvailable> isn't defined in the WSDL file. This may be because it is in the wrong namespace or has incorrect case. The SOAP sender application is a VB6 COM dll hosted in a...

MS query and corrupt data
I Use Msquery to get data from an Oracle view and get it directly into a excelsheet. In this view the results contains charakters like a degree character. (for degree celcius) When i look at the ms query results then the data is correct but when it is tranported to an excelsheet the the degreesign is corrupt. On an machine i get an �0�.. o another machine i get an black box with an questionmark in it. Does anyone knows what to do on that. I use excel XP Please help me if you know the reason --------------= Posted using GrabIt =---------------- ------= Binary Usenet downloading made...

Error Serializing a Wrapped SortedList
This is a multi-part message in MIME format. ------=_NextPart_000_000C_01C92EE4.BBA03630 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I'm using VB.NET 2002 on Windows 2000 Pro and am having trouble = serializing a SortedList. This is my class: <Serializable()> Public Class clsGoodFiles Inherits SortedList Public Sub New() MyBase.New() End Sub End Class This is my calling code: Dim xw As New Xml.XmlTextWriter(Path.Combine(Application.StartupPath, = "GoodFiles.xml"), System.T...

Business Closure error after upgrade
Anyone with a solution to this onne ? Error Details: Exception of type 'System.Web.HttpUnhandledException' was thrown. Full Stack: [ArgumentException: Cannot load resource that matches "query.F751F47D-6A6E-42d1-9F11-33AA7B42C13A.cell.duration.label" identifier. Parameter name: name] at Microsoft.Crm.Application.ResourceManager.BasicResourceManager.GetCultureString(String name, CultureInfo culture) at Microsoft.Crm.Application.ResourceManager.BasicResourceManager.GetString(String name) at Microsoft.Crm.Application.Controls.GridUtility.GetColumnInfo(XmlNode node,...

Editing MS Query
Hello, I am having a problem editing queries created in MS Query. I create the queries without any problems, and am able to add criteria and bring the query into Excel. But when I want to edit the query, I get the SQL box that pops up, and it is not easy to edit. Any suggestions? Thank you in advance. ...

Passing an argument from a query to a command
I am trying to send a report using an email address in a DB. I wrote the following code for it. Private Sub Reminder_Letter_Command_Click() On Error GoTo Err_Reminder_Letter_Command_Click Dim stDocName As String Dim SendTO As String stDocName = "BG Reminder Letter" RunQuery MsgBox "This is After RunQuery " & SendTO SendTO = [Queries]![BG email only from email from ae code].EmailAddress MsgBox "This is SendTO 2 Value ==== " & SendTO DoCmd.OpenReport stDocName, acViewNormal DoCmd.SendObject acSend...

How to convert AVCHD M2TS/MTS to iPad on Mac
Question: "I have a Sony HDR-SR5, or some other AVCHD camcorders such as Canon HF11, JVC GZ-HD10, Panasonic HDC-SD5, etc, but I failed to import the video files copied from my camcorder in the format of .mts or .m2ts for playback on my new iPad, is there anyone can give me some suggestions, I'm using Mac Snow Leopard." Answer: Well, firstly I should tell you that most portable devices cannot read .mts or .m2ts files directly, if you want to play them on your mobile devices like iPad, iPhone, etc. without trouble, you have to convert them into their acceptable format like MP4 vid...

How do this tricky query?
I have 2 tables, a CASE table and an INVESTIGATOR table. This is a one-to many relationship. Each Investigator has an Assign Date and a Completion Date. There may be zero, one or many investigators per case but only one at a time. Completion dates could be missing: Investigator1 Assigned 12/10/09 Completed 12/10/09 Investigator2 Assigned 12/10/09 Completed null Assigned dates could be the same e.g. Investigator1 Assigned 12/10/09 Completed 12/10/09 Investigator2 Assigned 12/10/09 Completed 12/11/09 Or, different e.g. Investigator1 Assigned 12/10/09 Completed 12/11/09 ...

Error 1920 during CRM install
I would like to set up a CRM to try it. I have 2 W2K3 hungarian servers. One Domain controller with Exchange 2000 and one SQL 2000 server. I have installed these as the installation manual says. I have set the local to English (US). I start CRM install and I get the message: "Error 1920 Service Crystal Cache Server (CacheServer) failed to start. Verify that you have sufficient privileges to start system services". If I choose Ignore, I get this message every Crystal services. I am logged in as domain administartor. What have I done incorrectly? Hi - did you every find out how...

No Current Record error
I have a form with a subform on it. When I open the form with the acFormAdd option (to add a new record) I get the error "No Current Record" on the following: Me.RecordsetClone.MoveLast which is in the load event of a subform on the form. The statements Me.RecordsetClone.MoveLast Me.RecordsetClone.MoveFirst are in my subform so the "Item n of n Items" label box will show the correct number of items. How can I avoid this error message? Robert When you open a form in add mode, it does not load any records. Consequently, the instruction to move to the ...

Nested query with combined key
Hi I am working on an existing database that has three tables, no option to change the database structure, but have to work on it. CREATE TABLE Building ( b_id INT PRIMARY KEY, b_name char(50)); CREATE TABLE Door( d_id INT PRIMARY KEY, d_name char(30), d_key char(10), d_BuildingID int references Building(b_id)); CREATE TABLE History ( h_key char(40) PRIMARY KEY, transaction_time DATETIME PRIMARY KEY); The h_hey in the History is actually d_id + "-" + d_key, now I need to join three tables, get the building, door and associated last transacti...

Converting time
How can I convert 1m38.15s or 0m58.98s into seconds in decimals ie:98.15 or 58.98. I've tried formatting the column next to the data "ss.00" but it does not work. Any help would be appreciated. =LEFT(A21,FIND("m",A21)-1)*60+MID(A21,FIND("m",A21)+1,FIND("s",A21)-FIND("m" ,A21)-1) -- HTH Bob Phillips "Dave" <Dave@discussions.microsoft.com> wrote in message news:33D1BCBB-991B-47C6-B12F-C31D01166C3C@microsoft.com... > How can I convert 1m38.15s or 0m58.98s into seconds in decimals ie:98.15 or > 58.98. I've trie...

12.2.3 Updater errors every time.
Auto Updater reminds me every time I run an Office app that I need to update to the CRITICAL 12.2.3 update of Office 2008 (12.2.1) on my Intel Power Book running up-to-date OS 10.6.2. EVERY time it tries to download it again, EVERY time it starts the install, then EVERY time it errors out saying : "The Installation Failed. The Installer encountered an error that caused the installation to fail. Contact the software manufacturer for assistance." It goes through the progress bar saying Writing files, then switches to Running Package Script - at which point the progress b...

Another tricky selection query
Hi, could not resist re-using the title of a previous question as I was just trying to scratch my head on how to name my question. I need a query in which the data from the previous record is referenced. to make things simpler, here is the situation. I have a simple table that contains 2 columns, first column contains name of an event and the second column contains the date. There ARE NO DUPLICATE dates. The date is the end date for the event. The start date for the event is from the day that follows the end date of the previous event. What I need: A query that generates a listing...

Excel exception error
I keep receiving the following error when I try to print an Excel report: Excell caused an exception 10H in module EXCEL.EXE @ 015F:30136240 Can you help me to fix this problem? Thanks so much That sounds like a printer driver conflict, try downloading the latest from the manufacturers website, is it a Kyocera? Rick "Becky A" <balessandro@ohioheadandneck.com> wrote in message news:0e5d01c38130$bc12ae20$a001280a@phx.gbl... > I keep receiving the following error when I try to print > an Excel report: > > Excell caused an exception 10H in module EXCEL.EXE @ >...

PST Backup: Error Copying file
Each time I try to backup Outlook 2000 files I get this message: "Cannot copy mailbox: The process cannot access the file because another process has locked a portion of the file." Anybody have a clue how I can fix this problem Not until you tell us how you are trying to back up the file. You must make sure Outlook is closed completely before you try. -- Russ Valentine [MVP-Outlook] "Marty" <marty@kingnewton.com> wrote in message news:_ss4g.17$Mn6.12@trndny06... > Each time I try to backup Outlook 2000 files I get this message: > > "Cannot copy mail...

Licensing errors on Small Business Server 2003
My SBS 2003 server just started reporting almost continuous pop-up errors with the message: "Warning: License usage for a product licensed in per server mode has exceeded the maximum number of licenses purchased and will prohibit further licenses from being granted. Consult the Application event log or Licensing from the Administrative Tools folder for more information." While these errors were popping up, all users on the network were unable to access any resources on the server. This seems like a straightforward message except that the server has 25 licenses instal...