Run-time Error 13 "Type Mismatch" Access 2000

[Jim Mc]

I believe I'm having DOA/ADO reference conflicts. The following code
is running on several machines. Some get error, some don't - all
running access 2000. Error 13 received at the Set Mydb statement...

Dim Mydb As DAO.Database, MyTable As DAO.Recordset
Set Mydb = DAO.DBEngine.OpenDatabase("X:\Box\Attend\ATNDDATA.MDB")
Set MyTable = Mydb.OpenRecordset("tblAbsence Data")
MyTable.Index = "EMP NUMBER" 'Index of
table
Points = 0!
CurDate = Now 'Current Date is today


Any help appreciated.

 

[Allen Browne]

Jim, there are several possible issues with this.

First, Access 2000 and 2002 (unlike all other versions) do not give you a
reference to DAO by default. You have to open a code window, choose
References on the Tools menu, and check the box beside:
Microsoft DAO 3.6
While you are there, you can uncheck the box for Microsoft ActiveX Data
Objects 2.x if you have no need for that reference. More info on references:
http://allenbrowne.com/ser-38.html

Once the references are in place, you want to set Mydb to a Database type.
If this is the database you are using, you don't need to open another copy
of it. Just use:
Set MyDb = CurrentDb()

If you do need to open another database directly, your code should work if
the path is open and the user has permission to open it.

(In general, I find it more flexible and efficient (memory wise) to use a
SQL statment to retrive the fewest records and fields possible for the
recordset, and have them sorted as desired, rather than open the whole
table.)

 

[Jim Mc]

Thanks for your response Allen. I should mention this code is opening
a backend database and not the "current". Something else I've have
noticed is the "Switchboard" form code is AOD, even though my
references point only to DAO. I inherited this database and believe it
was originally written for Access 95, and since been partially
converted. The one machine it works on is also running access 2000,
althought Access 97 also resides on the machine if that matters. Stll
receiving errors and out of things to try.

 

[Allen Browne]

Not sure what else to suggest.

If you do need to open another database (as distinct from using the attached
tables), you will need to use OpenDatabase rather than CurrentDb.

Not sure how the ADO(?) code is working without a reference to that library.
Perhaps it's late bound (just using Object.)

Partially converted? Didn't follow that bit.

Having Access 2000 and Access 97 on the same machine is not an issue, though
it can lead to references problems

 

[Jim Mc]

I'm sorry to say, I failed to reveal all of the pertinent facts. The
machine that has always been working, has windows 98 with access 2k
installed. The DAO it references is the DAO 2.5/3.5 compatibility
library. When the windows 2k / access 2k machines would reference DAO
3.6 the code would not compile, so I installed the older DAO 2.5 and
that seemed to fix the compile error but created run-time errors. What
I ended up doing was rewriting the syntax of the old code by replacing
the Dot (.) operator with the exclamation point (!) when referencing
objects. Now the code will compile with the newer DAO 3.6 and
everything is working fine. I'm still not sure why the windows 98
machine runs with the older DAO and has no problems, but appreciate the
support. Thanks!

 

[Allen Browne]

Okay, that makes more sense.

AFAIK, A2000 cannot use the compatibility layer, so if your code can work
without that (DAO 3.6 without the comp. layer) you're fine.