Mismatch Error on OpenRecordset

mikedu · February 17th, 2004, 06:49 PM

I am receiving a mismatch error on my Set line for a recordset that is opening a table in the current database. I have reviewed rhe Dim and Set statements over and over and they appear correct. No matter what I do, the error occurs. I have not had this problem in earlier versions of Access.

Any thoughts?

Code:

Dim pay As Recordset
Set pay = db.OpenRecordset("Earnings")

Yes, there is a table in the current database named Earnings.

rgerald · February 17th, 2004, 07:13 PM

Try

Dim pay As New Recordset

Rand

rgerald · February 17th, 2004, 07:23 PM

My DAO is rusty but ...

Code:

Dim db As Database
Dim pay As Recordset

Set db = CurrentDb()
Set pay = db.OpenRecordset("Earnings")

etc...

Rand

Dataman · February 17th, 2004, 07:29 PM

If the recordset is DAO then

Dim rstPay as DAO.Recordset
Set rstPay = CurrentDB.OpenRecordset("Earnings")

HTH,
Mike

rgerald · February 19th, 2004, 11:36 AM

Dear MikeDU,

The reason you are getting a mismatch is that you are using DAO. ADO is the newer (and preferred) form of data access.

You may use either one, BUT you need to know the following caveat.

Recordset can be either DAO or ADO (ADODB). When Access 2000 (or later) sees "Recordset" it sets the default type as either ADODB or DAO depending upon which one is first in the reference list. This may vary from computer to computer - ie. the code you wrote may work on one machine and not on the next one.

The only way to guarantee that the correct type is used every time is to explicitly specify it as ADODB or DAO, for example:

Code:

Dim pay As ADODB.Recordset

or

Code:

Dim pay As DAO.Recordset

Other than hard coding the type (ADODB or DAO) or re-ordering the reference list on each computer, I don't know of any other way to force the default.

In your case, use the DAO type.

Once you specify the type explicitly, you must then make certain that the correct reference is installed on each computer that uses the code. If the reference is MISSING you will get a different error.

After getting bitten by this feature many times, I now try to specify the data access type (DAO or ADODB) as above.

Rand