Community Pick: Many members of our community have endorsed this article.

Create local database backup from  MS Access database with tables that linked to SQL Server database

DDB
Published:
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.

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

Open in new window


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

Open in new window


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

Open in new window


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

Open in new window


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


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

Open in new window


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.

'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

Open in new window


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

Open in new window


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.
1
9,774 Views

Comments (3)

Top Expert 2015

Commented:
A lot of code, when you could do it with no code at all.

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.
DDB

Author

Commented:
You definitely could create individual statement for each table if there no many of them. Besides that you need to take in consideration that number of tables could be increased or decreased at any time as well as name of tables could change and in this case you need manually maintain your static script.  For me it was better to use the code that does a job no matter what kind of changes applied to the database.
Cad CoderDeveloper

Commented:
To DDB,
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

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.