move db without lose the connection with external tables

[Luigi]

I use Access 2003 on Windows XP OS.
I made an Access application with many linked table (essentialy txt file but also table of other mdb files); how can I move my principal mdb file and its relavant external file in a different directory without have to specify all the new paths through connect tables management tool?

[Luigi]

I use Access 2003 on Windows XP OS.
I made an Access application with many linked table (essentialy txt file but also table of other mdb files); how can I move my principal mdb file and its relavant external file in a different directory without have to specify all the new paths through connect tables management tool?

I see many answers to a number of other more recent messages, is my problem hard or do I not explained well it?

[MMcCarthy]

I use Access 2003 on Windows XP OS.
I made an Access application with many linked table (essentialy txt file but also table of other mdb files); how can I move my principal mdb file and its relavant external file in a different directory without have to specify all the new paths through connect tables management tool?

You can't. If you move the backend source of a link you have to relink to the new path.

[Lysander]

You can't. If you move the backend source of a link you have to relink to the new path.

I have some code that I wrote for Access 2.0 that checks, on startup, if the tables attachments are correct, and if not, it fixes them. The code is very long, so I can only post the main procedure here, but I did not write it, I copied it from the NorthWind sample database. Hopefully, the code sample is still there in Access 2003 so you can explore that and adapt it to your needs.

I wrote this in 199?, with no error checking but it did work then. Maybe you can do something with it.

Function RAT_AreTablesAttached(dbname, dbloc) As Integer
    '  Updates connection information in attached tables.
    '  Modified by Lytton Consultants from MS-Access NWIND sample
    '   dbname is the database name with no extension
    '   dbloc is default loaction of database
    '
    Dim MaxTables As Integer
    Const NONEXISTENT_TABLE = 3011
    Const DB_NOT_FOUND = 3024
    Const ACCESS_DENIED = 3051
    Const READ_ONLY_DATABASE = 3027
 
    Dim Tablecount As Integer
    Dim filename As String, SearchPath As String, temp As String
    Dim ReturnValue As Variant, AccDir As String, I As Integer
    Dim MyTable As TableDef
    Dim MyDB As Database, MyRecords As Recordset
    Set MyDB = DBEngine.Workspaces(0).Databases(0)
 
    '  Number of attached tables for progress meter.
    MaxTables = MyDB.TableDefs.Count - 1 ' no longer a constant
 
    RAT_AreTablesAttached = True
 
    ' Now find first attached table, if any.
    temp = ""
    Tablecount = -1
    Do
        Tablecount = Tablecount + 1
        temp = MyDB.TableDefs(Tablecount).Name
    Loop While MyDB.TableDefs(Tablecount).Connect = "" And Tablecount < MaxTables
 
    ' Did we not find one?
    If MyDB.TableDefs(Tablecount).Connect = "" Then Exit Function ' This system has no attached tables
 
 
    '  Continue if attachments are broken.
    On Error Resume Next
    '  Open attached table to see if connection information is correct.
    Set MyRecords = MyDB.OpenRecordset(temp)
    '  Exit if connection information is correct.
    If Err = 0 Then
      MyRecords.Close
      Exit Function
    End If
 
 
 
    '  Get name of directory where MSACCESS.EXE is located.
    AccDir = SysCmd(SYSCMD_ACCESSDIR)
 
    ' See if it is in default location, dbloc
    ' If not, use <Access directory> as starting place for OpenFile dialog.
    temp = Dir$(dbloc)
    SearchPath = AccDir
 
    If (Dir$(SearchPath & dbname & ".mdb") = "") Then
        filename = GetMDBName(dbname)             ' Display Open File dialog.
        filename = Trim(filename)
        If filename = "" Then GoTo Exit_Failed ' User pressed Cancel.
    Else
        filename = SearchPath & dbname
    End If
    ' Loop through all tables, reattaching those with nonzero-length Connect strings.
    For I = 0 To MyDB.TableDefs.Count - 1
        Set MyTable = MyDB.TableDefs(I)
        If MyTable.Connect <> "" Then
            MyTable.Connect = ";DATABASE=" & filename
            Err = 0
            MyTable.RefreshLink
            If Err <> 0 Then
               If Err = NONEXISTENT_TABLE Then
                  MsgBox "File '" & filename & "' does not contain required table '" & MyTable.SourceTableName & "'", 16, "Can't Run system"
               ElseIf Err = DB_NOT_FOUND Then
                  MsgBox "You can't run this until you locate " & dbname & ".MDB", 16, "Can't Run System"
               ElseIf Err = ACCESS_DENIED Then
                  MsgBox "Couldn't open " & filename & " because it is read-only or it is located on a read-only share.", 16, "Can't Run system"
               ElseIf Err = READ_ONLY_DATABASE Then
                  MsgBox "Can't reattach tables because this database is read-only or is located on a read-only share.", 16, "Can't Run system"
               Else
                  MsgBox Error, 16, "Can't Run System"
               End If
               RAT_AreTablesAttached = False
               GoTo Exit_Final
            End If
        End If
    Next I
 
    GoTo Exit_Final
 
