Microsoft Access Specialist

Microsoft Access Upgrades

Upgrade from Microsoft Excel to Microsoft Access

It is very common for Access database solutions to start off initially as Excel spreadsheets.

These are often put together quickly to solve a particular problem and then grow in size and complexity to such an extent that they become very difficult to manage.

Excel solutions will also often run into problems when multiple users need to work with the data at the same time, when complex but easy to produce reports are required or where the circumstances that prevailed when the original spreadsheet was designed have changed significantly

It is usually relatively straightforward to migrate an Excel database into a Access database.

We have migrated a lot of Excel spreadsheets to Microsoft Access and can advise you as to the best solution and likley cost of moving your Excel spreadsheet to Access.

Assuming that your Excel data has been entered in a fairly structured manor it is normally possible to move all the data across automatically so that no data is lost and no manual re-entry of data is required.

Upgrade from one version of Microsoft Access to another

Although the latest version of Microsoft Access is 2007 the majority of databases in use were developed in earlier versions and in most cases are still run using these earlier versions.

In many cases there will be no particular need to upgrade to a later version of Access.

In other cases you may wish to consider upgrading for the following reasons:

• Later versions introduce new features which you wish to take advantage of
• You are upgrading you PC, version of Windows or Microsoft Office and need to change your version of Access in the process
• Performance and reliability are better in later versions
• Integration capabilities are better in later versions
• Some earlier versions are no longer supported by Microsoft

Access has an inbuilt wizard which will attempt to upgrade your database for you. For simple databases this will often be sufficient but for more complex databases, particularly those which integrate with other software or components, the wizard may not be sufficient.

We have a lot of experience upgrading Access databases and can help you ensure that your upgrade is smooth and trouble free. We may also be able to significantly reduce your Microsoft Access/Office licencing costs when upgrading as well.

Upgrade to Microsoft SQL Server

Microsoft Access provides excellent solutions for small to medium sized teams and workgroups. It offers rapid, powerful and highly cost effective solutions to be built according to your precise requirements.

However, there are situations where Access may struggle; for example when the number of concurrent users or the size of the database increases significantly.

If you have reached this point or are concerned about the scalability of Access then you should consider a combined Microsoft Access and Microsoft SQL Server solution.

Microsoft SQL Server is essentially the "big brother" of Microsoft Access and is Microsoft's Enterprise database platform.

SQL Server will scale to handle very large numbers of users, large databases and transaction volumes as well as deal better with users accessing the database over a slower network link such as a VPN.

If you have developed a business application in Microsoft Access then you can migrate to Microsoft SQL Server and protect your investment in Access by keeping your existing Access front-end (screens, reports and code etc) and moving the data (tables) into Microsoft SQL Sever.

What is involved in converting from Access to SQL Server?

In essence there are 2 steps:

• Migrate the data tables to SQL Server
• Modify the front-end forms and reports etc to work with the SQL Server database back-end.

However, there are different ways to accomplish these steps and the methods chosen will have a significant impact on the performance, power and maintainability of the ultimate solution.

For example it is possible to use the Access Upsizing Wizard to move the tables to SQL and then link the MDB file to the new server database. This is quick and easy but has disadvantages:

• It only works for very simple databases - the wizard cannot deal with the more complex queries found in more sophisticated databases
• It fails to take advantage of the full performance available from SQL Server as it leave in place a layer of the Access Jet Database Engine which is inefficient when communicating with SQL Server

Generally we recommend creating an Access Data Project (.adp) file and using this to talk natively with SQL Server so that the Access front-end can take full advantage of the power and sophistication of SQL Sever. This is a longer and therefore more expensive process but the benefits are huge.

We have experience in migrating databases from the very small, which literally take a few hours to migrate, through those which are more complex and take significantly more time to migrate. We understand the issues involved and the options available.

If you would like to know what is involved in migrating your Access application to SQL Server contact us and we will arrange to look at your database. We will then review this and tell you what is required to migrate it to SQL Server, the options available the the likely cost.

If you have already tried to migrate your database and run into problems then please call us for advice and assistance.