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.
Front End Upsizing
Front-end upsizing rarely provides a solution to performance problems because forms and reports aren’t performance critical. Instead, there are two main reasons for front-end upsizing. The first is to move to a better programming environment – many systems begin and evolve on an Access database but, as user requirements, scope of use and complexity of functionality increase over time, they eventually outgrow it. The second is to allow the database to be used on the Internet or Intranet, a requirement that is increasing dramatically as remote working becomes more common.
The Access programming environment, whilst great for small projects, is generally regarded as unsuitable for larger projects: the VB language supported by Access is quite cut-down, and not hugely flexible. Companies that want a more friendly user interface, interactive graphics or integration with other systems generally need to look elsewhere. In past years, the default choice has been to move to Visual Basic, although there were other choices – Borland’s Delphi for example. Recently, this has changed as Microsoft have released Visual Studio .NET, with a dramatically improved Visual Basic, and a new language called C#. This platform, although very new, is undoubtedly the best route forward for many applications.
Another choice is to move the system to a web-based approach. The key benefit of this approach is the system can be accessed from any machine on a company network without installing any extra software. This option is discussed in Publishing a Database on The Web
