Hello,
I am tasked with providing code for backup of linked tables. These tables are supposed to be saved in a new database but as a local file.
I have the code for transferring the linked tables into a new database.
Sub BackUp()
Dim dTime As Date
On Error Resume Next
dTime = InputBox("Create a backup at", , Time + TimeValue("00:00:05"))
If Err.Number <> 0 Then Exit Sub
Do Until Time >= dTime
DoEvents
Loop
MsgBox "Time to create a backup"
Dim sFile As String, oDB As DAO.Database
sFile = CurrentProject.Path & "" & Format(Date, "m-d-yy") & ".accdb"
If Dir(sFile) <> "" Then Kill sFile
Set oDB = DBEngine.Workspaces(0).CreateDatabase(sFile, dbLangGeneral)
oDB.Close
Dim oTD As TableDef
DoDmd.Hourglass True
For Each oTD In CurrentDb.TableDefs
If Left(oTD.Name, 4) <> "MSys" Then
DoCmd.CopyObject sFile, , acTable, oTD.Name
End If
Next oTD
DoCmd.Hourglass False
MsgBox "Backup is stored in the same folder"
End Sub
I have the code for changing linked tables into local tables:
Sub convertLinkedToLocal()
On Error Resume Next
Dim db As DAO.Database, td As DAO.TableDef
Set db = CurrentDb
' Check each table definition
For Each td In db.TableDefs
' If a linked table (source is outside of the current db) . . .
If td.SourceTableName <> "" Then
' Select the table and convert to local
DoCmd.SelectObject acTable, td.Name, True
DoCmd.RunCommand acCmdConvertLinkedTableToLocal
End If
Next
End Sub
My Question is: How do i run the seconde code (for transferring linked into local tables) from my source databse for my backup databse.
I want to do this so i do not have to touch my backup database at all.
I am tasked with providing code for backup of linked tables. These tables are supposed to be saved in a new database but as a local file.
I have the code for transferring the linked tables into a new database.
Sub BackUp()
Dim dTime As Date
On Error Resume Next
dTime = InputBox("Create a backup at", , Time + TimeValue("00:00:05"))
If Err.Number <> 0 Then Exit Sub
Do Until Time >= dTime
DoEvents
Loop
MsgBox "Time to create a backup"
Dim sFile As String, oDB As DAO.Database
sFile = CurrentProject.Path & "" & Format(Date, "m-d-yy") & ".accdb"
If Dir(sFile) <> "" Then Kill sFile
Set oDB = DBEngine.Workspaces(0).CreateDatabase(sFile, dbLangGeneral)
oDB.Close
Dim oTD As TableDef
DoDmd.Hourglass True
For Each oTD In CurrentDb.TableDefs
If Left(oTD.Name, 4) <> "MSys" Then
DoCmd.CopyObject sFile, , acTable, oTD.Name
End If
Next oTD
DoCmd.Hourglass False
MsgBox "Backup is stored in the same folder"
End Sub
I have the code for changing linked tables into local tables:
Sub convertLinkedToLocal()
On Error Resume Next
Dim db As DAO.Database, td As DAO.TableDef
Set db = CurrentDb
' Check each table definition
For Each td In db.TableDefs
' If a linked table (source is outside of the current db) . . .
If td.SourceTableName <> "" Then
' Select the table and convert to local
DoCmd.SelectObject acTable, td.Name, True
DoCmd.RunCommand acCmdConvertLinkedTableToLocal
End If
Next
End Sub
My Question is: How do i run the seconde code (for transferring linked into local tables) from my source databse for my backup databse.
I want to do this so i do not have to touch my backup database at all.
Last edited: