Database 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 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.

See Also:​

• Choosing a Database Server
• Database Corruption & Repair
• Publishing a Database