Several things in addition to getting the correct MDAC.
MDAC is ADO and Jet and the Jet 4.x engine. The Jet 4.x
engine no longer ships with versions of the MDAC higher
than 2.5.
If you're using DAO, and not ADO, be sure that your
reference in MS Access under tools, references is
Microsoft DAO 3.6.
If you are really using ADO in your VB code, ensure that
you fully qualify your objects, just in case both ADO and
DAO are specified as a reference:
Dim myRecordset as ADODB.Recordset
as opposed to:
Dim MyOtherRecordset as DAO.Recordset
You also need to test for BOF and EOF as both being false
prior to attempting any recordset manipulation, including
getting recordsetname.recordcount:
Sub DAOexample()
'Assumes you've added DAO 3.x object library to your
list of references.
'This example used DAO 3.6 with MS Access 2000 for use
in an .mdb file
'To avoid confusion between ADO and DAO, which can co-
exist within the
'same project, we always fully qualify the object
references.
'We are also using the default database connection
rather than creating
'an independent DAO.Connection object, and the
OpenRecordset command
'is using the default recordset type (because I did
not specify).
Dim MyRecordset As DAO.Recordset
Set MyRecordset = CurrentDb.OpenRecordset
("QryExampleMSAccessQueryDef")
'See if any rows were returned:
If MyRecordset.BOF = False And MyRecordset.EOF =
False Then
'Yes.
MyRecordset.MoveFirst
Do While MyRecordset.EOF = False
Debug.Print MyRecordset!EntityName
MyRecordset.MoveNext
Loop
End If
MyRecordset.Close
Set MyRecordset = Nothing
End Sub
Also, ADO, if that's actually what you're using and not
DAO, behaves much differently in code above MDAC 2.1. If
you are coding to 2.5 or higher, you can open the
recordset and immediately test for the rst.recordcount and
receive 0, rather than a Jet Error, if the returned
recordset is empty.
In 2.1, if the returned recordset for recordset named rst
is empty and you do not test for rst.BOF=False and
rst.EOF=false prior to getting rst.recordcount, an error
occurs.
And finally, believe it or not, you may want to decompile
your VBA that resides in your MS Access .mdb or .adb
file.
Do do this, go to MS Access under Programs/Microsoft/etc.
Create a shortcut.
Open the properties of the short cut, and enter the full
pathname of the location of the application, in quotes,
followed with a space, followed by the full pathname of
the file to be decompiled, in quotes, followed
by /decompile (not in quotes). Save the change.
Hold down the shift key then open your short cut.
Hold down the shift key then compact the database.
Go to any of your modules.
Go to debug, compile in the VB editor to recompile.
Decompiling is very good to do whenever you switch between
versions of Office (2000/2002) and whenever you have tons
of VB, and you've never decompiled. It gets rid of old
psuedocode and leaves you with only compiled code from the
most recently specified library references.
