2

Background: I have a process that links Quickbooks to Ms Access. If a button is pressed, some information will be queried from Quickbooks and then updates Ms Access. If the power goes out, or if the user forces Ms Access to close during the sync process, it can cause some of the information to be corrupted.

Goal: I want to have a button on a form that users can press and it'll save the current database to a predefined location with the date and time attached to the filename.

I keep reading how it's possible to backup other closed databases (using FileCopy), but you need a hacky-workaround solution to do it on an open database, which can lead to data corruption. I'm not entirely convinced since the user can use "Save As" at any time.

Is there a way to backup a currently open Ms Access database, or something that will fulfill my needs?

Jaitnium
  • 621
  • 1
  • 13
  • 27

3 Answers3

3

The users "save as" does a different thing than just copying a file, it actually creates a new database, and exports everything to it. You can do the same if you wish (if there are no locked records), but it does require some coding.

The "backup database" is unavailable from the save as menu if the file is opened by other users (and closes all open objects when used).

You can, of course, create a new file, and then iterate through all tables, queries, forms, reports, macros and modules to copy them, and then iterate through all relationships to add them to the copy. Then you can copy all database properties to the new database. But that requires some work.

See the following code to create a backup that ignores relationships and database properties

Public Sub BackupDatabase(newLocation As String)
    'Make sure there isn't already a file with the name of the new database
    If Dir(newLocation) <> "" Then Kill newLocation
    'Create a new database using the default workspace
    'dbVersion30 = Jet 3, dbVersion40 = Jet4, dbVersion120 = 2007 accdb, dbVersion150 = 2013 accdb
    DBEngine.Workspaces(0).CreateDatabase newLocation, dbLangGeneral, Option:=dbVersion150

    'Iterate through common object collections, put the files in
    Dim iterator As Variant
    For Each iterator In CurrentDb.TableDefs
        If Not iterator.Name Like "MSys*" Then
            DoCmd.TransferDatabase acExport, "Microsoft Access", newLocation, acTable, iterator.Name, iterator.Name
        End If
    Next iterator
    For Each iterator In CurrentDb.QueryDefs
        If Not iterator.Name Like "~sq_*" Then
            DoCmd.TransferDatabase acExport, "Microsoft Access", newLocation, acQuery, iterator.Name, iterator.Name
        End If
    Next iterator
    For Each iterator In CurrentProject.AllForms
         DoCmd.TransferDatabase acExport, "Microsoft Access", newLocation, acForm, iterator.Name, iterator.Name
    Next iterator
    For Each iterator In CurrentProject.AllReports
        DoCmd.TransferDatabase acExport, "Microsoft Access", newLocation, acReport, iterator.Name, iterator.Name
    Next iterator
    For Each iterator In CurrentProject.AllMacros
        DoCmd.TransferDatabase acExport, "Microsoft Access", newLocation, acMacro, iterator.Name, iterator.Name
    Next iterator
    For Each iterator In CurrentProject.AllModules
        DoCmd.TransferDatabase acExport, "Microsoft Access", newLocation, acModule, iterator.Name, iterator.Name
    Next iterator
End Sub

Note that, depending on your security settings, you might get a lot of security popups.

Erik A
  • 31,639
  • 12
  • 42
  • 67
  • I'm looking for a way to backup my tables and their information. Since your solutions does that, I'm very interested. – Jaitnium Aug 16 '17 at 16:30
  • See the current edit. Never had problems with it, but haven't done extensive testing – Erik A Aug 16 '17 at 16:51
  • I have several linked tables that I'd like to avoid copying because they require Quickbooks to be open. Should I just check iterator.Name for the tables' names to skip over those tables? – Jaitnium Aug 16 '17 at 17:29
  • You don't need to check their names, a simple `If iterator.Connect <> "" Then` will exclude them. There also is an option to store the data from the linked table in an unlinked table in the backup. – Erik A Aug 16 '17 at 17:31
  • I made this modification to the first conditional: `If Not iterator.Name Like "MSys*" And iterator.Connect <> "" Then` but I'm getting error 3709: "The search key was not found in any record." – Jaitnium Aug 16 '17 at 17:41
  • I get an error on the "DoCmd.TransferDatabase" line right after it: `DoCmd.TransferDatabase acExport, "Microsoft Access", newLocation, acQuery, iterator.Name, iterator.Name` – Jaitnium Aug 16 '17 at 17:49
  • Looks like an error with a specific table. You can `Debug.Print iterator.Name` to find out which one. – Erik A Aug 16 '17 at 17:52
  • Figured it out. It was still accessing the linked tables because `iterator.Connect <> ""` should be `iterator.Connect = ""`. Thank you for your help. – Jaitnium Aug 16 '17 at 17:56
1

You can use the following line of code, This is assuming you have a split database:

Public Sub CompactDB()
dim strFrom as string
dim strTo as string

strFrom = "C:\Your Database Location Including File Name and Extension"
strTo = "C:\Your new Database backup location File Name and Extension"

DBEngine.CompactDatabase strFrom, strTo

End Sub

NOTE This will not compact your current backend (strFrom), This makes a copy of back end located at strFrom to the new location (strTo).

Just have a button click or event from another from call this sub.

But, the way I handle this is make a table that stores 2 fields. Field 1 is named "DestinationFrom", Field 2 is named "DestinationTo". Then I store records like below:

DestinationFrom = C:\Destination of current back end

DestinationTo = C:\Back Up destination

Then use the following code:

Public sub CompactDB()
dim rst as dao.recordset
dim strSQL as string
dim strLocation as string
Dim strDestination as string

strsql = "SELECT * " & _
         "FROM DestinationTable;"
set rst = currentdb.openrecordset(strsql)
strlocation = rst![DestinationFrom]
strdestination = rst![DestinationTo]
rst.close
set rst = nothing

DBEngine.CompactDatabase rst![DestinationFrom] , rst![DestinationTo]

if not rst is nothing then
rst.close
set rst = nothing
end if
End Sub

This way, if my code ever fails cause a folder was deleted or moved, I can change the string location in the field on the table without needing to change anything that was hard coded and needing to release a new copy. Very useful when allowing multiple users in a split database

Chris
  • 254
  • 1
  • 5
  • this looked promising. I tried to use it to backup the current backend to a new location using this line of code: DBEngine.CompactDatabase BackendPath("tbl"), BackendPath("tbl") & "_backup" However, when that code runs, I get a Run-time error '3704': You attempted to open a database that is already opened by user 'Admin' on machine {Computer Name}. Try again when the databse is available. I really wish this worked for me, though. – DRC Mar 01 '18 at 16:48
  • BAH! my mistake, I have edited my second code to fix the issue. The problem is when the rst opened a recordset to the strSQL query. it created a connection to the database. Since there is a current connection, the database was unable to compact and repair to the destination location. – Chris Mar 02 '18 at 16:34
0

You can try using FileSystemObject like this:

    'strFrom = Application.CurrentProject.FullName
    'strTo = "C:\FolderName\NewFileName.accdb"

Public Sub copyFile(strFrom As String, strTo As String)
    Dim fso As FileSystemObject
    Set fso = New FileSystemObject

    fso.copyFile strFrom, strTo

    Set fso = Nothing

End Sub
landrew
  • 56
  • 4