Copy front end with linked tables converted to local (1 Viewer)
- Thread starter rvsebi
- Start date
That is manually and i want to do it on event like on open.
For example
1. i will make a local table with two columns, BackupDate with datatype Date and Backup with Yes/No where No is default.
2. on open database i will check if that table have BackupDate with today date
-if no i gonna add it
-if yes nothing
3.on the same open i will check if table have BackupDate with yesterday date
-if yes check if Backup is yes/no and if is no "copy front end (network or local) with all linked tables converted" and put Yes on Backup
-if no nothing
In this way i can have database backup with front end for everyday.
I hope its clear, sorry for my english and thank you for your time.
Last edited:
isladogs
MVP / VIP
- Local time
- Today, 19:54
- Joined
- Jan 14, 2017
- Messages
- 18,209
Here you go....
This will convert a named linked table
So you now need to create a procedure to loop through all linked tables and apply Link2Local for each in turn. Can you do that part yourself?
This will convert a named linked table
Code:
Public Sub Link2Local(ByVal sTable As String)
'================================
'converts linked table to local table
'================================
On Error GoTo Err_Handler
Dim sTmpTable As String
sTmpTable = "mytmptable"
'Make tmp table
CurrentDb.Execute "select * into mytmptable from [" & sTable & "]" 'Wrapped in [] just in case!!!
'Delete Table
DoCmd.SetWarnings False
DoCmd.DeleteObject acTable, sTable
'Rename tmp to oldtable
DoCmd.Rename sTable, acTable, sTmpTable
DoCmd.SetWarnings True
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox "Error " & Err.Number & " in Link2Local procedure: " & Err.description
Resume Exit_Handler
End SubSo you now need to create a procedure to loop through all linked tables and apply Link2Local for each in turn. Can you do that part yourself?
I made a table with all link table names and solved problem with this :
Code:
...
Dim x As Integer, y As Integer
x = 1
y = DMax("ID", "TTableLink")
Do Until x > y
Dim rst As Recordset, sqlstmt As String
Set dbs = CurrentDb
sqlstmt = "Select * from [TTableLink] where [ID] = " & x & ""
Set rst = dbs.OpenRecordset(sqlstmt, dbOpenDynaset)
If rst.BOF Then
''
Else
Dim ntablelocal As String, ntablelocaltemp As String, ntablelink As String
ntablelocal = rst!NTable; ntablelink = rst!NTable
ntablelocaltemp = ntablelink & "Temp"
'''' put link table to local with "Temp"
CurrentDb.Execute "Select * into [" & ntablelocaltemp & "] from [" & ntablelink & "]"
''''delete link table
DoCmd.SetWarnings False
DoCmd.DeleteObject acTable, ntablelink
''''rename new table with oldname(without "Temp")
DoCmd.Rename ntablelocal, acTable, ntablelocaltemp
DoCmd.SetWarnings True
End If
x = x + 1
Loop
...