Microsoft’s Access Database is extremely popular and is adequate for most company applications. But Microsoft’s Access is meant for small projects with few users. As frequently happens, a small Access application grows and slowly becomes the heart and soul of a company administration system.
As 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 the Access system needs to support more features, more records and more users, a robust BackEnd database system such as SQL Server is required.
There is less Chance of Data Corruption
In Access, the database MDB file is opened directly. If the network connection is unexpectedly broken or a PC or the Server computer breaks down, you are almost guaranteed that data will be corrupted.
SQL Server is different – it runs as a service. The Front-End Access database does not have direct access to the BackEnd database file. If the server shuts down unexpectedly or the network connection is broken, the integrity of the data is maintained.
Database Log Files Enable Data Recovery
SQL Server has a distinct advantage over Access in that all transactions (database updates, insertions and deletions) are kept in a log file.
The log records contain all the changes to the data and enough information to undo the modifications made during each transaction. In the event of system failure, the log file can be used to recover the data.
Support for More Concurrent Users
Access supports a maximum of 255 concurrent users. But this is a theoretical limit, bearing no relationship to reality. In the real world, it is common to experience major performance issues with as few as 10 users (and sometimes much less) attempting to simultaneously use the BackEnd Access database over a network.
SQL Server supports a concurrent user base that is limited only by available system memory. Because of the optimised query-processing engine and ability to utilise simultaneously multiple computers, multiple processors and hard drives, it can scale to meet any requirement.
Support for a Larger Database
Access supports a maximum database size of 2 gigabytes plus linked tables. Although use of linked tables theoretically enables more much data to be stored, it is common to experience performance issues when handling large volumes of records.
Consider upgrading an Access database when the database records exceed 100 megabytes in size.
SQL Server, on the other hand, has vastly improved storage capabilities, allowing for 1,048,516 terabytes of data to be stored efficiently across multiple devices. It also has self-repairing and self-compacting features, making it a particularly robust and efficient database solution.
Performance and Administration
Access has limited backup features and does not support point-in-time restores. Access does not have performance monitoring features.
SQL Server on the other hand provides wizards that allow the database administrator to monitor and tune performance. It also contains tools to automate data backup and secure the data.
Upgrading to SQL Server
Upgrading a Microsoft Access Back-End database to SQL Server is reasonably straightforward.
Here are some of the issues that may need attention:
- Amend Tables with non numeric indexing or no indexing
- Ensure all Tables have a Primary Key
- Ensure AutoNumbers are in the first column of a Table
- Reserved words need special treatment
- Remove duplicate Indexes from Tables
- Add a Timestamp to all Tables
- Use DRI (Declared referential Integrity) and not Triggers
- Change all nvarchar, ntext and text fields to varchar and varchar(max)
- Change all money to decimal(8,2)
- Change all DateTime fields to Date
- Change Tables with Subdatasheet property of Auto to None
- Change True/False Integer fields to Yes/No
Most of these issues are easy to resolve.
To create the SQL Server database, use the SQL Server Wizard in the Database Tools of the Access Ribbon. Then use ODBC to link to the SQL Server Tables. Visual Basic can be used to manipulate the data with ADO and an OLEDB connection. And away you go!
Satish Kartan has been working with SQL Server for the past 20 years. To read more, please visit Satish Kartan’s blog at http://www.sqlfood.com/ where he has shared more details.
Satish Kartan 