Converting 2003 to 2010 (1 Viewer)

[wilpeter]

Starting with XP on a single PC, I expanded to Multi-user PCs with various operating systems (but not Apple) without problem with my Access 2003 multi-database file program. Now I'm asked if I can change to Win7 x64 PCs that already have Office 2010 loaded. My first thought was to transfer copies of the linked database .mdb files to one of these PCs and try opening it with the 2010 version of Access. While the opening screen (a menu form using macros) looks OK, and the Link Table Manager worked fine to reconnect the databases, some of the macros don't respond.
My questions are 1. Should I do it in stages--convert first to Access 2007 then to 2010? and 2. Will there be different problems with mixing X64 and X32 machines in the local network?
Thanks for any insights.

 

[Pat Hartman]

Leave the FE and BE as A2003 .mdb's to start.
Have everyone convert to Office 2010.
Once everyone is successfully using the A2003 app in A2010, you can convert the FE to an .accdb while still leaving the BE as an .mdb.
Once that is working, convert the BE to an .accdb and have everyone relink.

Only the last step needs to be coordinated and done all at once. As long as you do things in the order I specified, you can do a gradual conversion with only the final piece having a time constraint. Once the BE is converted, everyone has to relink to the new BE in order to use the app and they have to do it immediately.

Access 2007/2010 have a new (and pretty hokey) security model and you may be running afoul of that. Make sure you have defined a trusted directory on each computer and you store your access app there. The other option is to digitally sign the FE which costs money since you need to purchase a certificate and renew it yearly.

 

[wilpeter]

By FE & BE, I guessing at Front-End, Back-End databases; in which case it doesn't apply. It's more of a transactional database (used by 6 people) an archival database (one person) and a front-end (volunteer) that links to both for accounting purposes. Also, I think getting the firm to upgrade across the board to 2010 is unlikely given the training each would need to undergo. Those accustomed to 2003 will continue doing Word and Excel where they are comfortable. I suppose they might be convinced to uninstall the Access portion of 2003, once I can get the A2010 working. Is the x64 x32 factor not a problem? Would Office 2010 co-exist with Office 2003 if A2003 is deleted?

 

[boblarson]

By FE & BE, I guessing at Front-End, Back-End databases; in which case it doesn't apply. It's more of a transactional database (used by 6 people) an archival database (one person) and a front-end (volunteer) that links to both for accounting purposes.

Even if only one person uses the database, it should be split and each person given a copy of the frontend. Your odds of corruption are greatly increased if it isn't split, hence potential loss of data. Plus if it is split and someone's frontend corrupts the others can keep working while you replace the corrupt frontend for the ONE person.

Also, I think getting the firm to upgrade across the board to 2010 is unlikely given the training each would need to undergo. Those accustomed to 2003 will continue doing Word and Excel where they are comfortable. I suppose they might be convinced to uninstall the Access portion of 2003, once I can get the A2010 working. Is the x64 x32 factor not a problem? Would Office 2010 co-exist with Office 2003 if A2003 is deleted?

Mixing versions is a potential disaster waiting to happen. I'm not saying it can't be done but the odds of problems greatly increase when you do it. Also, you should TEST A COPY of your database(s) in 2010 BEFORE you go through with any upgrades. You may find, as we did with our 2007 upgrade from 2003 that some changes need to be made in order for it to work. It is highly dependent upon what features you have been using. Some of our databases required no changes at all. Others required some fairly substantial changes. So it is good to find this out BEFORE you try having this in a production environment.

 

[wilpeter]

Thanks Bob, I unfairly tried to simplify in my response to Pat. There IS a BE with all but one of the Tables; with a FE that is shared and relies on that BE. However, the BE is not solely a BE, since it has its own forms, queries, reports and macros. Step one in 2003 seems evident: create another FE that handles the maintenance of that BE. (Maintenance involves archiving inactive members and reinstating them; doing the same for Programs, Volunteer workers, Equipment, etc.)
I said 'all but one' above because a second database is a combined FE/BE which uses the first BE for members but keeps its own table of Receipts as the BE for the cashier who has a FE that deals solely with giving receipts to members. CRA (Canada Revenue Agency) requires donation receipts be tightly monitored, so Receipts table cannot be in same BE as operations tables.
BTW, does a separate drive count as a 'file server'?

 

[Pat Hartman]

For stability and ease of upgrading, you should start by splitting out data from other objects. ALL the databases should be split with a FE and BE and the ONLY shared file is the BE. EVERY user should have his own personal copy of the FE loaded on his c drive. Even single user apps should be split.

