VBA compact-and-repair of a closed (corrupted) database from a new, blank Access file (1 Viewer)

[alan2013]

Hi. I wonder whether someone could advise me on the following, please.

I recently encountered the MSysDb error-message on an Access 2021 database I've been developing.

"The Microsoft Access database engine could not find the object 'MSysDb'. Make sure the object exists and that you spell its name and the path correctly. If 'MSysDb' is not a local object, check your network connection or contact the server administrator'.

The database won't open at all.

My backup was created too long ago......(I can only say that with the benefit of hindsight !). I'd really like to be able to recover the latest version.

I've tried importing the database objects into a new Access file, but that doesn't work. (I get the above error-message).

I'm now trying to force a compact-and-repair of the database from a separate Access file, using the following :

Sub CompactRepairDatabase()
    Dim strDB As String
    strDB = "C:\Users\USER\Documents\Datebase101.accdb"

    'Compact and repair the database
    Application.CompactRepair SourceFile:=strDB, DestinationFile:=strDB
    
    MsgBox "Done", vbInformation
End Sub

However, I'm getting the error-message :

"Run-time error '7847'
C:\Users\USER\Documents\Database101.accdb already exists. Microsoft Access must create a backup of your file before you perform the repair operation. Enter a name for the backup file."
End | Debug | Help

What do I need to do to get this VBA to work ?
TIA

 

[arnelgp]

try using this code:

Sub CompactRepairDatabase()
    Dim strDB As String, strTemp As String
   
    strDB = "C:\Users\USER\Documents\Datebase101.accdb"
    strTemp = Replace$(strDB, Dir$(strDB), "temp.accdb")
   
    If Dir$(strTemp) <> "" Then
        Kill strTemp
    End If
       
    'Compact and repair the database
    Application.CompactRepair SourceFile:=strDB, DestinationFile:=strTemp
   
    If Dir$(strTemp) <> "" Then
        Kill strDB
        Name strTemp As strDB
    End If
   
    MsgBox "Done", vbInformation
End Sub

 

[alan2013]

try using this code:

Sub CompactRepairDatabase()
    Dim strDB As String, strTemp As String
  
    strDB = "C:\Users\USER\Documents\Datebase101.accdb"
    strTemp = Replace$(strDB, Dir$(strDB), "temp.accdb")
  
    If Dir$(strTemp) <> "" Then
        Kill strTemp
    End If
      
    'Compact and repair the database
    Application.CompactRepair SourceFile:=strDB, DestinationFile:=strTemp
  
    If Dir$(strTemp) <> "" Then
        Kill strDB
        Name strTemp As strDB
    End If
  
    MsgBox "Done", vbInformation
End Sub

I’m away from my PC for a few hours, but am really eager to try your code. I’ll get back to you. In the meantime, Thanks!

 

[isladogs]

"The Microsoft Access database engine could not find the object 'MSysDb'. Make sure the object exists and that you spell its name and the path correctly. If 'MSysDb' is not a local object, check your network connection or contact the server administrator'.

I'm sorry to be the bearer of bad news but I think its highly unlikely you will be able to recover your database as a whole.

MSysDb is not a system table but is a fundamental part of all Access databases.
If it has got corrupted or somehow lost, your database will not be repairable either using compact and repair or decompiling.

The code supplied by @arnelgp works by opening your database externally in order to compact it. You've already said the database cannot be opened.

Realistically, your only chance of success is likely to be attempting to import all objects into a new blank database BUT I suspect that may also not work. You MAY be able to retrieve some objects yourself or by using specialist recovery software, but possibly not all the code.

 

[Gasman]

When you get it back to some form of a working DB, back it up regularly.

You could use something like this.

Public Function BackupDb()
Dim vDir, f, vExt, vTargFile, vSrcDB, vDB
Dim vTargDir, vSuffx
Dim strBackUpPath As String


   vSrcDB = CurrentDb.Name
   If vSrcDB <> "" Then
        getDirName vSrcDB, vDir, f
        vExt = Mid(f, InStrRev(f, "."))
       
        vTargDir = vDir & "Backup\"
   
        vSuffx = "_Backup" & Format(Now, "yymmdd-hhnn") & vExt
       
         vTargFile = vTargDir & f & vSuffx
         Copy1File vSrcDB, vTargFile
  End If
    
     'FRONT END APP
   MsgBox "Done - " & vTargFile, vbInformation, "Backup"
End Function

Public Sub getDirName(ByVal psFilePath, ByRef prvDir, Optional ByRef prvFile)
    'psFilePath: full file path given
    'prvDir : directory name output
    'prvFile: filename only output
Dim i As Integer, sDir As String


