Open another database and copy all tables into back-up

[jaryszek]

Hello,

I am trying to do back-up macro for my database.

Every day automatically my access runtime database (Database1) runs in order to do macro (Function is for autoexec module to run automatically on database open)

Function DesignView()
    
    Dim sFile As String
    Dim oDB As dao.Database
    Dim oTD As TableDef
    Dim OtherDB As dao.Database
    Dim sPath As String
    
//Curent date for back up database
    sFile = CurrentProject.Path & "\Archiwum" & "\" & Format(Date, "DD-MM-YYYY") & ".accdb"

//creating new database
    On Error Resume Next
    Set oDB = DBEngine.Workspaces(0).CreateDatabase(sFile,  
    dbLangGeneral)
    oDB.Close

//try to loop through another database abd copy all tables into created database in sFile
    Set OtherDB = OpenDatabase("C:\Users\ljar01\Desktop\Makro\Makro IBS\Raport Błędów\Aplikacja_Błędy_BE.accdb")

    DoCmd.Hourglass True
    
[B]For Each oTD In OtherDB.TableDefs
       If Left(oTD.Name, 4) <> "MSys" Then
            DoCmd.CopyObject sFile, , acTable, oTD.Name
       End If
Next oTD[/B]

    OtherDB.Close
    DoCmd.Hourglass False
    
    MsgBox "Done!"
    
End Function

Problem is within for each loop. My tables are found and command:

"DoCmd.CopyObject sFile, , acTable, oTD.Name" causes error:

"Access cant find object "tbl_Employee"".
I think that problem is connected with DoCmd because docmd is working within only current database (with macro).

I have tested it and if in my database1 there is a tbl_Employee - all is working fine. How to do DoCmd within another database?

How to solve the problem?

Best wishes,
Jacek Antek

 

[The_Doc_Man]

Usually this sort of thing, though possible, is very tricky. Whether you are doing backups based on running a macro using the command-line /x:macro method and based on using something like Windows Task Scheduler to trigger this, or whether it is triggered some other way within Access, you would probably do better to use an external script to just COPY the database using a command-line script. There are various scripting options available that could just as easily be triggered by Windows Task Scheduler.

The problem with doing this as you describe it makes me wonder... Are you doing this on a split database or a unified database? Because if you had a front-end / back-end split going on at the time, and if you had the BE set up with no locking or optimistic locking, there would be no lock interference and not other barriers. If this is a database trying to backup another database, look into using the File System Object to do wholesale file copies.

Further, if you had a BE split, it would be possible for you to just copy the BE to another place and have the copying script generate a date tag to rename the BE file. Doing it that way, you take a MONOLITHIC copy rather than a time-blurred copy of the BE file, which is where your tables would be located anyway. And you would NEVER wonder about what would be copied because EVERYTHING would be copied.

I don't want to squelch your learning experience, but an important thing to consider is that you have a computer with MANY tools in its tool kit. Sometimes you need to remember that if the only thing you use is a hammer, everything you use it on had better be a nail. Look into your Windows scripting capability as a way to do a quick, clean backup for you.

 

[jaryszek]

Hi The_Doc_Man.

Thank you for your post and help!
Yes i know this Task Scheduler method. And i can do it with Excel VBA code.
But i want to this via Access

File System Object? What do you mean? I want to have backup of my Back-End split database.

Further, if you had a BE split, it would be possible for you to just copy the BE to another place and have the copying script generate a date tag to rename the BE file. Doing it that way, you take a MONOLITHIC copy rather than a time-blurred copy of the BE file, which is where your tables would be located anyway. And you would NEVER wonder about what would be copied because EVERYTHING would be copied.

This is interesting for me. If I had 2 copies of my Back-End - 1 of them will be working as back-up? What about safety? If my whole Access split database will crash is it possible that also my 2 copies (linked with FE) will crash?

Returning to my question. Is it possible to do DoCmd function with reference to another database?

Best Wishes
Jacek Antek
(sorry for my late answer, I had a lot of other responsibilities)

 

[Gasman]

