The given MS Access 97 database has data that linked to SQL Server database’s tables and views. My task was to create the monthly backup of data in MS Access database with local tables (no links to SQL Server) and all functions of MS Access database should stay in place including Reports, Queries, Macros, Modules, Specification, and Tool Bars. If you try just copy the database to backup it creates the exact clone of the existing database with linked tables. I needed to have local tables without any link to SQL server database. The created backup process runs on Win2K server as a batch.
给定的MS Access 97数据库具有链接到SQL Server数据库的表和视图的数据。 我的任务是使用本地表（没有指向SQL Server的链接）在MS Access数据库中创建数据的每月备份，并且MS Access数据库的所有功能应保留在原位，包括报表，查询，宏，模块，规范和工具栏。 如果尝试仅复制数据库以进行备份，它将使用链接表创建现有数据库的精确克隆。 我需要没有任何指向SQL Server数据库的链接的本地表。 创建的备份过程将在批处理中在Win2K服务器上运行。
My original approach was written in VB.NET and it worked smoothly and fast until our server was moved to another environment with more strict security policy. The process failed to copy data from linked tables to local tables. I had to rewrite the process by moving a lot of code from VB.NET batch to MS Access module to avoid problem with data copy.
我最初的方法是用VB.NET编写的，并且工作流畅，快速，直到我们的服务器移至具有更严格安全策略的另一个环境。 该过程无法将数据从链接表复制到本地表。 我不得不通过将大量代码从VB.NET批处理移至MS Access模块来重写过程，以避免数据复制出现问题。
Method 1 was implemented in VB.net and it includes following steps:
方法1是在VB.net中实现的，它包括以下步骤：
1. Copy MS Access Source database to DB Backup database;
1.将MS Access Source数据库复制到DB Backup数据库；
2. Extract List of all tables of the Source database;
2.提取源数据库所有表的列表；
3. Delete all linked tables from DB Backup;
3.从数据库备份中删除所有链接的表；
4. Copy tables from Source database to DB Backup.
4.将表从源数据库复制到数据库备份。
1.将MS Access Source数据库复制到DB Backup数据库 (1. Copy MS Access Source database to DB Backup database)
Option Strict Off
Option Explicit On
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports ADODB
Imports VB = Microsoft.VisualBasic
Imports System.IO
Public Function BackupDatabase(ByVal sErrLogFile As String) As Boolean
On Error GoTo ErrorHandler
Public gsDBTables as String
Dim oAccess As Access.Application
Dim sDate As String
Dim sDBBackup As String
sDBBackup= “[name of your backup database]”
'Check if error file exist then delete it to catch currect process errors
If Dir(sErrLogFile) <> "" Then
Kill(sErrLogFile)
End If
‘ Replace names in [] with your DB Names: Source and Target DB
'create copy of your current database that will serve as your DB backup
' this copy will sill have linked tables
File.Copy([SourceDB], sDBBackup, True))
'List all tables in your current database. This list wil be used to copy table's data. You also could have exception tables list
' if necessary and filter out tables that you don't neeed to backup
ListAllTables([SourceDB])
' Delete linked tables from backup database
DeleteAllTables(sDBBackup)
'Now you can Copy data to DB Backup by extracting data from current database and inserting data
' in corresponding tables of the DB Bakup database
CopyTables([SourceDB], sDBBackup)
Exit Function
ErrorHandler:
WriteLogFile(Err.Number & ": " & Err.Description, sErrLogFile)
Resume Next
End Function
2.提取源数据库所有表的列表 (2. Extract List of all tables of the Source database)
Public Sub ListAllTables(ByVal sDB As String) 'Pass the database with ‘the full path in as a string
On Error GoTo ErrorHandler
' define ADO connection and necessary components that will be used
Dim Con As ADODB.Connection
Dim Cat As New ADOX.Catalog
Dim Tbl As New ADOX.Table
Dim ShowTable As Boolean
Dim Rec As New ADODB.Recordset
Dim sExcludeObjs As String = ""
'instantiate the connection
Con = New Connection
'client side cursor
Con.CursorLocation = CursorLocationEnum.adUseClient
'Open the database !!!3.51 did not work properly
Con.Open("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & gDataBase _
& ";")
'set the catalog's connection to the one just made
Cat.ActiveConnection = Con
ReDim gsDBTables(0)
glCounter = 0
'instantiate the adoTable variable and begin looping through the tables
' filter out system tables and any "~" tables that could be created by MS Access
'keep only tables name that you nned. In this case process list all application tables
For Each Tbl In Cat.Tables
Select Case Trim$(UCase(Tbl.Type))
Case "TABLE", "ACCESS TABLE", "PASS-THROUGH" ' Data tables
If Mid(Tbl.Name, 1, 4) <> "MSys" _
Or Left(Tbl.Name, 1) <> "~" Then
gsDBTables(glCounter) = Tbl.Name
ReDim Preserve gsDBTables(UBound(gsDBTables) + 1)
glCounter = glCounter + 1
End If
'keep it for future references. Current process does not use system tables or Views
Case "SYSTEM TABLE" ' System table - hide
ShowTable = False
Case "VIEW" ' Query table - hide
ShowTable = False
Case Else ' Unknown table - show
ShowTable = False
End Select
Next
'close your database connection
Con.Close()
'destroy the catalog object
Cat = Nothing
'destroy the database object
Con = Nothing
Exit Sub
ErrorHandler:
WriteLogFile(Err.Description, clsUE.LogDir & "DBBackup.log")
Resume Next
End Sub
3.从数据库备份中删除所有链接的表 (3. Delete all linked tables from DB Backup)
Public Function DeleteAllTables(ByVal sDBBackup As String) As Boolean
On Error GoTo ErrorHandler
Dim sSql As String
Dim lInx As Long
' open connection to DB Backup using OLEDB Jet
Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & sDbBackup)
AccessConn.Open()
'Loop thru tables and delete all linked tables
For lInx = 0 To glCounter - 1
sSql = "Drop table [" & gsDBTables(lInx) & "]"
Dim AccessCommand As New System.Data.OleDb.OleDbCommand(sSql, _ AccessConn)
AccessCommand.ExecuteNonQuery()
Next lInx
'Close and destroy connection
AccessConn.Close()
AccessConn = Nothing
Exit Function
ErrorHandler:
'you can create Error Log file for your batch to capture errors
WriteLogFile(Err.Description & " Table:" & gsDBTables(lInx), _ "DBBackup.log")
Resume Next
End Function
4.将表从源数据库复制到数据库备份 (4. Copy tables from Source database to DB Backup)
Public Function CopyTables(ByVal sFromDB As String, ByVal sToDb _
As String) As Boolean
On Error GoTo ErrorHandler
Dim sSql As String
Dim lInx As Long
Dim sTableName(20) As String
Dim i As Integer = 0
Dim oAccess As Access.Application
'Start Access and open the database.
oAccess = CreateObject("Access.Application")
oAccess.Visible = True
oAccess.OpenCurrentDatabase(sToDb, False)
oAccess.CloseCurrentDatabase()
iSecondTryFlag = 0
'open connection with ODBC JET
Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & sFromDB)
AccessConn.Open()
'extract data from current database and load data into DB Backup tables
'Generate string with command then execute this command.
For lInx = 0 To glCounter - 1
‘this command failed after we moved to another enviroment set
Dim AccessCommand As New System.Data.OleDb.OleDbCommand _
("SELECT * INTO [MS Access;DATABASE=" & sToDb & ";].[" & gsDBTables(lInx) & "] FROM [" _
& gsDBTables(lInx) & "]'", AccessConn)
AccessCommand.ExecuteNonQuery()
Next lInx
'destry connection
AccessConn.Close()
AccessConn = Nothing
Exit Function
ErrorHandler:
WriteLogFile(Err.Description , "DBBackup.log")
Resume Next
End Function
Method 2 was implemented in VB.net module that calls MS Access macro:
方法2在调用MS Access宏的VB.net模块中实现：
1. Open existing database in VB.Net
1.在VB.Net中打开现有数据库
2. Execute MS Access Database Backup macro from VB.NET
2.从VB.NET执行MS Access数据库备份宏
Public Function BackupDatabase(ByVal sErrLogFile As String) As Boolean
On Error GoTo ErrorHandler
Dim oAccess As New Access.Application 'Access.Application
Dim sSourceDB As String
If Dir(sErrLogFile) <> "" Then
Kill(sErrLogFile)
End If
' first open the currect database
oAccess.OpenCurrentDatabase(sSourceDB, False)
'execute database macro that calls DB Backup module
oAccess.DoCmd.RunMacro("mMonthlyBackup")
'after backup completion destroy connection
oAccess.CloseCurrentDatabase()
oAccess.Quit()
Exit Function
ErrorHandler:
WriteLogFile(Err.Description, sErrLogFile)
Resume Next
End Function
Steps in MS Access Macro:
MS Access宏中的步骤：
1. Create new MS Access database as shell for DB Backup.
1.创建新的MS Access数据库作为Shell for DB Backup。
2. ImportToolBarPreview – I found this code on Internet and could provide code by request.
2. ImportToolBarPreview –我在Internet上找到了此代码，可以根据要求提供代码。
3. Transfer tables with names that do not begin with “MSys” or “~” .
3.传输名称不以“ MSys”或“〜”开头的表。
Perform transfer with SELECT-INSERT structure.
使用SELECT-INSERT结构执行传输。
4. Transfer Queries;
4.转移查询；
5. Transfer Reports;
5.转移报告；
6. Transfer Forms;
6.转让表格；
7. Transfer Macro;
7.传输宏；
8. Transfer Modules.
8.传输模块。
'MS Access mMonthlyBackup macro calls Monthly backup module
' you need to create macro that will execute your backup module
Function MonthlyBackup() As Boolean
On Error GoTo ErrorHandler:
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim frm As Access.Form
Dim rpt As Access.Report
Dim qry As DAO.QueryDef
Dim mdl As Access.Module
Dim d As Document
Dim c As Container
Dim sDate As String
Dim sBackupDB As String
Dim sSql As String
Dim sObjName As String
WriteTextFile sMonthlyLog, "Started at :" & Time
'generate name for Monthly backup
sDate = Format(Date, "mm-yyyy")
sBackupDB = sMonthlyDB & sDate & ".mdb"
'Create MS Access new database that will be used for backup
CreateDatabase (sBackupDB)
'since I use this code as batch I need to supresss warnings
DoCmd.SetWarnings False
'Import tool bars that being used in your application, I used code that I found on internet.
ImportToolBarPreview sBackupDB
Set db = CurrentDb()
'create all tables in db Backup by extracting data from source; do not include system tables and any ~ tables
For Each tdf In db.TableDefs
If Mid(tdf.Name, 1, 4) <> "MSys" Then
sObjName = tdf.Name
If Left(sObjName, 1) <> "~" Then
sSql = "SELECT * INTO [" & sObjName & "] IN '" & sBackupDB & "' FROM [" & sObjName & "];"
DoCmd.RunSQL sSql
End If
End If
Next tdf
' export queries
For Each qry In db.QueryDefs
sObjName = qry.Name
DoCmd.TransferDatabase acExport, "Microsoft Access", _
sBackupDB, acQuery, qry.Name, qry.Name
Next qry
'export forms
Set c = db.Containers("Forms")
For Each d In c.Documents
sObjName = d.Name
DoCmd.TransferDatabase acExport, "Microsoft Access", _
sBackupDB, acForm, d.Name, d.Name
Next d
'export reports
Set c = db.Containers("Reports")
For Each d In c.Documents
sObjName = d.Name
DoCmd.TransferDatabase acExport, "Microsoft Access", _
sBackupDB, acReport, d.Name, d.Name
Next d
'export macros
Set c = db.Containers("Scripts")
For Each d In c.Documents
sObjName = d.Name
DoCmd.TransferDatabase acExport, "Microsoft Access", _
sBackupDB, acMacro, d.Name, d.Name
Next d
'export modules
Set c = db.Containers("Modules")
For Each d In c.Documents
sObjName = d.Name
DoCmd.TransferDatabase acExport, "Microsoft Access", _
sBackupDB, acModule, d.Name, d.Name
Next d
'destoy object. After completion control will be return to VB.NET module
Set c = Nothing
db.CLOSE
Set db = Nothing
WriteTextFile sMonthlyLog, "Completed at :" & Time
DoCmd.SetWarnings True
Exit Function
ErrorHandler:
WriteTextFile sMonthlyLog, Err.Description & " " & sObjName
Err.Clear
Resume Next
End Function
Public Sub CreateDatabase(sDBName As String)
Dim ws As Workspace
Dim db As Database
'Get default Workspace
Set ws = DBEngine.Workspaces(0)
'Make sure there isn't already a file with the name of the new database
If Dir(sDBName) <> "" Then Kill sDBName
'Create a new mdb file
Set db = ws.CreateDatabase(sDBName, dbLangGeneral)
db.CLOSE
Set db = Nothing
End Sub
Method 1 is much faster thAn Method 2. The statistic shows that the database backup with database size of 500,000 KB runs 7-15 min by method 1 and 40-45 min by method 2.
方法1比方法2快得多。统计数据显示，数据库大小为500,000 KB的数据库备份在方法1中运行7-15分钟，在方法2中运行40-45分钟。
In order to improve the performance of the Method 2 you can execute all steps from Method 1 and then execute only copy tables by MS Access module. I just want to show how to perform backup by executing all steps in MS Access. You can initiate MS Access Macro from Windows scheduler and not to use VB.Net code.
为了提高方法2的性能，您可以执行方法1中的所有步骤，然后通过MS Access模块仅执行复制表。 我只想展示如何通过执行MS Access中的所有步骤来执行备份。 您可以从Windows调度程序启动MS Access Macro，而不使用VB.Net代码。
"C:\DiectoryofMSAccess\Msa
“ C：\ MSAccess \ Msa的Diectory ccess.exe” “ C：\ YoursDB目录\ DBN ame.mdb” / x宏名
There is no detail code provided for steps that do not play significant role in backup process such as WriteTextFile or WriteLogFile modules.
没有为在备份过程中不起重要作用的步骤（如WriteTextFile或WriteLogFile模块）提供详细代码。
扫一扫
930
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
