Visit our new web pages

We have launched a new extended set of web pages at www.alvechurchdata.co.uk. These old pages will stay on the server because people still have links to them but they will not be updated.

The new version of this page is at www.alvechurchdata.co.uk/hints-and-tips/accmigr.html.

Migrating to Access 2002

Migration to a new database system must be considered carefully. Compared to a spreadsheet or word processor file, a typical database file:

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

Hardware

In general, Office XP needs a more powerful PC than Office 2000. Access 2002 may need yet 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 users PC rather than using a database on a separate server.
• If you use SQL Server Desktop Engine.

File format

You have five options:

• Use Access 2000 format (or Access 97) to retain compatibility with existing software. This is Microsofts recommended rollout technique.
• Use Access 2002 format to be able to use the new features of the language immediately.
• Use SQL Server Desktop Engine for systems that are planned to grow and will 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 different front-ends against a common back-end.

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.

• SSDE has better performance across a network than the file server mechanism used in native Access databases.
• SSDE replaces MSDE (Microsoft Data Engine) from Office 2000 and Visual Studio 6.
• MSDE and SSDE can co-exist on the same PC but its better to use one or the other as your standard.
• SSDE has the same database format as SQL Server 2000 but lacks the security features and scalability. Migration from SSDE to SQL Server 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 the new names may conflict with a name youve used for a function that you have written.
• 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 Access use a form of SQL, the Structured Query Language from IBM. The language is regulated by the ANSI committee.

• 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 cant mix the two standards in one database so you must decide a policy before you migrate.

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

References

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