Sunday, August 3, 2008

Out of Memory Error in MS Access 2003

MS Access is a database management tool which easily works well on less memory PC’s. But some user was facing the “out of memory error”. This error often comes on those computers which have more than 1 GB memory. This is an error that is becoming a nuisance. It comes on different machines....all which have Access 2003. The setup uses replication on the front end database to multiple users with one design master. All the front ends then access the same back end database on a network drive. This error most often occurs when a new module or module update is made to the Design Master. Ultimately we end up resurrecting a new Design Master from one of the replicas or a backup of the Master itself. To get rid from this error we can try compact and repair but it close the database, reboot the machine, etc. But until we scrap the design master and resurrect one from one prior to the changes...the same error continues.

Strange error messages such as "out of memory" are usually due to access database corruption. Access databases are particularly prone to corruption when there are multiple users of the same database on a network share. If there are 3-4 users, the risk is pretty low, but anything beyond that and there could be frequent difficulties. Also, the fact that the errors go away after you restore from backup strongly suggests that your design master is getting corrupted. To get rid from this solution we can go for access repair software to repair database but we will discuss it last. When you are working with multiple users accessing the same database on a network share, even you will notice that if there was anyone else who had the database open when I saved a change to anything the database would frequently get corrupted. In fact, most of the corruptions can be experienced when you save or make a change while someone else was in the database. Although you are not working with replication at the time, your situation is still very similar because each user of each replica still has to open the design master so that changes and data can be synchronized. Here are some tips that might help, if indeed the design master is getting corrupted. Remove everybody out of the database and open it in exclusive mode before making changes to the design master. You can also try to disable "opportunistic locking" on the file server. It's a registry edit that has to be done on the sever so unless you are the server's admin or are in really good with him/her, good luck on getting this accomplished. "Opportunistic locking" has been known to cause corruption in jet databases when multiple users are using a database on a file server.

If your database is corrupted and you are continuously getting this error and compact and repair doesn’t help. Try to install the updated version of Microsoft Jet Database Engine 3.5 or new patches from Microsoft. If it doesn’t help, don’t panic for your database. You can use access recovery utilities which recover the database and fix the error.

Stellar Phoenix access recovery 3.0 is one of the best utility to repair your database or mdb files. It fixes the error cause database corruption and repairs your database as previous. This access recovery software works well with older version of MS Access as well as newer version such as access 2002, Access XP, access 2007 etc. Stellar phoenix access repair recovers Queries, Forms, Reports, Macro and modules. You can see the preview of repair database with the help of demo version of this software.