[ Support Knowledge Base Article ] 

KB2202

In most cases, database corruption is not the result of poor programming techniques or sloppy design. Most corruption can be attributed to a number of other factors which generally result in loss of file continuity.

For example, the server where the database file (MDB) is located may be restarted while users are connected to the database. Or, users may be prematurely switching off their computers during file operations.

Unlike managed data systems such as Microsoft SQL Server, Microsoft Access is essentially a file based database. When you request data from the 'database' you are merely making a request for the file across the network. Similarly, data updates occur across the network when you send back information. 

Consequently, there is a greater chance of something going wrong in the process and Microsoft Access marking the database as 'suspect'. When this happens, you will be presented with an ambiguous error message (such as 'unrecognized database format')

The source of corruption can be the network such as faulty cables, network interface cards or hubs. Or it can simply be caused by the user not correctly shutting down the application using the cross (i.e. using Alt-Ctrl-Delete or switching the power off without shutting down correctly).

Hard and Fast Rules for Preventing Corruption

(1) Always exit the database or application correctly and avoid using Alt-Ctrl-Delete to terminate the application or database unless you absolutely have to.

(2) Avoid losing power to the computer accessing or containing the database.

(3) Executing the 'Compact and Repair' options inside an Access database regularly is recommended as a procedure to prevent corruption. This should certainly be executed every week for a database that is being used daily. Compacting the database removes unwanted space, reindexes tables and improves efficiency.

(4) Avoid mixing operating systems in a multi-user environment. For example, Windows 98 with Windows 2000. In this case, it is recommended that you place the database on a Windows 2000 or Windows NT machine and disable opportunistic locking.

(5) If the database is primarily used by one person but the database file is located on a network, it is recommended that the file be placed on your local machine. This will prevent corruption caused by any unwanted disruptions in network connectivity - put simply, its one less thing that can go wrong.

(6) When designing the database tables it is prudent to be sensible about the number of fields per table. As a general indicator, most tables should contain no more than 25 fields. The more fields in one table, the greater the chance of corruption within that table.

(7) Consider disabling opportunistic locking on the file server it is Windows NT or Windows 2000 based.

(8) Do not run IPX on Windows NT Server where Jet databases are located across the network and the client is Microsoft Windows 95 with IPX/SPX. Instead run TCP-IP on the Windows NT Server and a dual protocol stack of IPX and TCP-IP on the Win95 client. (Windows NT to Windows NT with IPX/SPX will not cause the problem, nor will Novell to any client.)

(9) Avoid Large Number of Open/Close Operations in a loop (40,000 successive open/close operations to over 1,000,000.

(10) When programming, close all DAO and ADO referenced objects using the close method and setting the object to nothing.

(11) For intensive, mission critical applications or those housed on IIS it is recommended that a more robust database such as SQL Server is used (Microsoft recommends this).

Click Here for DataRevive Same Day Access Database Repair