10 Super Easy Ways to Prevent MS Access Database Corruption

by guest blogger Max Jordan
From Scottgem: This is an article submitted to me by Max Jordan, self described as a passionate learner of technology. Besides working as an MS Access DBA, he loves exploring his knowledge on MS Access and sharing tech blogs.

Specifically, in the case of IT industry “The pain of the severe loss is quite unimaginable for those who had never suffered this situation. It kills several instances because its anguish can no longer be attempted again.” Similar is the situation if Microsoft Access database gets corrupted due to any reason. This is why system experts recommend users to take preventive measures, regularly. The question arises of how to prevent Microsoft Access database corruption? So, here in this article we are going to suggest 10 ways to prevent Access database corruption.

  • Split MDB or ACCDB Files: It should be a practice of an user to split the database in two parts i.e., the front-end and the back-end. The front-end database should contain forms, reports, queries and code modules. The back-end contains just the tables with the user’s. The front-end is installed on the local drive of each machine. The back end is stored on a shared network drive. This separation of Access files proves itself beneficial when the application is to be used by multiple clients. This technique will make the database accessible more quickly and enable users to enter/edit data in front-end. And possibilities of MS Access database corruption issues decreases as back-end is secure. 
  • Utilize Decent Network Cards: The unbranded motherboards are made up of plastic and silicon wafers. These unbranded materials lead to cause corruption in files stored on the hard drive by intermittent loss of network connections. Therefore, we suggest sticking with good quality branded network cards. It is suitable to match manufactures of all NICs with networking elements. If possible then, using same brand as the switches and hubs to inhibit corruption in MS Access databases or other applications. 
  • Keep Updated with Latest Drivers: Not only device drives but, programs should also be updated with their recent editions. It is quite obvious that a system manufacture pays less attention on the device drivers. Till today’s research, 80% of crashes are caused due to lack of proper maintenance of device components. It should be the responsibility of each user (or the corporate IT department) to keep drivers updated for fixing bugs and problems. 
  • Change the Suspected Network Element: If an individual stretches the cables tightly then, wires might get bent or disconnected because snagless material is used in them. Users are advised to use switches instead of hubs for networking. A switched network is a topology that benefits users with several advantages. The major advantage of using a switch is that the network speed increases by using collision domains concept. This concept includes breakdown of the LAN leading to occurrence of fewer collision. It means that fewer packets will be transferred over the network through cables. 
  • Avoid MAC and Windows on Same Network: There is a good chance that availability of both Windows and Mac operating system users on one network leads to problem. Mac applications create large amount of traffic over the closed loop. The traffic rapidly increases when transferring of graphic files or their printing takes place. To speed up the working in a closed network, users are recommended to keep Mac and Windows users separate. As much possible, it will be by far better to keep database traffic different from graphics traffic. Permit the administrative workstations for connecting to the server along with a shared database through local switches.
  • Regularly Utilize Compact and Repair Applications: Until and unless, users utilize the Compact & Repair feature, the Access file does not get smaller in size. Even though deletion of data is taking place, it will have no affect on overall file size. Keep one thing in mind, the larger the file, is the greater the target of packet loss. Therefore, set ‘Compact on Close’ feature on the database file, which is provided in Options menu. Compact the file regularly, if you are splitting data onto a separate back-end. Apart from all this, to prevent Access database corruption clients can also utilize visual basic code to set file compacting after certain time duration. 
  • Defragment the Network Hard Drives: It is known that the Access database files are often big. The size increases with regular use of this file. This causes hand in hand problem, with hard drives. A disk with low or full storage space leads to fragmentation more rapidly. This will be having less than 25% of free space on drive. Therefore, clients should defragment their system drives to prevent any disaster. 
  • Try to Avoid Leaving the Database Opened: When an Access database is opened the backup products create back up of the data. This operation starts executing on itself leading to chances of the MS Access database file corruption. This is the reason why it is suggested to keep the application properly closed when they are not in use. 
  • Terminate the Connection When Not in Use: We hope that being a responsible technician, users might do this preventive measure regularly. This protective tip is important when users connect to ADO or DAO within an Access database. It is just one click to close connection but, without it the database will get corrupted twice or thrice.
  • Be Attentive While Using Wireless Networks: A simple cable network might work fine and it is also safe to use. But, using WiFi, multiple clients could suddenly cut off the connections. This will lead to MS Access Database corruption when someone is writing to it simultaneously. When users are reading database then, this sudden interference is not a big issue but, in case of writing, it is! So, try to avoid using Wifi when not in need. 

Observational Verdict

10 ways to prevent Access database corruption are described with details in this article. Not only for Access but, these preventive measures should also be used for other programs. If a person keeps these tips in his / her mind then, there will be less chances of corruption in Access database or in any other database file.