Compile error when converting from A2003 to A2007

[Howard Jacobs]

Converted working Access 2003 apps to Access 2007.

Now get compile error on this code in a subform::

Dim rs As Recordset
Set rs = Me.RecordsetClone
strCriteria = "[chrTitle] = " & """ENTER TITLE HERE"""
rs.FindFirst strCriteria <----- COMPILE ERROR - Method or data error
not found
Forms!fsub1Author.Form.Bookmark = rs.Bookmark
Set rs = Nothing

"Me" is the subform record set with recordSource "tblBooks" and filter
"[chrLastName]='Parker' AND [chrFirstName]='Robert' "

The error seems to occur with all my findfirst, findnext, etc
statements in the subform

I do not know how to get started on this. Pls help.

 

['69 Camaro]

Hi, Howard.

I do not know how to get started on this.

Change the following line of code:

Dim rs As Recordset

To:

Dim rs As DAO.Recordset

The reason you're having the problem now and not before the conversion is
because the Access 2003 database developer took a shortcut and moved the DAO
library above the ADO library in the precedence list, instead of
disambiguating the code by identifying which library's Recordset object is
being used. This is not guaranteed to stay in this configuation when the
database file is opened on multiple computers or is opened in different
versions of Access, so it's not a recommended method of fixing the problem.
For more information on the co-existence of the DAO and ADO libraries,
please see Access MVP Tom Wickerath's article, "ADO and DAO Library
References in Access Databases," on the following Web page:

http://www.Access.QBuilt.com/html/ado_and_dao.html

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Howard Jacobs]

Everything working

Help much appreciated

Thank you

Howard

 

['69 Camaro]

Everything working

Help much appreciated

Thank you

You're welcome. Good luck with it.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Chris Mills]

Whilst Gunny's answer is impeccable, I have always used ONLY DAO and never
ADO.

This is probably because I learned from older versions, and found no reason to
convert to or use ADO (and it is not recommended if you only use JET
databases). ADO never took off really, even though it's a "default" in later
Access.

IF you ONLY use DAO, it IS NOT NECESSARY to disambiguate code. Merely remove
the ADO reference will fix it.

It is possible that a conversion from one Access to another might re-establish
an ADO reference. But, in doing such a conversion, one should go through the
references to see what is NECESSARY anyway. (and remove unnecessary ones).

Gunny's fix would fix the problem in all circumstances, no argument. But it
might also leave you with a never-used reference, which is also not a good
idea. More importantly, if the later version MDB is derived from "legacy
code", it IS NOT NECESSARY to disambiguate the code in the way Gunny (and
others) say. It can be resolved by being aware of the references.

I say, unless there is a specific reason, you SHOULD NOT HAVE both DAO and ADO
in the same MDB. Removing one or other reference is usually sufficient, rather
than the hassle of disambiguating code (only necessary if for some reason you
need BOTH references).

Of course, relying on Reference order is unreliable as Gunny says. REMOVING
ADO is perfectly reliable, assuming you do not use ADO, and that you check
references on any conversion. In the case of using only JET database, advice
seems to be that you wouldn't have any good reason to use ADO, which never
really established itself as the purported replacement for DAO.

(is this a sensible explanation or description, Gunny? It's more like an
alternate way of saying things which does not really conflict with yours?
Except, I've never needed to go through all the code and disambiguate!
Imagined downside only eventuates if one doesn't check references, and they DO
NOT alter on installations only in development?)(some of them may become
"missing" on installation, which is different from "altering")

Chris