Neville
Silverman

Visual Basic Programmer
Microsoft Access Database Programmer

Custom Built Software 
Sydney, Australia

Upgrade a Microsoft Access database to SQL Server

The biggest limitation of an Access Database is the file sizeAs volumes and the number of users increase, the limitations of Microsoft Access soon become apparent. Slow response times, routines that hang, esoteric error messages, unstable functions, security issues and data corruption are common symptoms.

When an Access Database system needs to support more features, more records and more users, a robust Back-End database system such as SQL Server is required.

The Microsoft SQL Server Database is clearly established as an industry standard. The Microsoft SQL Server Database is easy to program and maintain, easy to add or modify data.

The Microsoft SQL Server Database is designed to provide optimum performance even during peak load times and ensures mission-critical reliability.

Database Analysis

Before starting the upgrade to SQL Server, a complete and thorough analysis will be made of the different elements of the Database Tables.

By examining the entire database, all complex relationships, hidden problems, Table & Field inefficiencies and unused objects will be identified.

Back-End Database Restructure

Upgrading a Microsoft Access Back-End database to SQL Server is reasonably straightforward. The changes to Table structures involve correcting indexes, primary keys and reserved words.

Data type conversions are made to transform Access Jet Field definitions to the appropriate SQL Server formats.

Most of these issues are easy to resolve.

Database Network communication

The technology that is available over the Network depends upon the type of Front-End Forms used.

Form Type
Network Technology available
Visual Basic.Net Forms
The fastest and best connectivity is achieved using ADO.Net. ADO.NET accesses data using disconnected Datasets – where the connection is not maintained. The disconnected Dataset can be modified, and later updated when the connection is re-established.
Access with Unbound Forms
ADODB is reasonably fast and a completely acceptable method of communication method with the Back-End Database.
Access with Bound Forms
The easiest connectivity path is to use ODBC, to connect the Access Front-End to SQL Server. This will allow a controlled upgrade from Access Bound Forms to Visual Basic.Net Forms to be carried out. The VB.Net development time is comparable to the time taken for a conversion from Access Bound Forms to Unbound Forms. However VB.Net will be far more efficient and acceptable.

SQL Server Reporting

There are three options available for reporting:

  • Microsoft Access Reports can be used with the ODBC option
  • SQL Server Reporting using a Report Wizard
  • SAP Crystal Reports

Microsoft Access reporting should only be used in the short term. Microsoft SQL Server reporting is basic, but free. Crystal Reports is fully functional, and reasonably priced.

Microsoft SQL Server Maintenance

Maintenance and optimisation of a Microsoft Access database is simple: Do a Compress and Compact once a week.

Things are very much different when using Microsoft SQL Server. Regular maintenance tasks and monitoring are needed to ensure that the SQL Server database performs efficiently and high availability is maintained.

The Maintenance Plan Wizard can be used to create basic database maintenance procedures.

SQL Server Maintenance Planning

Setting up SQL Server efficiently, securely and maintaining it requires a new set of skills.

Before your up-sizing project is deployed, there should be an administrative plan in place for the new SQL Server system. There needs to be backup strategies, recovery, administrative procedures, automation, optimisation, etc.

SQL Server provides wizards that allow the database to be administered, monitored and tuned. SQL Server also contains tools to automate data backup and to secure the data.

There is also the option of using a high speed network connection to the SQL Server database of an Internet Service Provider (ISP). This could prove a cost effective alternative – and avoid maintenance issues.

Testing With SQL Server Express

SQL Server Express can hold up to 10 gigabytes of data – enough to hold the data for all but the largest Companies. SQL Server Express only uses 1 CPU and Memory is limited to 1 GB. It is only meant for single user testing.

SQL Server Express does not have a Maintenance Plan wizard. Automatic Backup and database shrinking must be done with Transact-SQL.