Old Access Database Conversion

[Cosimo]

I have an Access database I built over a period of years. I started the database in Access 2.0 and continue to build the database in Access 2003.

I retired the database almost twenty years ago, but recently I decided to convert it to a more recent version of Access so I can use it again.

I had a copy of Access 2007 which I installed and used to convert the database from mdb format to accdb format.

And I am getting errors that I never got before the conversion.

One problem I've isolated in a function occurs when the line "Set GetPerDb = DBEngine.Workspaces(0).Databases(0)" tries to execute, it generates "Error 13 - Type Mismatch" in the following part of the function:


Dim GetPerDb As Database
Dim PersonSet As Recordset

Set GetPerDb = DBEngine.Workspaces(0).Databases(0)

I tried substituting the following line, "Set GetPerDb = CurrentDb()" but that only gives the same Error 13.

Does anyone have any idea what might be causing this?

When I open the unconverted, mdb, version of the same database in Access 2007, the same line executes without an error.

[June7]

I cannot replicate issue. If you want to provide mdb file for analysis, follow instructions at bottom of my post.

[pbaldy]

Shot in the dark, but I'd try disambiguating the declarations:

Dim GetPerDb As DAO.Database
Dim PersonSet As DAO.Recordset

[Missinglinq]

I've been away from this for a while, but IIRC, converting an Access app from 32 bit to 64 bit can cause errors. It's been a long time since 2003!

Also, IIRC, there's a simple method of getting around this, but sadly my old brain can't retrieve it!

Sorry!

Linq

[Cosimo]

I cannot replicate issue. If you want to provide mdb file for analysis, follow instructions at bottom of my post.

Thank you, but for certain reasons that would be a last resort.


I was wondering--some library references in the database are different after Access converted the database from mdb to accdb. If I post the references from the mdb and from the accdb version, do you think comparing the two sets of references might provide a clue?

[Cosimo]

I've been away from this for a while, but IIRC, converting an Access app from 32 bit to 64 bit can cause errors. It's been a long time since 2003!

Also, IIRC, there's a simple method of getting around this, but sadly my old brain can't retrieve it!

Sorry!

Linq

The mdb version runs fine on a 64 bit PC running Windows 7 and Access 32 bit 2003. The mdb version also seems to run fine on a Windows 10 64 bit PC running Access 32 bit 2007.

But the mdb converted to accdb generates the error on the Windows 10 64 bit PC running Access 32 bit 2007.

[Cosimo]

Shot in the dark, but I'd try disambiguating the declarations:

Dim GetPerDb As DAO.Database
Dim PersonSet As DAO.Recordset

That worked!!!

Thanks so much.

I have tons of code I wrote in this database--would it hurt for me to run find and replace so I can make the same change in all my functions and subs?

[pbaldy]

I would, but make a copy first.

[Cosimo]

I would, but make a copy first.

Sure thing.

Thanks again!

[June7]

Interesting. My code has no issue without the DAO prefix.

[pbaldy]

Sure thing.

Thanks again!

Happy to help!

[pbaldy]

Interesting. My code has no issue without the DAO prefix.

If memory serves that may depend on the order of items in your references, but I could be wrong.

[Cosimo]

Interesting. My code has no issue without the DAO prefix.

I didn't have a problem without using DAO either when I ran an mdb version of the same database either on Access 2003 or Access 2007.

It was only when I converted the database to accdb format and then ran it on Access 2003 that this problem occurred.

I also added DAO to another function I made years ago that was giving me the same Error 13 when using the accdb format in Access 2007, and now that function is running OK.

Possibly, this issue goes away with Access 2010.

Or, as Pbaldy mentioned, it could be you and I have somewhat different library references set (my mdb version and the converted accdb version of the same database, do have somewhat different references--something I will also need to take a close look at).

[Cosimo]

Interesting. My code has no issue without the DAO prefix.

I was skimming through my old notes from twenty+ years ago, and ran across a possible explanation: if in the library references the DAO thing is higher than the ADO thing, Access will treat DAO as the default code.

That might be why you don't have the problem.

[Cosimo]

If memory serves that may depend on the order of items in your references, but I could be wrong.

I just discovered info in my old notes that if DAO is higher that, for example, ADO, then Access treats DAO as the default reference.

I haven't checked, but I bet the order in my library changed when I converted my database from 2003 to 2007.

But in any case, I will always use the DAO prefix from now on.