Unanswered: Designing/converting between Access versions
I need to build a new Access db for my office but have to do it on my own time (evenings/weekends) due to a huge workload during the business day. Problem is that we run Access 2000 at work and I have Access 98 and Access 2003 at home. I am not able to use a 2000 work license at home temporarily nor will my office buy me a copy of Access 2000 for my temporary use. In addition, the office plans to upgrade to XP and Office 2002 in a few months. Ugh!
So, I guess my question is whether I should develop the new DB at home in Office 98 and then have Access 2000 convert it at work for Team use and what sort of problems I might encounter doing so? And might there be any long-term issues with such a conversion? (Better to design in older version then convert up, right?) I believe there's a bigger difference, though, bettween version 98 and 2000 than 2000 and 2002 or 2003. (Thought I recall having some conversion errors with some VBA code or something once before with another database elsewhere...but maybe I'm wrong.)
At this point, I don't think that the DB will be extremely complicated with many VBA modules, macros, etc. I was told to design it in Access 2003 then save it as Access 2000. Would like some confirmation/input from others, too.
Any guidance from experienced users would be appreciated.
Converting from Access 2.0 to Access 97 was a huge leap - no other Access conversion will be as bad. So, be thankful you're not working with any 2.0
A notable difference in code structure between 97 and 2000 is that DAO is not part of 2000; 2000 uses ADO. This will impact your recordset objects. This can be fixed by explicitly referencing DAO and adding DAO. in front of each recordset declaration. This will provide "legacy" support in newer version for the old recordset objects. For example, change:
Dim rsData as Recordset
Dim rsData as DAO.Recordset
As far as I have experience with, the rest should be smooth from 97 to 2k.
Note that I am primarily a 97 users with limited 2k experience.
Oh, and one other thing: don't let the Access 2000 runtime application and Access 97 full installation coexist on the same PC our you'll have all sorts of problems.
I develop for a department that has Acc 97, 2000, 2002 and 2003. I have backends in 97 and front ends in everything. I develop in 97 and convert. I don't run into many problems and the conversion wizard helps eith most problems. My most common one is a conversion error for a module siting compilation errors. To avoid this, make sure you reference Microsoft DAO 3.6 Object library instead of 3.51 in modules. If you are unfamiliar with this, open or create a module, choose Tools --> References. You may have to create a blank one just to change the reference.
If you have any db's in 98 already, do some trial runs with copies. If you are going to have linked tables, go to MS Knowledge base and find articles on linking and improving performance for Acc 2000 and up.
BE CONSISTENT AND CAREFUL with regards to changes. It is easy to make the mistake of making changes to a converted db and not go back and do the same in the earlier version. It is best to always change the original and convert. If the program is already live, convert and change the name and import the new objects.