upgrading from Access 97 to Access 2007

[Guest]

I designed a database (forms, reports, et al) several years ago in Access 97
and it continues to work well. I've just received a MS Office Professional
2007 and wonder if it is time to migrate my data into the new program.

I've attempted two or three times to do this in Access 2002 and I keep
getting error messages along the line of "...corrupted file..cannot be
opened by Access 2002 (or some such message)..." The help radio button
opens up a box which suggests I do some programming in Virtual Basic to
perform my transfer task.

The easy way out, of course, is to stick with Access 97, which does the job,
but I'm a bit stubborn about wanting to squeeze the most I'm capable of out
of the new 2007 package. Suggestions will be welcomed.

I'm not a programmer or developer so learning the appropriate moves is
probably beyond my skills. I think my alternative may be to export the
database to MS Exceland start over by importing that set of data and fields
to Access 2007 and spending what may be a great deal of time in rebuilding
my reports and forms. If I choose to do that, what Access 2007 books are
recommended for the amateur?

 

[Allen Browne]

Most straight-forward and well maintained databases can be upgraded from
previous versions to 2007 with minimal effort. The fact that you had
problems trying to convert to A2002 indicates there is a problem in the A97
database that would need to be addressed first.

The solution will be to use Access 97 to fix the problems, so you can then
upgrade. (This is important, even though the bad database is still usable
under A97.)

Here's some more information about issues you may face when converting from
Access 97:
http://allenbrowne.com/ser-48.html
and some more info about issues you may face when converting to Access 2007:
http://allenbrowne.com/Access2007.html

Here are the steps to revive the database with Access 97 before conversion.
Follow then in order:

1. Compact the database:
Tools | Database Utilities | Compact/Repair

2. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

3. Open Access (holding down the Shift key if you have any startup code),
and compact again.

4. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html
(Note: If the database started out in Access 1 or 2, it may use the
Compatibility Layer. You will need to re-write those lines of code that
require this library.)

5. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the name-autocorrect errors
are gone, the indexes are repaired, inconsistencies between the text- and
compiled-versions of the code are fixed, reference ambiguities are resolved,
the code syntax is compilable, and the VBA options are set to show errors
and avoid this kind of corruption.

If it is still a problem, the next step would be to get Access to rebuild
the database for you. Follow the steps for the first symptom in this
article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html