Monday, March 18, 2013

Need to Improve the Performance of an Access Database

Are you facing performance issues with your Access database? If Yes then splitting an Access database is the best solution for you. Split an Access database is a process to reorganize your database into two files; one is front-end database and another is back-end database. Back-end database contains all the tables of database while front-end database contains all the other objects like forms, queries and reports. Users have their own local-copy of back-end database to interact with the front-end database.

Have you decided to split the database? Before you start splitting an access database; make sure following points:
  1. Backup the database: Backup helps you in the restoring of database in case you want to revert back.
  2. Stop all the operation of database: User database modifications will not be saved during split.
  3. Split a copied database: If database is on the network drive then copy it on the local drive of your computer/laptop and start with the copied database.
How to do it? You can split an Access database by following below steps:
  • Open the copied database from your local drive where you have saved the copied database.
  • Go to the Database Tool->Move data group and click on the Access database.
  • Database splitter wizard will start.
  • Now click on the split database.
  • Give the name, file type & location for the back-end database file.
  • Done message will be display.
Note: If your database is password protected, the new back-end database will be created without password and accessible to all the users. You will need to create a new password after split is done.

Benefits of it: It will improve the performance of your database along with availability, security, reliability & flexibility.

Availability: Now your database users can edit the data more quickly because only data is available on the network drive.

Reliability: Now your database is more reliable than ever before. If a user encounters any problem and database closes suddenly then corruption is limited to that user only because he was using the back-end database file with the linked table.

Flexibility: Now your users can develop their own forms, reports & queries without affecting the other database users.

Alternative: If you don't want to split your database to gain high performance then you have other options too.
  1. Share your database using SharePoint Site.
  2. Use your Access database with a database server like SQL server

No comments:

Post a Comment