Ms Access数据库在使用vba打开时是否可以创建其自身的备份? [英] Can an Ms Access database create a backup of itself while it's open using vba?

问题描述

背景:我有一个将Quickbook与Ms Access链接的过程.如果按下按钮，将从Quickbooks中查询某些信息，然后更新Access女士.如果断电，或者在同步过程中用户强迫Access Ms关闭，则可能会导致某些信息损坏.

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.

我一直在阅读如何使用FileCopy来备份其他 closed 数据库的方法，但是您需要一个可解决问题的解决方案在开放式数据库上进行备份，这可能会导致数据损坏.我并不完全相信，因为用户可以随时使用另存为".

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.

有没有办法备份当前打开的Ms Access数据库，或者可以满足我需要的东西?

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

推荐答案

用户另存为"所做的工作与复制文件不同，它实际上创建了一个新数据库，并将所有内容导出到该数据库.您可以根据需要执行相同的操作(如果没有锁定的记录)，但是确实需要一些编码.

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.

这篇关于Ms Access数据库在使用vba打开时是否可以创建其自身的备份?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！