MS ACCESS 2010 file > 2GB now corrupted (1 Viewer)

SpentGeezer · May 8, 2017

Hi,

I have an access database which is used to run a procedure and extract and manipulate large datasets for a linked SQL Server table and spit out excel files for my work. It runs overnight, and last run bloated to a file size of 2,097,063kb. Now I cannot open to compact and repair as I get a corrupted file warning. I can't compact and repair from a separate database, or import into a new database as I get the corrupted error message. Is there any way to salvage this database? It has quite a lot of VBA code that was not written the last time the database was backed up :

eek:

isladogs · May 8, 2017

1. Can you open it if you hold down the shift key to bypass any startup code

2. Can 64-bit Access handle files larger than the 2GB limit for 32-bit?
I know that's the case for Excel but not sure about Access?

3. Could you import tables one at a time into a new database & compact as necessary to reduce file size

AND OF COURSE if you solve it .... create an automatic daily backup system for the front-end as well as for the backend data

Also, see this link:
https://answers.microsoft.com/en-us/msoffice/forum/msoffice_access-mso_winother/db-went-over-2gb-and-gets-corrupted-when-going/bb934b53-11eb-4372-9791-88fbfe20b462

SpentGeezer · May 8, 2017

Thanks for the response ridders

1. Can you open it if you hold down the shift key to bypass any startup code
No, says database is corrupt or not recognized

2. Can 64-bit Access handle files larger than the 2GB limit for 32-bit?
I know that's the case for Excel but not sure about Access?
Yes, wish I had 64 bit! Ha ha

3. Could you import tables one at a time into a new database & compact as necessary to reduce file size
No, says database is corrupt or not recognized

AND OF COURSE if you solve it .... create an automatic daily backup system for the front-end as well as for the backend data
Yep, I failed this well and truelly

SpentGeezer · May 8, 2017

Well I ran this code and compacted the database to 120,000kb but the forms and all VBA modules are gone.. any tips?

Code:

Private Sub Command0_Click()


On Error GoTo errHandler

    Dim oFSO As Object
    Dim strDestination As String
    Dim strSource As String
    
    'Get the source of your back end
    strSource = "C:\google\prob.accdb"

 
    'Compact the new file, ...
    Name strSource As strSource & ".cpk"
    DBEngine.CompactDatabase strSource & ".cpk", strSource
    Kill strSource & ".cpk"
    
    'Notify users
    MsgBox "Backup file '" & strDestination & "' has been created.", _
            vbInformation, "Backup Completed!"
    
errExit:
    Exit Sub
    
errHandler:
    MsgBox Err.Number & ": " & Err.Description
    Resume errExit

isladogs · May 8, 2017

If I were you I'd take that sword of yours & give up dancing ...

PLEASE tell me you made a backup BEFORE running that code

AFAIK all the VBA code is now gone forever so you may as well bin that copy.

If you still have another copy, suggest you pay for someone to recover it

CJ_London · May 8, 2017

Can 64-bit Access handle files larger than the 2GB limit for 32-bit?

To clarify: 2Gg limit remains the limit for a 64bit Access file, but 64bit Excel files can be larger - although the general advice is if the excel file is that big, it should be in a database anyway

Agree with Ridders, sounds like your file is beyond repair, you need to revert to a backup. Moving forward, recommend you split your db - have the code in one file and the tables in the other. That way if the table file goes over limit you should still be able to compact and repair it and your code file will be safe.

Or have your 'source tables' from sql server in one file and your 'output' tables in another file or files.

The_Doc_Man · May 8, 2017

If I recall correctly, that sword-and-dance quote is from Robert A Heinlein and - I think - Glory Road.

The first lesson I learned was to always keep a backup before you tried anything. The idea is that when stuff fails, make another copy from the backup and try something else. Do you have ANY backup of that database, even if it is so old that it hadn't blown out the sizing limit yet? Because from the backup copy, you MIGHT be able to recover at least some if not all of the code. After all, if it is an old system, how much code have you changed lately?

I also concur with the others that this database should be split because (a) it keeps the queries, forms, reports, macros, and modules in a separate and smaller file; and (b) each file has its own 2 GB limit. You get some space back, which will give you breathing room in terms of getting some archiving done.

SpentGeezer · May 9, 2017

Guys, original post states that I had a backup, just lost some VBA code written since the last backup. Ridders, I'm gunna keep dancing and coninue my sword training. I've put it all back together, and added a couple of tables and queries back from the database produced by the code I executed above. All good. Thanks for your help.

The Doc Man, the quote is from Confucious..but may have been used in Glory Road as well.

Lightwave · May 9, 2017

Spent

Glad it looks like it has turned out well.

Here's some advice on a method of vastly reducing the time for MS Access databases backup using VBS scripts there are many different ways - this semi-automates the process . I very strongly recommend anyone with valuable databases to study and learn a technique that they are confident in using. You absolutely MUST have a technique to do this if you are building business critical applications. Stopping your work routine to spend 15 minutes to backup databases can get unwieldy if you are wanting to backup many many times.

Using Visual Basic Script to create rolling MS Access Database backups

Open up a text editor
Copy the code in
Edit the attributes to match the database you wish to back up.
Save the file
Edit the suffix of the file to vbs
Place the file somewhere eg on your desktop and double click it.

This particular script creates a new database every day numbered according the numerical day of the week. It will overwrite if you are perfectly a week apart. It will also create a monthly copy. Therefore if you were to run this everyday of a year you would have a total of 7 week day copies and 12 month copies.