FWIW I use this to backup my database be it FE or BE.

Sub CreateBackup(Optional strDBType As String)
    Dim strDBpath As String, ext As String, tmp As String
    Dim strPath As String, strBackupPath As String, strDB As String
    
    'tmp = CurrentDb.Name    'or maybe this should be the name of your BE
    'strDBType = "FE"
    strDBpath = GetAccessBE_PathFilename("tblUser")
    strPath = Left(strDBpath, InStrRev(strDBpath, "\"))
    strBackupPath = strPath & "Backup\"
    
    'Will nor backup front and back end database
    If strDBType = "FE" Then
        strDBpath = CurrentDb.Name
    End If
    strDB = Right(strDBpath, Len(strDBpath) - InStrRev(strDBpath, "\"))
    
    With CreateObject("Scripting.FileSystemObject")
        'ext = "." & .GetExtensionName(tmp)
        tmp = strBackupPath & Format(Now(), "yyyymmdd_hhnnss") & "_" & strDB
        .CopyFile strDBpath, tmp
    End With
    MsgBox strDBType & " Database saved as " & tmp
End Sub

I then just call it using CreateBackup("FE") or CreateBackup("BE")

The sub uses

Function GetAccessBE_PathFilename(pTableName As String) As String
'strive4peace

   ' RETURN
   '  the file path and file name of the BE database
   '  "" if the table is not linked
   
   On Error GoTo Proc_Err
   
   Dim db As DAO.Database _
      , tdf As DAO.TableDef
   
   GetAccessBE_PathFilename = ""
   
   Set db = CurrentDb
   Set tdf = db.TableDefs(pTableName)
   
   If Len(tdf.Connect) = 0 Then
      GoTo Proc_Exit
   End If
   
   ' look at Connect string - Database Type is the first thing specified
   ' if the BE is Access
   If InStr(tdf.Connect, ";DATABASE=") <> 1 Then
      GoTo Proc_Exit
   End If
   
   GetAccessBE_PathFilename = Mid(tdf.Connect, 11)
    
Proc_Exit:
   On Error Resume Next
   Set tdf = Nothing
   Set db = Nothing
   Exit Function
  
Proc_Err:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   GetAccessBE_PathFilename"

   Resume Proc_Exit
   Resume
             
End Function

 

[The_Doc_Man]

My comments regarding File System Objects are merely that you can make a reference that gives you the ability to use the "{object}.CopyFile" method to copy a file from point A to point B with any name you want for the point B copy. Do a web search on Office File System Object and you will see some of the things you can do in terms of file system manipulation, searching, renaming, and other features. (Though simple VBA allows a rename using the "Name As" syntax.)

Gasman's post included a FileSystemObject reference.

With regard to backup files... This is just like typing a COPY command to the Windows Command Line, or using CTRL/C (copy) from a Windows Explorer window followed by a CTRL/V (paste) to some other location. The backup operation DOES NOT AFFECT the linkage from the FE to the BE. The original BE remains in place and is still linked to the FE as it always was.

My suggestion (and Gasman apparently also does this) is that you can take a quicker snapshot of your BE file if you do a file copy rather than moving tables one at a time. Your backup copy IS NOT ACTIVE. Only the original BE stays active. Making a backup that would be endangered by something done later from the FE wouldn't be such a good idea and we wouldn't offer that kind of suggestion.

Think about taking a picture of something with a very slow camera. If the wind is blowing or kids are in the frame, some things will be blurred. (Hell, when my very active grandson is around and I'm just using my eyes, he's still a blur sometimes...). Anyway, that blurring occurs because you are seeing different parts of the image originating from different times. The idea of copying tables one at a time, will cause the database equivalent and give you a blurry image of the contents of the file. Doing a wholesale file copy gives you the closest thing you can get to a "real" snapshot and minimize the blur. Not only that, but you expressed concern about having to create tables in the target file for your table-by-table copy. Doing it this way? One file copy and EVERYTHING is done.

I won't post code because Gasman's offering should do the trick nicely.

 

[jaryszek]

FWIW I use this to backup my database be it FE or BE.

Sub CreateBackup(Optional strDBType As String)
    Dim strDBpath As String, ext As String, tmp As String
    Dim strPath As String, strBackupPath As String, strDB As String
    
    'tmp = CurrentDb.Name    'or maybe this should be the name of your BE
    'strDBType = "FE"
    strDBpath = GetAccessBE_PathFilename("tblUser")
    strPath = Left(strDBpath, InStrRev(strDBpath, "\"))
    strBackupPath = strPath & "Backup\"
    
    'Will nor backup front and back end database
    If strDBType = "FE" Then
        strDBpath = CurrentDb.Name
    End If
    strDB = Right(strDBpath, Len(strDBpath) - InStrRev(strDBpath, "\"))
    
    With CreateObject("Scripting.FileSystemObject")
        'ext = "." & .GetExtensionName(tmp)
        tmp = strBackupPath & Format(Now(), "yyyymmdd_hhnnss") & "_" & strDB
        .CopyFile strDBpath, tmp
    End With
    MsgBox strDBType & " Database saved as " & tmp
End Sub

I then just call it using CreateBackup("FE") or CreateBackup("BE")

The sub uses

Function GetAccessBE_PathFilename(pTableName As String) As String
'strive4peace

   ' RETURN
   '  the file path and file name of the BE database
   '  "" if the table is not linked
   
   On Error GoTo Proc_Err
   
   Dim db As DAO.Database _
      , tdf As DAO.TableDef
   
   GetAccessBE_PathFilename = ""
   
   Set db = CurrentDb
   Set tdf = db.TableDefs(pTableName)
   
   If Len(tdf.Connect) = 0 Then
      GoTo Proc_Exit
   End If
   
   ' look at Connect string - Database Type is the first thing specified
   ' if the BE is Access
   If InStr(tdf.Connect, ";DATABASE=") <> 1 Then
      GoTo Proc_Exit
   End If
   
   GetAccessBE_PathFilename = Mid(tdf.Connect, 11)
    
Proc_Exit:
   On Error Resume Next
   Set tdf = Nothing
   Set db = Nothing
   Exit Function
  
Proc_Err:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   GetAccessBE_PathFilename"

   Resume Proc_Exit
   Resume
             
End Function

Thank you Gasman, i will try your code and let you know if it is owrking for me.

Thank you The_Doc_Man. Yoour explanation hit into my heart and i know which way will the best for me. Best wishes for you and your grandson!

Jacek Antek

 

[static]

To copy data from one database to another you can use SELECT INTO.

And instead of skipping linked tables, you could use them to specify which tables you want to back up from multiple source databases into a single target file.

Public Function RunBackup()

    DB_CopyTo = "C:\backup_" & Format(Now, "yymmdd_hhnn") & ".accdb"

    DBEngine.CreateDatabase DB_CopyTo, dbLangGeneral
    
    With CurrentDb
        For Each t In .TableDefs
            Select Case True
            Case Left(t.Name, 1) = "~"
            Case Left(t.Name, 4) = "msys"
            Case Else
                .Execute "select * into [" & DB_CopyTo & "].[" & t.Name & "] from [" & t.Name & "]"
            End Select
        Next
    End With
    
End Function

 

[jaryszek]

Hi Guys,

I have tested Gasman's macro and this is not working.

I have error: "Item not found in this collection":

Error is here:

Set tdf = db.TableDefs(pTableName)

Why this is not working?
What if i want to have db as other database like here:

Set OtherDB = OpenDatabase("C:\Users\ljar01\Desktop\Makro\Makro IBS\Raport Błędów\Aplikacja_Błędy_BE.accdb")

EDIT:
Maybe problem is within name of your database :

strDBpath = GetAccessBE_PathFilename("DoBackUp.accde")

Ok i know waht cause the problem. In your function you are passing now Database, you are passing only tblName.
I have done it with my table "tbl_Main" and problem is that here in this code:

If Len(tdf.Connect)

there is a problem.
It is showing constantly = "".

Please help Guys,
Jacek

 

[jaryszek]

To copy data from one database to another you can use SELECT INTO.

And instead of skipping linked tables, you could use them to specify which tables you want to back up from multiple source databases into a single target file.

Public Function RunBackup()

    DB_CopyTo = "C:\backup_" & Format(Now, "yymmdd_hhnn") & ".accdb"

    DBEngine.CreateDatabase DB_CopyTo, dbLangGeneral
    
    With CurrentDb
        For Each t In .TableDefs
            Select Case True
            Case Left(t.Name, 1) = "~"
            Case Left(t.Name, 4) = "msys"
            Case Else
                .Execute "select * into [" & DB_CopyTo & "].[" & t.Name & "] from [" & t.Name & "]"
            End Select
        Next
    End With
    
End Function

static thank you but i want to refer to another database not current.
So start other access database automatically not connected with my split database in order to do backup.

Jacek

 

[Gasman]

Fistly my code backs up the current FE or BE, nothing else.

In order to obtain the BE path, I use a table that is in the backend

strDBpath = GetAccessBE_PathFilename("tblUser")

You have to do the same.

So if you can still use it, change the above code to a table in your backend, as you obviously fo not have a 'tbluser' table.

 

[jaryszek]

Thank you Gasman.

This is still not working unfortunately.

Please find the sample database in attachment.

Problem is here:

If Len(tdf.Connect) = 0 Then

tdf.connect equals ""...

Please help,
Jacek Antek

 

[static]

static thank you but i want to refer to another database not current.
So start other access database automatically not connected with my split database in order to do backup.

Jacek

It doesn't matter where you want to copy data from or to. You can get information about Access objects from msysobjects, and to copy or insert you just specify the database path in the sql.

RunBackup CopyFrom , CopyTo , IncludeLinks

Private Sub Command0_Click()
    RunBackup "C:\src.accdb", _
              "C:\backup_" & Format(Now, "yymmdd_hhnn") & ".accdb", _
              False
End Sub

Public Function RunBackup(DB_CopyFrom As String, DB_CopyTo As String, IncludeLinks As Boolean)

[B][I]    'Create a new database for the backup data[/I][/B]
    DBEngine.CreateDatabase DB_CopyTo, dbLangGeneral
    
[B][I]    'type 1 is a normal table.
    'type 4 is a linked table in a non access databse. 
    'type 6 is a linked table in another access database[/I][/B]
    If IncludeLinks Then
        ttypes = "1,4,6"
    Else
        ttypes = "1"
    End If

[B][I]    'get names (open a recordset) of all tables (filtered by 'table type') from the source database[/I][/B]
    With CurrentDb.OpenRecordset("select name from [" & DB_CopyFrom & "].msysobjects WHERE type in (" & ttypes & ") and name not like 'msys*' and lvprop is not null")
    
[B][I]        'for each source table[/I][/B]
        Do Until .EOF
                
[B][I]            'copy the data from the source table into the target database and use the same name.[/I][/B]
            CurrentDb.Execute "select * into [" & DB_CopyTo & "].[" & .Fields(0).Value & "] from [" & DB_CopyFrom & "].[" & .Fields(0).Value & "]"

            .MoveNext
        Loop
        .Close
    End With
    
End Function

 

[jaryszek]

Wow static,

this is working very good!

Please help me understand your code.

What means IncludeLinks , why are you setting it as false and assignt to it type = "1,4"?

and this loop:

 With CurrentDb.OpenRecordset("select name from [" & DB_CopyFrom & "].msysobjects WHERE type in (" & ttypes & ") and name not like 'msys*' and lvprop is not null")
        Do Until .EOF

CurrentDb.Execute "select * into [" & DB_CopyTo & "].[" & .Fields(0).Value & "] from [" & DB_CopyFrom & "].[" & .Fields(0).Value & "]"

            .MoveNext
        Loop
        .Close
    End With

What means lvprop?
and with "select name" you are selecting first table?

 

[static]

"this is working very good!"

Excellent news!

What means IncludeLinks , why are you setting it as false and assignt to it type = "1,4"?

IncludeLinks - true Includes linked tables, false ignores linked tables.
I set it to false because you were trying to ignore links. Set it to true if you want.

In msysobjects different objects have different typeID's. 4 and 6 denote linked tables

and I just spotted my error - fixed in the post above

"What means lvprop? "

I have no idea what lvprop is. msys tables aren't documented.

"and with "select name" you are selecting first table?"

"select name from" selects all names matching the WHERE clause.

HTH

 

[jaryszek]

Ok thank you very much.

"select name from" selects all names matching the WHERE clause.

"CurrentDb.OpenRecordset("select name from [" & DB_CopyFrom & "].msysobjects WHERE type in (" & ttypes & ") and name not like 'msys*' and lvprop is not null")" --> so here you are doing some kind of loop ?
For each table which meet specific criteria in WHERE clause?

And one more thing:

Do Until .EOF

CurrentDb.Execute "select * into [" & DB_CopyTo & "].[" & .Fields(0).Value & "] from [" & DB_CopyFrom & "].[" & .Fields(0).Value & "]"

.MoveNext
Loop

What exactly is doing this code?
You are selecting all rows one by one within table and copy them to another database to the same name of table ?

Please help wtih that, i have to know this.
Especially ".Fields(0).Value" this is interesting.

Jacek

 

[Gasman]

Well you seem to have another solution that works for you, so that is the main thing.
I won't pretend to know what the difference is in your setup.

tdf.connect should produce something long the lines of ";DATABASE=\\srvph\JAGPH\DB\Employee_be.accdb"

I found this

Function List_Linked_Tables()

    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    
    Set dbs = CurrentDb
    For Each tdf In dbs.TableDefs
        If Len(tdf.Connect) > 0 Then
            Debug.Print "Table name: " & tdf.Name
            Debug.Print "Source table name: " & tdf.SourceTableName
            Debug.Print "Connection string: " & tdf.Connect
            Debug.Print
        End If
    Next
    Set dbs = Nothing
    
End Function

and that works nicely in my db.

I am assuming that it just tests the file passed is linked. is yours ?

 

[static]

Please help wtih that, i have to know this.
Especially ".Fields(0).Value" this is interesting.

Jacek

The recordset only has one field. The fields collection is zero based.
I could have used the field's actual name .Fields("name").Value but 0 is quicker to type.

I've edited comments into the code to answer your other questions.

 

[jaryszek]

Well you seem to have another solution that works for you, so that is the main thing.
I won't pretend to know what the difference is in your setup.

tdf.connect should produce something long the lines of ";DATABASE=\\srvph\JAGPH\DB\Employee_be.accdb"

I found this

Function List_Linked_Tables()

    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    
    Set dbs = CurrentDb
    For Each tdf In dbs.TableDefs
        If Len(tdf.Connect) > 0 Then
            Debug.Print "Table name: " & tdf.Name
            Debug.Print "Source table name: " & tdf.SourceTableName
            Debug.Print "Connection string: " & tdf.Connect
            Debug.Print
        End If
    Next
    Set dbs = Nothing
    
End Function

and that works nicely in my db.

I am assuming that it just tests the file passed is linked. is yours ?

Thank you for you post Gasman.
This is tested on BE but all tables are main tables without linking.

So this function is working only for FE?

Thank you static.

The recordset only has one field.

What this is mean? recordset is a table. And has a lot of fields not one...

Jacek

 

[static]

Nope. It would be more correct to say a table is a recordset, except a table defines how data is stored. When you open a table the data is read into a recordset and displayed in a grid that you think of as a table.

Queries also return recordsets and allow you to specify which fields to show.
I that query I selected one field 'name' from the msysobjects table.

The clue is the object names, TableDef, QueryDef.
'tables' and 'queries' are just a set of rules and instructions about how data is stored/displayed. They don't actually hold or display the data.

 

[jaryszek]

Thank you static for your help and support.
Now is more understandable for me.

Jacek