Copy front end with linked tables converted to local

[rvsebi]

Hi, I want to make a copy of access front end on close with all linked tables converted to local. Any ideea?
Thank you in advance!

 

[isladogs]

Select all linked table in nav pane, right click and select Convert to local table.
Or I have a Link2Local procedure I can post later when I'm at my pc

 

[rvsebi]

Select all linked table in nav pane, right click and select Convert to local table...

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.

 

[isladogs]

Here you go....

This will convert a named linked table

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 Sub

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?

 

[rvsebi]

Thank you i will use that.
I still need to figure out how to make that backup with all tables locals and in the same time keeping front end like it is.

 

[isladogs]

OK - I'll leave it in your capable hands

 

[rvsebi]

Thank you for your time!

 

[rvsebi]

I made a table with all link table names and solved problem with this :

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