Upgrade Access 97 to 2000

[George Wilson]

I am assisting a customer upgrading an Access 97 DB to
Access 2000 on a Windows 2000 system. The database appears
to convert with no errors but when trying to run a query
she gets a "Undefined function 'Mid' in expression." This
is not a user defined function as far as I know so it is
not a matter of moving it to a global module. Our
corporate version of Office 2000 is not a developers
version, could this be part of the issue? Can anyone think
of any other causes for this error?
TIA
George

 

[Jeff Conrad]

It is a References problem. The arch-nemesis of Access is missing references.

To fix the References problem follow these steps:
- Open any module in Design view.
- On the Tools menu, click References.
- At least one Reference should say "Missing". WRITE down the one(s) missing, most likely it will be
the DAO library.
- Click to clear the check box for the type library or object library marked as "Missing:."
- Close the References Window box.
- Open the References Window back up. Hopefully, nothing should say Missing this time.
- Now go find that library/project(s) in the list that was missing.
- If it is the DAO one scroll down to you get to Microsoft DAO 3.xx and check it.
- If you're using Access 97 that should be DAO 3.51 Object Library.
- If you're using Access 2000, 2002, or 2003 that should be DAO 3.6 Object Library.
- If more than one were missing, find the others and check them as well.
- Close the References box again.
- Now re-compile again. Debug--Compile.
- Hopefully you should not see any more compile errors.

If you'd like to read more about References, here's more info than you could possibly ever want to
know:

http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html

http://members.iinet.net.au/~allenbrowne/ser-38.html

http://www.trigeminal.com/usenet/usenet026.asp

ACC2002: References That You Must Set When You Work with Microsoft Access
http://support.microsoft.com/?id=283115

ACC97: References That You Must Set When You Work with Microsoft Access
http://support.microsoft.com/?id=175484

ACC: VBA Functions Break in Database with Missing References
http://support.microsoft.com/?id=160870

 

[Dirk Goldgar]

I am assisting a customer upgrading an Access 97 DB to
Access 2000 on a Windows 2000 system. The database appears
to convert with no errors but when trying to run a query
she gets a "Undefined function 'Mid' in expression." This
is not a user defined function as far as I know so it is
not a matter of moving it to a global module. Our
corporate version of Office 2000 is not a developers
version, could this be part of the issue? Can anyone think
of any other causes for this error?
TIA
George

This sort of error message, especially in a situation such as you
describe, is almost always the result of a broken reference. Even
though the broken reference probably isn't to the VBA library itself, it
prevents Access from resolving the reference to VBA. The steps outlined
here should help:


http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html

 

[Wayne Morgan]

It sounds as if the References didn't convert properly. In the code window,
go to Tools|References and compare them to the database prior to conversion.
Also, look for any marked as Missing in the bottom part of the window when
you select them. One difference you should see is that you should be
referencing newer versions of the Access and DAO libraries. If not, uncheck
the ones that are checked and scroll down to check the newer ones. You may
have to change the Dim statements on DAO objects in code as well. Access
uses the references in the order the checked ones are displayed in the
window. ADO and DAO have some similar components, such as each has a
Recordset object, and so Access will stop at the first one it finds, which
may not be the one that you want. While you can reorder the references in
the window, it is better to just remove the ambiguity in the code. To do
this, specify DAO in your Dim statements.

Example:
Change Dim rst As Recordset
to Dim rst As DAO.Recordset

Do this for all DAO objects. It can usually be done quickly using the
Replace dialog (Ctrl+H). Replace As Recordset with As DAO.Recordset and set
the search scope to the entire project.

For more help on references, check this link.
http://members.iinet.net.au/~allenbrowne/ser-38.html