When you understand it you can alter the code to give you minutes and hourly and yearly copies. Minutes and hourly copies can be useful if you are really want to throw the development about the place on really big and important databases and you may wish to roll back 15 minutes or an hour say rather than a whole day or god forbid a month! (both in terms of the VB and the data). I recently completed a project where I was transferring data across between systems and I was writing all the SQL in an Access Database. The SQL was horrendously complicated and I would backup after I completed each complicated SQL because I just didn't want to loose even an hour. By having the VBS script on the desktop - I would just click and have parameters to allow a new save every minute. I would therefore have ten or twelve backups every day.

I should add that this can be useful to backup any file not just accdbs/mdbs

Apologies if you already do something like this - not clear from your note.

If you call the vbs from Windows Task Scheduler thats it fully automated.

isladogs · May 9, 2017

Following on from Lightwave's very helpful post, here is a function to copy the Access front end from within Access as an alternative.

It creates a copy of the database in the same folder & adds the current date & time. The copy is then compacted.

Code:

Public Function CopyCurrentDatabase()

On Error GoTo Err_Handler

'creates a copy of the current db (frontend) to same folder with date/time suffix
'e.g. SDA_20170423014248.accdb

    Dim fso As Object
    Dim strOldPath As String, strNewPath As String, strTempPath As String, strFileSize As String
    Dim newlength As Long
    
    Set fso = CreateObject("Scripting.FileSystemObject")
        
    strOldPath = CurrentDb.Name
    strTempPath = Left(CurrentDb.Name, InStr(CurrentDb.Name, ".") - 1) & _
        "_TEMP" & Mid(CurrentDb.Name, InStr(CurrentDb.Name, "."))
            
    strNewPath = Left(CurrentDb.Name, InStr(CurrentDb.Name, ".") - 1) & _
         "_" & Format(Now, "yyyymmddhhnnss") & Mid(CurrentDb.Name, InStr(CurrentDb.Name, "."))
            
    
    If MsgBox("This routine is used to make a backup copy of the Access front end (FE) database.           " & vbNewLine & vbNewline & _
        "The backup will be saved to the same folder with current date/time suffix                   " & vbNewLine & _
            vbTab & "e.g. " & strNewPath & "                            " & vbNewLine & vbNewLine & _
        "This can be used for recovery in case of problems    " & vbNewLine & vbNewLine & _
        "Create a backup now?", _
            vbExclamation + vbYesNo, "Copy the Access FE database?") = vbYes Then       
  
            'copy database to a temp file
            fso.CopyFile strOldPath, strTempPath
            Set fso = Nothing
            
            strNewPath = Left(CurrentDb.Name, InStr(CurrentDb.Name, ".") - 1) & _
                "_" & Format(Now, "yyyymmddhhnnss") & Mid(CurrentDb.Name, InStr(CurrentDb.Name, "."))
            
            'compact the temp file
            DBEngine.CompactDatabase strTempPath, strNewPath
            
            'delete the tempfile
            Kill strTempPath
                
            DoEvents
            
            'get size of backup
            newlength = FileLen(strNewPath) 'in bytes
             
            'setup string to display file size
            If FileLen(strNewPath) < 1024 Then  'less than 1KB
               strFileSize = newlength & " bytes"
            ElseIf FileLen(strNewPath) < 1024 ^ 2 Then  'less than 1MB
               strFileSize = Round((newlength / 1024), 0) & " KB"
            ElseIf newlength < 1024 ^ 3 Then 'less than 1GB
               strFileSize = Round((newlength / 1024), 0) & " KB   (" & Round((newlength / 1024 ^ 2), 1) & " MB)"
            Else 'more than 1GB
                strFileSize = Round((newlength / 1024), 0) & " KB   (" & Round((newlength / 1024 ^ 3), 2) & " GB)"
            End If           
    
            MsgBox "The Access FE database has been successfully backed up.                " & vbNewLine & vbNewline & _
                "The backup file is called " & vbNewLine & _
                    vbTab & strNewPath & "                       " & vbNewLine & vbNewLine & _
                    "The file size is " & strFileSize, vbInformation, "Access FE Backup completed"

    End If
    
Exit_Handler:
    Exit Function
    
Err_Handler:
    Set fso = Nothing
    If Err <> 0 Then
      MsgBox "Error " & Err.Number & " in CopyCurrentDatabase procedure : " & _
          " - " & Err.Description, vbCritical, "Error copying database"
    End If
    Resume Exit_Handler
    
End Function

NOTE: This does NOT backup the linked Access / SQL datafile.
It goes without saying that similar procedures should be used to backup the datafile.
If anyone would like code for either of these, let me know & I'll post these as well

isladogs · May 9, 2017

Hi Spent

Glad to hear it all worked out for you.
I knew you had a backup but you said it wasn't recent ....

As Confucius may have said:
After all that I'm sure you'll be doing backups very frequently from now on !

SpentGeezer · May 10, 2017

Thanks for the backup tips guys!
LOL @ ridders Confucius quote

MS ACCESS 2010 file > 2GB now corrupted (1 Viewer)

SpentGeezer

Pure Noobism

isladogs

MVP / VIP

SpentGeezer

Pure Noobism

SpentGeezer

Pure Noobism

isladogs

MVP / VIP

CJ_London

Super Moderator

The_Doc_Man

Immoderate Moderator

SpentGeezer

Pure Noobism

Lightwave

Ad astra

isladogs

MVP / VIP

isladogs

MVP / VIP

SpentGeezer

Pure Noobism

Similar threads

Users who are viewing this thread