Accdb references

[gemma-the-husky]

I converted a complex mdb to an accdb and it produced these references.

As a result, all references to DAO objects started to fail, so I presume this order of references uses ADO before DAO.

I have changed all the declarations from say dim db as database to dim db as DAO.database, and it now builds and seems to run, but I may have missed a few other datatypes that might be affected.

Can anyone confirm how I need to amend the references - either remove some, or change the order etc, so it resolves DAO first by default.

Actually now I see the above screen shot, I moved the ActiveX Data Objects library down to the bottom. I can't remove it completely as there is some ADODb code to read lats and longs. Is that all I needed to do?

 

[Minty]

I had to do the same recently on a freshly imported database.
It appears that new versions of access require the explicit declaration of the database type, or I wonder if the default references have changed?

I haven't had time to experiment properly yet.

 

[CJ_London]

For one early version of access the default was ado but ms abandoned it and reverted to dao

if you have both dao and ado libraries you do have to specify which one you want. Same goes for fields as well as recordsets

in my opinion, better to late bind ado when required. Some form functionality (sort,filter) will only work with dao so you have to build your own ado functionality if required

 

[gemma-the-husky]

Well when I changed as Database to as DAO.Database there were something like 300 changes.

 

[Pat Hartman]

ALWAYS disambiguate your DAO and ADO objects as you create them EVEN IF you are not using both libraries. You never know what you or someone else is going to add later and you don't want to have to fix the problem then.

Also, the order in which the libraries are listed is the order in which they are searched when the interpreter is trying to figure out where an object is defined. So, if your ADO objects are disambiguated but your DAO objects are not, moving the ADO library to the end of the list should resolve the problem. But I would bite the bullet and disambiguate the DAO objects also.

 

[isladogs]

When a new ACCDB is created, there are 4 default references which are the First three in your screenshot followed by Microsoft Office 16.0 Access database engine library (which includes all the DAO items). For that reason, I would move that reference up to position 4 in the list.

If you really do need all the other references, I would update to the latest versions: ActiveX Data Objects 6.1 and XML 6.0

 

[gemma-the-husky]

As I say this was an mdb converted to an accdb, which then produced errors with processes using DAO objects, which I didn't expect. I might be able to remove the extensibility library, and maybe others, but they aren't there by accident. I imagine the library versions in the database were the most recent versions when they were first added to the original database.

 

[isladogs]

Its logical that those would have been the latest versions when first added but that was a very long time ago
This is a screenshot of some of the references from the first version of Access with VBA - Access 95 (Aug 1995) and even that includes a later ADO reference than 2.5!

Also, Access 2003 included XML v5.0 which was later superceded by v6.0 in A2010.

 

[Pat Hartman]

I guess you didn't read #5 where I told you what would fix the problem even though that was not the best solution.

 

[gemma-the-husky]

You mean me, Pat?

When I first noticed this, I wasn't 100% sure whether I could remove any of the references, and which references might be in the wrong order. I haven't actually removed any. As I composed the opening post I realised which DLL must be the one in the wrong place. I had already disambiguated many declarations. Database, recordset, property, field, I think but there are 100s of each in this database. I don't know whether tabledef or index declarations would need changing for instance, Hence asking the question.

I don't normally disambiguate references and had never had this issue previously. I can see your point but I don't expect I will remember to always do that going forward.

Re Colin's point about the references, I don't understand why the XML reference is version 3. Maybe the access conversion changed it. I will go back and see what the old references were out of interest.

On reflection, it came as a surprise that just converting the database would have changed the order of references, and produced errors. I wondered whether there was a database setting to choose the default reference type perhaps.

The answers here have been very helpful.I had already disambiguated enough references to allow the database to compile. I moved the ADO reference to the bottom which I think is probably more important as mentioned above, in case other declarations might have worked incorrectly.

 

[Pat Hartman]

I can see your point but I don't expect I will remember to always do that going forward.

Getting burned this way does a lot to enhance your memory

Again, I remind you that the libraries are used in the order they appear in the list. If having the ADO library at the end worked, then the ADO code was probably already disambiguated or it would NOT have worked. So ----- just reordering the libraries should bring you back to status quo.

You probably were not working with Access in 2000 or 2002 (I forget which version it was) where MS screwed us all royally by moving ADO to the head of the line and breaking virtually all my applications because they did not disambiguate since I only used DAO. BUT, they NEVER changed the RecordSource for forms/reports from DAO to ADO so there was huge inconsistency. It was a mess until MS went back to DAO for the next version.

 

[gemma-the-husky]

@Pat Hartman
Yes - the ADO references were disambiguated in the code. I was using ADO because the code sample I found to do whatever I needed used ADO.
It might have been A2007 that went back to ADO, as I didn't use A2007. I thought A2010 was an improvement, and the current versions are fine, but I still prefer command bars to the ribbon, I like the A2003 switchboard, and never really needed the later facilities.

 

[Pat Hartman]

I'm glad you understand how to fix the issue both ways.

If you like the A2003 switchboard, I created a version that uses VBA instead of embedded macros. Or maybe one of the derivations or even the custom version.

Replacement Switchboard

This Switchboard form is based on the last VBA version which I think was A2k. I expanded it to allow for 12 items rather than 8 and you can easily change it to do more. I've included directions. This updated version has two switchboards. One more traditional and the other is big buttons...

www.access-programmers.co.uk

Switchboard with simple security

This is a custom switchboard. It expands on the Switchboard Items table to add four additional columns to define view/edit/add/delete security levels for switchboard items and forms/reports referenced by them. It includes tables to manage users and also to assign them security levels. The user...

www.access-programmers.co.uk