Summary: The MS Access database file size continues to grow with increasing usage. Large Access database files are prone to corruption and other issues. So, you can reduce the file size by compacting and repairing the Access database file. Read this post to learn how to compact and repair an Access database using VB.NET code. If your database got corrupt due to large size, you can use the advanced MS Access repair tool mentioned in this post.
Contents
An Access database file increases in size with the continuous addition of data. Also, the database file can grow in size in any of these situations:
- MS Access creates temporary objects (that are hidden) for performing several tasks. For instance, Access may create objects to query external sources when used as a back-end. Even though they are no longer needed, these objects remain in the database.
- When a database object is deleted, the disk space consumed by that object remains empty and is not reclaimed. And, more space on the disk is occupied for a new object.
The large size of the file increases risk of database corruption. The corruption risk is higher when users frequently edit the MEMO fields in a Visual Basic for Applications (VBA) module. To prevent corruption and other issues, you can reduce the Access database file size by compacting and repairing the database.
Why Compact and Repair?
Running the Compact and Repair utility helps:
- Reorganize indexes for increasing database efficiency.
- Reclaim empty space from deleted records or objects.
- Lower the risk of corruption of a split database.
How to Compact and Repair Access Database Using VB.NET?
You can schedule to compact and repair an Access database through code in VB.NET. However, you must have exclusive access to the database before compacting and repairing it. The detailed steps are as follows:
- Add a reference to ?Microsoft Jet and Replication Objects 2.6 Library?.
- Copy and paste the following code to display a warning message if the database is in use.
| Dim strAccessDBPath As String =”ENTER PATH OF YOUR ACCESS DB” Dim LockedDbFileInfo As New System.IO.FileInfo(strAccessDBPath.Replace(“.accdb”, “.laccdb”)) If LockedDbFileInfo.Exists Then ‘Give warning that the database is in use. End If |
- Create a temporary folder, like TempFolder, and then copy the database using the below query:
| Rename(strAccessDBPath, TempFolder.FullName & “\” & “db1.accdb”) |
- Create a JRO JetEngine object and compact the database (i.e., db1 in our case) by calling the compact method. Next, replace the original database with the compacted one. Once the compacting operation is complete, delete the temp folder.
| Dim JRO As New JRO.JetEngine JRO.CompactDatabase(“Provider=Microsoft.Jet.OLEDB.4.0;” & “Data Source= ” & TempFolder.FullName & “\” & “db1.accdb”, _ “Provider=Microsoft.Jet.OLEDB.4.0;” & “Data Source=” & strAccessDBPath & “;Jet OLEDB:Engine Type=5”) TempFolder.Delete(True) |
What If the Access Database gets Corrupted?
You can run the Compact and Repair tool to fix a corrupted Access database. If the utility fails to repair the database, you can use an advanced Access database repair tool, such as Stellar Repair for Access. The software can help you fix corruption in damaged or corrupted .accdb and .mdb database files. Also, it helps repair tables, modules, macros, and other objects of the database. It even allows repairing a corrupted split Access database and linked tables. The software also previews all the recoverable objects extracted from the corrupt file, including tables, linked tables, queries, etc.
Conclusion
The size of an Access database can increase rapidly due to the accumulation of temporary database objects that are no longer required. Also, the database size can grow when the disk space occupied by an object is not reclaimed. Furthermore, the large database file size makes it vulnerable to corruption. And so, compacting and repairing the database is crucial to reduce the size of the database file. This blog explained the steps to compact and repair an Access DB through code in VB.NET. The code helps schedule running the Compact and Repair utility.
If the Access database has turned corrupt due to its large size, use Stellar Repair for Access to repair the corrupted DB and restore all its components.