Exit_Failed:
    MsgBox "You can't run system until you locate " & dbname & ".MDB", 16, "Can't Run system"
    RAT_AreTablesAttached = False
 
Exit_Final:
    ReturnValue = True
End Function
 

[Luigi]

I have some code that I wrote for Access 2.0 that checks, on startup, if the tables attachments are correct, and if not, it fixes them. ....

Thank you very much Lysander. Starting from your example I write the following code.

My external tables are into different directories (the paths are stored in public constants).
I check each table and if there is an error I simply tray to move the connect path to the first possible directory, then I check again and, in case of error, I move the connection to the second and last possible path. At the end I check another time to verify if there are still an error and, in case, I report it.
Not very efficient but simple.

I have still a little problem, I use the root path in many different functions so I store it in a public const but in that way to move the DB it is necessary to change the code.
On the contrary if I store it in a public variable I can’t assign its value out of a function block (it is true?) and so EACH function must read it for example from a table. Is there an efficient way?

Public Const Radice = "C:\Dati\db\"
Public Const Dati_u = "dati utente\"
Public Const Manutenzione = "manutenzione\"
 
Function link_table() As Integer
'  Updates connection information in attached tables.
 
Const NONEXISTENT_TABLE = 3011
Const DB_NOT_FOUND = 3024
Const ACCESS_DENIED = 3051
Const READ_ONLY_DATABASE = 3027
Dim tipi_tab, I, posStart As Integer
Dim MyTable As TableDef
Dim MyDB As Database
Dim direttorio(2), filename As String
Dim varReturn, intCounter As Long
 
Set MyDB = DBEngine.Workspaces(0).Databases(0)
 
DoCmd.Hourglass True
 
Forms![Pannello comandi]![Running].Visible = True
Forms![Pannello comandi]![Running].Value = "Eseguo riconnessione tabelle ..."
Forms![Pannello comandi].Repaint
 
varReturn = SysCmd(acSysCmdInitMeter, "Updating...", (MyDB.TableDefs.Count - 1) * 3)
intCounter = 1
 
direttorio(1) = Left(Radice, Len(Radice) - 1)
direttorio(2) = Radice & Left(Manutenzione, Len(Manutenzione) - 1)
 
On Error Resume Next
 
For tipi_tab = 1 To 3
    For I = 0 To MyDB.TableDefs.Count - 1
        varReturn = SysCmd(acSysCmdUpdateMeter, intCounter)
        intCounter = intCounter + 1
        Set MyTable = MyDB.TableDefs(I)
        filename = MyTable.Connect
 
        If filename <> "" Then
            Err = 0
            MyTable.RefreshLink
            If Err <> 0 Then
                If tipi_tab = 3 Then
                   If Err = NONEXISTENT_TABLE Then
                      MsgBox "File '" & "xxx" & "' does not contain required table '" & MyTable.SourceTableName & "'", 16, "Can't Run system"
                   ElseIf Err = DB_NOT_FOUND Then
                      MsgBox "You can't run this until you locate " & "cfr.MDB", 16, "Can't Run System"
                   ElseIf Err = ACCESS_DENIED Then
                      MsgBox "Couldn't open " & "xxx" & " because it is read-only or it is located on a read-only share.", 16, "Can't Run system"
                   ElseIf Err = READ_ONLY_DATABASE Then
                      MsgBox "Can't reattach tables because this database is read-only or is located on a read-only share.", 16, "Can't Run system"
                   Else
                      MsgBox Error, 16, "Can't Run System"
                   End If
                   link_table = False
                   GoTo Exit_Final
                End If
 
                posStart = InStr(1, filename, ";DATABASE=", 1)
                filename = Left(filename, posStart + 10 - 1)
                filename = filename & direttorio(tipi_tab)
                MyTable.Connect = filename
            End If
        End If
    Next
Next
 
Exit_Final:
 
Forms![Pannello comandi]![Running].Value = ""
Forms![Pannello comandi].Repaint
Forms![Pannello comandi]![Running].Visible = False
varReturn = SysCmd(acSysCmdClearStatus)
DoCmd.Hourglass False
 
End Function
 

[MMcCarthy]

As long as the public variable is global, that is it is declared outside of any procedure or function in a module. Where a number of such variables are required in a database it is standard practice to create a module just to hold the declarations of these variables.

These variables should be available for use throughout the database.