Create local database backup from MS Access database with tables that linked to SQL Server database
Published:
Browse All Articles > Create local database backup from MS Access database with tables that linked to SQL Server database
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.
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.
Method 1 was implemented in VB.net and it includes following steps:
1. Copy MS Access Source database to DB Backup database;
2. Extract List of all tables of the Source database;
3. Delete all linked tables from DB Backup;
4. Copy tables from Source database to DB Backup.
Method 2 was implemented in VB.net module that calls MS Access macro:
1. Open existing database in VB.Net
2. Execute MS Access Database Backup macro from VB.NET
Steps in MS Access Macro:
1. Create new MS Access database as shell for DB Backup.
2. ImportToolBarPreview – I found this code on Internet and could provide code by request.
3. Transfer tables with names that do not begin with “MSys” or “~” .
Perform transfer with SELECT-INSERT structure.
4. Transfer Queries;
5. Transfer Reports;
6. Transfer Forms;
7. Transfer Macro;
8. Transfer Modules.
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.
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.
"C:\DiectoryofMSAccess\Msaccess.exe" "C:\DirectoryofYoursDB\DBName.mdb" /x macroname
There is no detail code provided for steps that do not play significant role in backup process such as WriteTextFile or WriteLogFile modules.
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.
Method 1 was implemented in VB.net and it includes following steps:
1. Copy MS Access Source database to DB Backup database;
2. Extract List of all tables of the Source database;
3. Delete all linked tables from DB Backup;
4. Copy tables from Source database to 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 Function2. 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 Sub3. 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 Function4. 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 FunctionMethod 2 was implemented in VB.net module that calls MS Access macro:
1. Open existing database in VB.Net
2. Execute MS Access Database Backup macro from VB.NET
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 FunctionSteps in MS Access Macro:
1. Create new MS Access database as shell for DB Backup.
2. ImportToolBarPreview – I found this code on Internet and could provide code by request.
3. Transfer tables with names that do not begin with “MSys” or “~” .
Perform transfer with SELECT-INSERT structure.
4. Transfer Queries;
5. Transfer Reports;
6. Transfer Forms;
7. Transfer Macro;
8. Transfer Modules.
'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 FunctionPublic 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 SubMethod 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.
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.
"C:\DiectoryofMSAccess\Msa
There is no detail code provided for steps that do not play significant role in backup process such as WriteTextFile or WriteLogFile modules.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (3)
Commented:
1. In Access, create a series of CreateTable queries. Simply individual SELECT ALL queries for each of your tables. When you ask for a CreateTable query in the Access query designer, you are offered the option to create the table in another .mdb, your backup. The path to that backup.mdb is saved in the query along with the SQL command.
2. Create an Access macro that runs all the queries in sequence.
3. All you have to do when you need to backup is to provide an empty .mdb and run the macro.
Author
Commented:Commented:
The Method 1 code is exactly what I need. However I am having trouble implementing it in VB.net using Visual Studio 2010. Are you still available for help since this post was done in 2010?
Thanks,
Cad Coder