Query error after converting

[truleuneek]

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?

 

[Douglas J. Steele]

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)

 

[truleuneek]

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.

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

(no private e-mails, please)

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?

 

[Douglas J. Steele]

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

(no private e-mails, please)

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.

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

(no private e-mails, please)

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?