Friday, March 29, 2013

Top 10 Tips to resolve Access Database error message

Every now and then Access DBAs may get some error messages in their Access database that they have not thought. They will try to resolve the error messages but do not know where to start. If you are also a victim of Access error messages then this article is for you, try below steps and I am quite sure you will find a resolution for the error message.

1. Compile the Modules: Sometimes a compile error in a single module can cause the problem in other module so it is a good idea to check all the modules are compiling successfully. You can do this by following procedure:
Click on the Debug->Compile from any module.

2. Compact & Repair Database: Sometimes compacting & Repairing can fix the problem, and it is a good idea to perform compact & repair on the regular interval to avoid such types of problem. You can do this by following procedure:
Tools->Database Utilities->Compact & Repair

Note: Make a backup of database before compact & repair.

3. Restart the Machine: Sometimes problem has been occurred due to memory problem so it is a good idea to shutdown your machine for few seconds and turns it on again. Now try to open your Access database, you may find that problem is solved.

4. Decompile & Recompile the Database: Sometimes Access generates error message due to wrong residual compiled code so it is the better idea to decompile & recompile the database.

5. Import the Database into a New Database: If above resolutions do not work for you then try to import the entire object from an old database into a newly created database. You can do this by following procedure:

Create a new database-> Click on the File menu-> Get External data -> now click on the Import-> select old database file-> Import

6. Restore from Backup: Restoring from backup is the best option to get back all your data from the database. If you have an updated backup of your database then it is the right time to use it.

7. Repair/Reinstall Access Database: Sometimes MS Access generates error messages due to missing or corrupt libraries so it is better idea to repair or reinstall the Access database. You can repair Access database from the control panel while re-installation can be done with the help of installation disk.

8. Update Latest Updates: It might be possible that you are getting error message due to missing updates so try to update all the latest updates for your Access database version. You can download all the latest update from the Microsoft website

9. Get Professional Help: There are some people on the internet who will provide help for you without any cost. Popular websites for Access help are,,,, and many more.

10. Addition Tips: There are some data recovery companies that offer Access database repair software to repair corrupt Access database. Give them a try and see what they can do for you.
Note: Always try free demo version of any software before buy.  

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