OOPs! You CAN make a copy of the Back-end database using vba in the Front-end - I made a stupid mistake with the name BUT. . .
UNLINKING TABLES
Can anyone explain why version 1 does not work (It stops halfway through because it can't find a table it has already deleted!) but version2 does work.
Version 1:
Private Function Unlink_Data()
On Error GoTo Unlink_Data_Err
Dim Db As DAO.Database, DataTb As TableDef
Set Db = CurrentDb
For Each DataTb In Db.TableDefs
If Not (Left(DataTb.Name, 4) = "MSys" Or Left(DataTb.Name, 4) =
_"USys") Then
'remove the link
Db.TableDefs.Delete DataTb.Name
End If
Next
Db.TableDefs.Refresh 'does this do anything??
Set Db = Nothing
Unlink_Data_Exit:
Exit Function
Unlink_Data_Err:
MsgBox Err.Description
Resume Unlink_Data_Exit
End Function
Version2:
Private Function Unlink_Data()
On Error GoTo Unlink_Data_Err
Dim Db As DAO.Database, DelTab() As String
Dim I As Integer, TbName As String
Set Db = CurrentDb
'collect all linked Tables to be deleted
Set Db = CurrentDb
ReDim DelTab(Db.TableDefs.Count)
For I = 0 To Db.TableDefs.Count - 1
TbName = Db.TableDefs(I).Name
If Not (Left$(TbName, 4) = "MSys" Or Left$(TbName, 4) = "USys") _Then DelTab(I) = TbName
Next I
'delete the linked Tables
For I = 0 To Db.TableDefs.Count - 1
If DelTab(I) <> "" Then Db.TableDefs.Delete DelTab(I)
Next I
Db.TableDefs.Refresh
Db.Close
Set Db = Nothing
Unlink_Data_Exit:
Exit Function
Unlink_Data_Err:
MsgBox Err.Description
Resume Unlink_Data_Exit
End Function
