Compact and Repair MS Access Database
The easiest way to avoid Database corruption is to compact & Repair it regularly.
In Access 2007, you can manually compact by clicking the Office Button, select Manage, and then choose Compact And Repair Database from the Manage This Database list.
To automate the process using VBA here is the Code.
Code:
Function Database_Compact_and_Repaire(strDbPath As String, Optional strDBPass As String = "") As Long
On Error GoTo ErrFailed
'Delete the existing temp database
If Len(Dir$(strDbPath & ".tmp")) Then
VBA.Kill strDbPath & ".tmp"
End If
With CreateObject("JRO.JetEngine")
If strDBPass = "" Then 'DB without password
.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDbPath, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDbPath & ".tmp;Jet OLEDB:Encrypt Database=True"
Else 'Password protected db
.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDbPath & ";Jet OLEDBatabase Password=" & strDBPass, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDbPath & ".tmp;Jet OLEDB:Encrypt Database=True;Jet OLEDBatabase Password=" & strDBPass
End If
End With
On Error GoTo 0
VBA.Kill strDbPath 'Delete the existing database
Name strDbPath & ".tmp" As strDbPath 'Rename the compacted database
ErrFailed:
Database_Compact_and_Repaire = Err.Number
strDbPath = vbNullString
strDBPass = vbNullString
End Function
You can call this function as below.
Without Password
Code:
Call Database_Compact_and_Repaire("d:\test.MDB")
With Password
Code:
Call Database_Compact_and_Repaire("d:\test.MDB", "password")
How to repair Access database file?
If any of you is looking for a solution to repair Access database file, I have multiple solutions here to share. First you should try Compact and Repair utility, built-in tool to repair corrupt Access database. Microsoft also provides JetComp.exe that can repair Access database. There's one more thing you can do here. You can import corrupt database file into a new database file. I can share this blog post here in which you can find the complete information: Repair Access Database with Compact and Repair Utility | N. Sem's Blog
Before you can purchase any paid software, try the manual tricks given in the blog post shared above. I hope they would be helpful.
Thanks and best regards.
You should a professional solution.
Quote:
Originally Posted by
Manfred
Hello, I followed these steps, but I was not lucky.
What did you try? Was that Compact and Repair utility or JetComp.exe utility? Or did you try to import corrupt database into a new database? In case of severe corruption, you can't fix a corrupt database. You should, now, try a professional recovery solution to recover content from corrupt database file. This is all you can do here.
Quote:
Originally Posted by
Manfred
I need to repair a database in Access 2010, which tool do you recommend?
I can't recommend any such software since I haven't used any.
Quote:
Originally Posted by
Manfred
I used AccessFIX but it isn't free.
No freeware utility is available to repair Access database. There are only commercialware tools in the market, and most of them are available with free demo/trial version. There might be some tools in the market which are more effective and less expensive than the one you used. You should try them. But make sure you first download free demo/trial version.
Thanks.
How to repair Access database?
Quote:
Originally Posted by
Manfred
I need to repair a database in Access 2010, which tool do you recommend?
I was searching best Access database repair tool on Google, and I found this - http://www.data-recovery-solutions.c...e-recovery.php
You should try it once. But I'm recommending this software. And remember my words, try before you buy. You can download its demo version for free evaluation. By using demo version, you can see what you could recover from the corrupt Access database before you can actually pay for software. If you're satisfied with the results, then only you should purchase the software.
Hope it would be helpful for you.