i = InStrRev(psFilePath, "\")          'not available in '97
If i > 0 Then
  prvDir = Left(psFilePath, i)
  prvFile = Mid(psFilePath, i + 1)
  ''If Asc(Mid(prvFile, Len(prvFile), 1)) = 0 Then RemoveLastChr prvFile
End If
End Sub

I use it perhaps a few times a day, if I am making changes to any of my personal DBs

 

[alan2013]

I’m away from my PC for a few hours, but am really eager to try your code. I’ll get back to you. In the meantime, Thanks!

Hi again. At my first attempts using your suggested code, I get run-time error 31523.

Based on this - and isladogs' post too - I need to re-think.....

 

[alan2013]

I'm sorry to be the bearer of bad news but I think its highly unlikely you will be able to recover your database as a whole.

MSysDb is not a system table but is a fundamental part of all Access databases.
If it has got corrupted or somehow lost, your database will not be repairable either using compact and repair or decompiling.

The code supplied by @arnelgp works by opening your database externally in order to compact it. You've already said the database cannot be opened.

Realistically, your only chance of success is likely to be attempting to import all objects into a new blank database BUT I suspect that may also not work. You MAY be able to retrieve some objects yourself or by using specialist recovery software, but possibly not all the code.

Thanks for the further info and suggestions. I'm now having a re-think / re-investigation....

 

[alan2013]

I'm sorry to be the bearer of bad news but I think its highly unlikely you will be able to recover your database as a whole.

MSysDb is not a system table but is a fundamental part of all Access databases.
If it has got corrupted or somehow lost, your database will not be repairable either using compact and repair or decompiling.

The code supplied by @arnelgp works by opening your database externally in order to compact it. You've already said the database cannot be opened.

Realistically, your only chance of success is likely to be attempting to import all objects into a new blank database BUT I suspect that may also not work. You MAY be able to retrieve some objects yourself or by using specialist recovery software, but possibly not all the code.

You mention 'specialist recovery software'. Which one(s) would you suggest / recommend.....[if you're able to on here?]

 

[Trevor G]

Look at this link it suggests various steps as well as software which may help. I've not encountered this issue but hopefully the Google search on the missing file might provide a solution.

How to Fix The Microsoft Access database engine could not find the object "MSysDb” Error?

Summary: This blog discusses ‘the Microsoft Access database engine could not find the object “MSysDb”’ error. It outlines the possible reasons behind the error and how to fix the issue. When attempting to open an Access database (such as a table, query, form, or report), you may encounter the...

community.spiceworks.com

 

[GPGeorge]

You mention 'specialist recovery software'. Which one(s) would you suggest / recommend.....[if you're able to on here?]

The service I would recommend is EverythingAccess.com.

 

[isladogs]

Most of the highly advertised recovery software does nothing that an expert Access user can't do for themself.
In the case of badly corrupted database (like yours), they are often a complete waste of money.

Note that the link in post #9 is written by someone from the most heavily advertised company. It is factually incorrect in places. Those steps won't work. It is typical of their articles in suggesting a series of steps that usually achieve nothing before recommending their software at the end. Clever but misleading marketing

Like George, the only service I would recommend is EverythingAccess.com.

 

[Trevor G]

Thanks for pointing that out Colin I have taken note of what I came across in my post #9. As mentioned I haven't encountered this issue.

 

[Pat Hartman]

I have used Wayne's service (Everything Access) to recover the code from an accdr and it worked well. I haven't had to use it to recover a corrupt database though.

 

[isladogs]

Thanks for pointing that out Colin I have taken note of what I came across in my post #9. As mentioned I haven't encountered this issue.

I wasn't getting at you. That particular company is well known for its particular style of marketing. Very few of their suggested solutions actually work. The articles are purely designed to get people to purchase their 'recovery' software

 

[Trevor G]

Colin I hadn't taken it in the wrong way. Sincerely I appreciated that you added your thoughts. We all learn from each other.

Best regards

Trevor

 

[arnelgp]

I used Stellar Phoenix, it does recover most of the time.

 

[alan2013]

Update on this :
At about 5am (UK time) Fri 7th, I uploaded the corrupted file on the EA website, and received a reference number. I haven't yet heard from them. I guess that it'll now be Monday at the earliest before I learn anything more from them.

I notice that the "common corruption symptoms" listed on their site includes "The Microsoft Jet Database Engine could not find object Msys*/databases. Make sure the object exists and that you spell its name correctly and the path name correctly". Whether or not that means that they will be able to do anything for me in this instance remains to be seen.

Thanks for recommending EA, George, Colin, & Pat. arnelgp : Depending on the outcome with EA, I may take a look at SPh. Thanks.

 

[arnelgp]

Let me try to do it for you for Free.
There is no harm trying.

 

[alan2013]

Let me try to do it for you for Free.
There is no harm trying.

Thanks very much for the kind offer. I may take you up on that. Just for now, I'll await word...
Thanks again.

 

[alan2013]

Evening of Tu 11 Feb 25 : Awaiting contact from EA.