Conversion: ’97 to A2k (Access 2000)

Topic

New Reply

Are there any known “issues” when converting a ’97 database to 2000?

I have an inherited database that is very large and complex and I’m going to have to convert it at some stage.

Reply | Quote

Replies

Actually, it’s fairly painless. When you first start using A2000, ADO is the default data lib instead of DAO, so you’ll need to uncheck that reference and check DAO instead. If you want to use both DAO and ADO, then you’ll have some work to do as in the code, you’ll need to explicitly indicate which lib Access should use. ie.

Dim rst1 as DAO.Recordset
Dim rst2 as ADO.Recordset.

Outisde of that issue, the only thing you *might* consider doing is using /Decompile to make sure the BA project is clean. But I’d do that only if you triesd to convert and had problems. Just make sure that your A97 MDB can be compiled without errors before you try converting.

The MSKB on Microsoft’s web site does have a couple of nice articles on converting to A2000.

HTH,
Jim.

Reply | Quote

Once in a while, you may get a database that will begin to convert and fail but will leave the database in a partially converted state. You wouldn’t be able to open in the 97 and you can’t change any of the design in 2000. In that case, the answer is to create a new 2000 database, import all the objects and set the DAO reference. Since ADO is the default object model in Access 2000, you’ll either need to turn it off in the converted database or go through your code disambiguating all the references to DAO objects. Otherwise, Access will error out of things like recordset methods and properties because they’re different in the ADO object model.

One thing to watch out for if your database is a split front-end/back-end is that you need to upgrade the back-end as well or the performance will be very poor.

Reply | Quote

Thankfully there isn’t any VBA code to convert, it’s just a lot of tables, queries, forms, reports & macros. It does however have multiple “backends”.

Please can you highlight my denseness by explaining DAO / ADO

Reply | Quote

It would be very rude of me to “highlight your denseness”, so would it be OK if I just explain the difference?

DAO is the object model that has been in Access since version 1.1 (as I recall). It includes the Database object, a recordset object, fields, tabledefs, querydefs, connections, etc. It is Jet-centric, so you can’t use DAO to handle anything that isn’t coerced into the Jet object model. DAO objects have a predefined collection of methods and properties, so you have to learn to use those to write DAO code.

ADO (ActiveX Data Objects) is new to Office 2000/VB6. It is an object model that is *not* Jet specific and can be used to manipulate a variety of data sources, even text files, xml/html pages, and email messages. It doesn’t have a database object, it has a connection object. The object model is much smaller but everything is pretty much multi-purpose. It does have some objects, like recordset, field, property, parameter, etc., that have the same name as the DAO objects but that have different methods and properties. ADO object methods and properties, except for a bare minimum, are defined by the ADO provider, rather than by ADO itself. So a recordset object based on the Jet 4.0 provider has slightly different properties than one based on the SQL Server provider, and it my support different methods as well.

Needless to say, the code can be very different. Unfortunately, some of it can look enough the same to confuse you and Access. Plus, the syntax is very different. DAO is highly linear and you have to do certain things in a certain order to make it work. ADO is much more flexible about *when* you do something, as long as you get around to it before it’s needed.

If you just want to make your converted 97 databases run, set the DAO 3.6 reference and take out the ADO 2.x reference. It will run just fine and you won’t have to do any rewriting to keep Access from getting the object models confused.

Reply | Quote

Charlotte:
Thanks for the clearest “DAO/ADO” explanation I’ve yet seen. I’m also trying to deal with the issue. Have been trying to implement one of Helen’s ‘Archon’ code bits dealing with adding to combo box underlying tables ‘on the fly’ without success and suspect this issue may be affecting it since I’m working with 2k ‘out of the box’.
Since I’m also subject to “denseness”, I have a dumb question: When the references are changed, does the change apply to Access 2k in general, or just to the database open at the time?
Again … thanks for your help and willingness to share your insights and experience.

Reply | Quote

References are database specific. You have to set them for each database. However, if you convert an Access 97 database to 2000, it sets the DAO reference for you. If you *import* the objects from a 97 mdb into a new 2000 mdb, you have to go in and set the references.

By the way, if you’re having trouble figuring out the NotInList event for comboboxes, try doing a Lounge search on notinlist. That will turn up quite a few posts where the technique was discussed.

Reply | Quote

Charlotte:

Many thanks for the help. It and you are greatly appreciated.

Found the Microsoft Q197526 ‘NotInList’ from the search string and it answered my questions. My NotInList cbos now work well. Am using the ‘entry form’ suggested code.

Thanks again for your patience with those of us who don’t quite know where to find the appropriate help references.

Reply | Quote

WOW!!

Thankyou Charlotte.

I won’t pretend to understand completely what you’re saying, but I do actually get the idea. I’m glad that none of my A97 databases have any code to convert!!

Reply | Quote