Ms Access Repair Database

0 views

Skip to first unread message

Danielle Just

unread,

Aug 3, 2024, 4:20:06 PM8/3/24

to kannkachatu

There are several approaches to compacting and repairing a database. A common practice is to automatically compact and repair a database when it closes. In addition, you can manually run the Compact and Repair Database command, when you have a database open, and on a database that is not open.

Make a backup of the database During the repair process, Access may truncate some data from tables that are damaged. It is sometimes possible to recover this data from a backup. In addition to your regular backup strategy, you should make a backup immediately before you use the Compact and Repair Database command. For more information, see Protect your data with backup and restore processes.

Get exclusive access to the database A compact and repair operation requires exclusive access to the database file, because the operation may disrupt other users. You should notify other users when you plan to run a compact and repair operation, so that they can avoid using the database during that time. For more information, see Open an existing Access database.

Tell users how long they must avoid using the database. If you run compact and repair operations on a regular basis, keep a record of how long it takes. You can then make more precise estimates that give other users guidance on how long they should avoid using the database.

Get sufficient file permission to the database If you do not have sufficient permissions and you need to compact and repair a database, contact your system administrator for assistance. For more information, see Changes to file sharing over a network in Windows.

You can select the Compact on Close database option if you want to automatically compact and repair a database when it closes. Setting this option affects only the database that is currently open. Set this option separately for each database that you want to automatically compact and repair. In multiuser databases, you might not want to set this option, because it can momentarily disrupt database availability.

If Access completely repairs a corrupted file, it displays a message stating that the repair was successful and that you should check the database contents to be sure that everything is as it should be.

If Access is only partially successful, it keeps track of database objects that it could not repair in a system table named MSysCompactErrors. Access opens the MSysCompactErrors table in datasheet view. If you have a previous backup before the database was corrupted, you can use the MSysCompactErrors table to decide which objects to import into your repaired database. To reveal system tables, right click the Navigation title bar, and then in the Navigation Options dialog box, select Show System Objects.

This overview explains how using the Compact and Repair Database command can help prevent and correct the following problems that sometimes affect a database: files growing larger with use and files becoming corrupted.

As your database file fills up with the remains of temporary and deleted objects, its performance can degrade. Objects may open more slowly, queries may take longer than normal to run, and typical operations generally seem to take longer.

In particular circumstances, a database file can become corrupted. If a database file is shared over a network and multiple users work directly with the file simultaneously, that file has a small risk of becoming corrupted. The risk of corruption is somewhat greater if users frequently edit data in Long Text fields, and the risk grows over time. You can mitigate this risk by using the Compact and Repair Database command.

Often, this type of corruption results from a problem with a Visual Basic for Applications (VBA) module and does not pose a risk of data loss. However, this type of corruption does pose a risk of database design damage, such as lost VBA code or unusable forms.

I have a python script running from an ArcGIS custom toolbox. The script deals with Access databases (mostly personal geodatabases). What I am wondering is, is there a way to compact and repair an access database (specifically a personal gdb) from my python script?

Use Python's win32com library and make a call to the VBA method CompactRepair(). Do note, a backup file path is required (one that does not exist yet) during the process but can be deleted after successful compact, using os.remove() for that need:

Here is the thing: I found that I can use the COM object library "Microsoft Office 12 Access Database Engine Object Library" and use the DBEngine class and call its CompactDatabse method.But there doesn't seem to be a place for me to provide the database password; Seems like Office 12 Database Engine doesn't have any documentation anywhere.I found some documentation for older versions of CompactDatabase method but those don't help me at all.

There is a branch for Microsoft Jet Replication Objects (JRO) Reference, so JRO (one of the libraries comprising ADO classic) is still officially supported (and AFAIK does still work) for ACE e.g. the accdb format. The CompactDatabase method takes two parameters, both of which is an OLE DB connection string. Therefore, supplying the database password should be no different from a regular connection e.g. using Jet OLEDB:Database Password in the connection string. Remember, for accdb format you need to include Jet OLEDB:Engine Type=5 in the connection string e.g.

Bear in mind, most of the Access Database Engine was developed during the 1990s when proprietary formats were all the rage; I suspect documentation was suppressed for commercial reasons and the knowledge is simply lost. So there are many holes in the currently-available documentation, and large ones at that. Often, the only way of finding out how the engine works is to discover it through actual usage. The documentation that does exist contains some appalling errors: already today I've posted a couple of examples on SO where potentially use yet fictitious functionality (LIMIT TO nn ROWS, CREATE TEMPORARY TABLE, etc) has been advertised in the Access Help. Caveat emptor.

You don't need JRO. JRO should not even exist -- it is one of the ugly stepchildren of ADODB that came about because of Microsoft's misguided attempt to replace Access/Jet/ACE's native DAO with ADO, a non-native abstraction layer. JRO is there to provide support for Jet-specific functionality not available in ADODB.

If you used DAO instead, you'd have all the functionality right there already, and it works for all formats supported by the version of Access you're using (since the DAO version is synchronized with your Access version, that is, when the db engine is enhanced, there's a new version of DAO to go with it).

So, in A2007, you'd simply use the DAO compact methods (there's been no repair operation as a separate command since Access 2, and a repair happens only if the database engine determines during the compact that a repair is necessary). If you're working from outside Access, then you need to use the ACE-compatible version of DAO. If you do not have A2007 installed and have installed the ACE independently, you have that version of DAO installed along with it.

Just an FYI regarding JRO, it does not support Access version 2007 or higher database files. While the CompactDatabase method in this library appears to function when using the ACE OLEDB Provider, it will in fact generate a 2002-2003 (Jet 4.0) database file.

Note that this test backup is a stress test of 500,000 files (low version count), so there was a lot data to be rebuilt - however since the destination was a local file system, it may not accurately take into account file transfer overhead.

So the actual length of a repair, from a downloads point of view, can vary greatly depending on what actually went wrong. On top of the downloads, there is also a lot of database processing that has to happen - and some of that code is probably not as optimal as it could be.

Note that for test restores you should either restore an older version of a file or make sure to enable the --no-local-blocks parameter (which tells Duplicati to not use the local file to speed up the restore).

It does seem oddly long: if it had to read every byte of the source dataset, that implies a read speed of 1.4TB / (14 days) / (86400 seconds per day) = 1.2MB/s. Given the drives that are in there it should have been able to do that maybe a hundred times that fast if the data were accessed linearly. So does the rebuild scan the source dataset more or less randomly, meaning the bottleneck is seek speed/IOPS, not bandwidth?

Those destination files (not necessarily all of them) need to be downloaded and are used to rebuild the local database. Technically, most of the local database is just a faster way to access all the history and hashes that exist in the destination - otherwise things like deduplication would require TONS of downloads with each backup job.

So a better throughput calculation would be to look at the destination size (either directly or in the job summary) and divide that by repair time. And when looking at potential bottlenecks like RAM and IOPS you also need to consider network bandwidth. (Of course if the destination is a physical drive attached to the machine, network bandwidth can be ignored.)

So the per-file progress works, but as more and more hashes are added each file of size X appears to take longer and longer. Toss a large file in there and the hash lookups take so long it appears to stall.

Summary: Is your Access database corrupt? Need to know how to repair a corrupt Access database? Read this post to explore the methods to repair a corrupt Access database. Also, check out step-wise instructions to recover data from the corrupted db. If nothing works, use Stellar Repair for Access software to repair your database and recover its objects without downtime.

Corruption in MS Access database usually occurs when errors are found in the underlying binary file format of the database. When that happens, you may face random errors or difficulty in accessing Access database files (.MDB/.ACCDB).