It isn't necessary to convert the database from A2003 to use it with A2010 but you do have to test it because you need to make sure you aren't using any deprecated features.

If your users don't develop their own applications but just use apps developed for them, there is no training involved. Your app will still control everything via menus and the only things the user needs from the ribbon will be printing options.

DO NOT INSTALL the 64-bit version of Office. The 64-bit version of Access is incompatable with the 32-bit and you can't install some 64-bit components and some 32-bit due to shared libraries. Once all users are converted to O2010 and running on 64-bit hardware, you can then replace the 32-bit Office with 64-bit if you need to (they come on the same CD so you can uninstall one and install the other). The only advantage (and I'm not sure I would actually call it an advantage) is that the 64-bit version of Excel supports humongous workbooks.

 

[wilpeter]

Thanks Pat, I've moved most of the tables (the ones with permanent data) to two new BE databases, leaving four FE databases (each with a unique set of links and other objects). Only one Table is linked to all four FEs.
Initially, I'll be testing all four FEs without converting to A2010 as you said.
So far, the easy stuff works but many reports are coming up blank. (Something called "with owner access option" has added itself to my SQL.)
The user firm has two employees that have learned (over the past 9 years) to manage their system, adding queries, forms, reports, and even fields to the tables. They only call me when something new needs integrating or the software needs updating. My services are free, so they don't abuse that.
Two of the PCs are x64, but the O2010 is a 32bit installation; so no worries.
I'm going to be haunting the web for lists of deprecated features and may even buy a copy of Access 2010 for Dummies!
Thanks again.

 

[wilpeter]

Already a new question! I chose the smallest FE which has just two linked tables. The PC is password protected. In A2003, the StartUp Menu stops display of Database Window and use of Access Special Keys (like F11), and the Tools menu has been hidden. A menu limits functionality. To open the Database window for me to perform checks, the FE must be opened with Shift-dbl click. So, after linking to the tables and verifying it works, closing the Database Window, and converting to A2010, can you still get back into the DbWindow to make adjustments later? If I do these adjustments off-site, I obviously have to change the link to do so; and change it back when I replace it on site--so the design view has to be available yet hidden from the volunteers.
We've never needed security-enabled elements, no workgroup log-in, no user-names, and no permissions. I base it on inclusion instead of exclusion.
Any suggestions?

 

[Pat Hartman]

Don't start adding Access security at this point. It is one of the deprecated features and is not available once you convert the .mdb to an .accdb.

The shift-click works the same in A2010 as it did in earlier versions. When you do convert to .accdb you will have to first remove any ULS (user-level-security).

 

[wilpeter]

Remarkable! Now all six databases are nestled as accdb's and the linked tables are all cooperating. Just one of the FE's displayed a yellow message bar (Security Warning: some active content has been disabled. Click for more details.) but when I clicked the "Enable Content" button it disappeared without an explanation. Who knows what it changed! It would take weeks to try all the queries and reports, but it looks good. Again, thanks for giving me the confidence to give it a try.

 

[boblarson]

Just one of the FE's displayed a yellow message bar (Security Warning: some active content has been disabled. Click for more details.)

That meant that the database was not trusted nor in a trusted location. I hope you know what Trusted Location means in 2010. If not, here's a link:
http://www.btabdevelopment.com/ts/2010TL

but when I clicked the "Enable Content" button it disappeared without an explanation. Who knows what it changed!

By enabling, you had set this as a trusted document (that is new in 2010, Access 2007 only had trusted locations).

 

[wilpeter]

Thanks, Bob. Yes, I had added the file location to the Trusted Sites earlier. Didn't know about having to trust files too.

 

[boblarson]

Didn't know about having to trust files too.

It isn't that you need to trust files. What the Trusted Documents thing is all about is that they assume that if you have it in a Trusted Location, then it is Trusted. If you don't have it in a Trusted Location and you click ENABLE they assume that it is trusted so, that will become a Trusted Document until you either go remove it from the Trusted Documents area in the Access Options > Trust Center, or you move the file to another computer.

 

[wilpeter]

Since the only obvious change was the macros, can you tell me please what the language is called and if there's a book on it.

 

[boblarson]

Since the only obvious change was the macros, can you tell me please what the language is called and if there's a book on it.

They're still "macros" and they are covered in several books. One of them, Access 2010 Inside/Out, was written by Jeff Conrad and John Viescas (both members of this forum and John is still an MVP and Jeff, who WAS an MVP, works for Microsoft as a Software Test Engineer with the Access Team).