Migrating to Access 2002

Migration to a new database system must be considered carefully. A typical database is more complex and important than a spreadsheet or word processor file. The database:

• has a much longer life - a customer list will persist for the lifetime of the business.
• is very much larger - 10 Mb is not at all unusual.
• exists as a single master copy on a single machine unlike a spreadsheet which may exist on your desktop and your laptop and which might have been emailed to colleagues.
• cannot be "typed in again" if it becomes corrupt because there is just too much information and it is unlikelyto exist anywhere else.
• is more likely to be mission-critical.

Hardware

Office XP has more features than Office 2000 and needs a more powerful PC if it is to run smoothly. Access 2002 may need even more resources in the following circumstances:

• If the database is large, with tens of thousands of records.
• If you are performing complex queries on many tables.
• If several users in the workgroup are sharing a database on another user's PC rather than using a database on a separate server.
• If you use SQL Server Desktop Engine.

File format

Access has a poor record of changing database format with every release. With Access 2002 you have five options:

• Use Access 2000 format (or even Access 97) to retain compatibility with your existing software. This is Microsoft's recommended rollout technique.
• Use the new Access 2002 format to be able to use the new features of the language immediately. This of course implies that all your users must upgrade to Office XP at the same time.
• Use SSDE (SQL Server Desktop Engine) for systems that are planned to grow and will soon become too large for Access itself.
• Connect to SQL Server 2000 for systems that are already too large for Access.
• Split your database into front-end and back-end and run front-ends in different versions of Access against a common back-end in Access 97.

SSDE

The SQL Server Desktop Engine is a new client/server storage mechanism supplied as part of Office XP and suitable for small numbers of users. It offers the following advantages:

• SSDE has better performance across a network than the file server mechanism used a in native Access databases because much of the processing takes place on the server.
• SSDE replaces MSDE (Microsoft Data Engine) from Office 2000 and Visual Studio 6.
• MSDE and SSDE can co-exist on the same PC but it's better as a long-term strategy to use one or the other as your standard.
• SSDE has the same database format as the full version of SQL Server 2000 but lacks the security features and scalability. Migration from SSDE to SQL Server is (or is promised to be) simple and straightforward.

Visual Basic

The VBA programming language is compatible with earlier versions but you must consider the following points:

• There are many new commands in Access 2002 and these new names may conflict with a name you've used for a function that you have written in an earlier version of the language.
• There is an extended object model with many new properties. Again these may clash with a user-defined name in your existing systems.
• By default, Access 2002 uses ADO (ActiveX Data Objects) instead of DAO (Data Access Objects) for programmable access to databases. Code may have to be rewritten.

SQL

Queries in Microsoft Access use a form of SQL, the Structured Query Language from IBM. The language is regulated by the ANSI committee and there are some changes incorporated into Access 2002:

• Access 2000 and earlier versions were based on the ANSI 89 standard.
• Access 2002 uses a different syntax based on ANSI 92 in order to be compatible with ADO.
• ANSI 92 is the new default but you can choose to use ANSI 89 instead.
• You cannot mix the two standards in one database so you must decide a policy before you migrate.

Note the careful phrasing, 'based on ANSI 92'  does not necessarily mean  'compliant with ANSI 92'.

References

There is (or was at the time of the launch) more information on the Microsoft web site at http://microsoft.com/office/ork/xp/welcome/depf05.htm