<

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

Published on
14,798 Points
8,698 Views
1 Endorsement
Last Modified:
Approved
Community Pick
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
Comment
Author:DDB
3 Comments
LVL 40

Expert Comment

by:Jacques Bourgeois (James Burger)
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.
0
LVL 3

Author Comment

by:DDB
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.
0

Expert Comment

by:Cad Coder
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
0

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Join & Write a Comment

Learn the basics of Skype For Business in office 365
There are many cases found where ScanPST.exe fails to repair corrupt Outlook PST File. When user tries to repair PST using Inbox Repair tool and it throws below error: •      Inbox Repair tool does not recognize the file •      ScanPST.exe hangs in betwee…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month