Expertise
Database Upsizing
How Upsizing Works
By default, Access operates without a database server. Each user runs a separate copy of the program, which directly accesses the data file. When a database server is introduced, the users’ machines stop doing the hard work – they simply query the server, which returns the results
The Basics
Databases generally comprise two parts: the ‘front end’ which the user sees (forms, reports and queries), and the hidden ‘back end’ (tables or data storage). By default, Access stores both data and forms in a single .MDB file – this helps to keep the system easy to use for small projects.
Upgrading an existing database can involve replacing either or both of these two parts. So, if the existing Access forms are working well, the solution may be to move just the data into a database server. If the forms need enhancement, the solution may be to rewrite the project using Visual Basic, or to move to a web-based approach.
Upsizing in Brief
The first choice is to decide if the front-end (forms and reports) are still useable.
- If the forms are OK, then only the data need be moved.
- Moving the data is relatively easy, but the migration isn’t finished until a programmer has completed a compatibility check on tables, queries and code.
- Migrating the forms and reports to a new system is quite a lot more complex.
Data-Only Upsizing
Many companies have an Access database with forms that work well, but with poor reliability or performance. In these cases, the solution is to move the data only to a server, leaving the existing Access forms alone. Access supports this through a feature known as ‘linked tables’ – each table in the database is copied to the server, and the local table is replaced with a link to the new table.
Before this can happen, a suitable database server must be chosen and installed. Many companies opt for MS SQL server simply because it’s from Microsoft, and this should mean compatibility with Access. There are, however, many other options, and some of these are considerably cheaper. For more information, see Choosing a Database Server
Bear in mind that data upsizing is rarely just a matter of copying some tables and setting up links in Access – there’s usually some extra work to be done. For example, although Access and SQL server both use the same language (SQL), they actually speak slightly different versions of it. So it’s often necessary to examine the project carefully to check for incompatibilities – often there will be some small problem with tables or queries. Access also allows the developer to use Visual Basic code, and this needs extra checking and testing to ensure server compatibility.
