By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,606 Members | 1,140 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,606 IT Pros & Developers. It's quick & easy.

move db without lose the connection with external tables

Luigi
P: 4
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?
May 29 '07 #1
Share this Question
Share on Google+
5 Replies

Luigi
P: 4
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?
May 30 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Jun 1 '07 #3

Expert 100+
P: 344
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.

Expand|Select|Wrap|Line Numbers
  1. Function RAT_AreTablesAttached(dbname, dbloc) As Integer
  2.     '  Updates connection information in attached tables.
  3.     '  Modified by Lytton Consultants from MS-Access NWIND sample
  4.     '   dbname is the database name with no extension
  5.     '   dbloc is default loaction of database
  6.     '
  7.     Dim MaxTables As Integer
  8.     Const NONEXISTENT_TABLE = 3011
  9.     Const DB_NOT_FOUND = 3024
  10.     Const ACCESS_DENIED = 3051
  11.     Const READ_ONLY_DATABASE = 3027
  12.  
  13.     Dim Tablecount As Integer
  14.     Dim filename As String, SearchPath As String, temp As String
  15.     Dim ReturnValue As Variant, AccDir As String, I As Integer
  16.     Dim MyTable As TableDef
  17.     Dim MyDB As Database, MyRecords As Recordset
  18.     Set MyDB = DBEngine.Workspaces(0).Databases(0)
  19.  
  20.     '  Number of attached tables for progress meter.
  21.     MaxTables = MyDB.TableDefs.Count - 1 ' no longer a constant
  22.  
  23.     RAT_AreTablesAttached = True
  24.  
  25.     ' Now find first attached table, if any.
  26.     temp = ""
  27.     Tablecount = -1
  28.     Do
  29.         Tablecount = Tablecount + 1
  30.         temp = MyDB.TableDefs(Tablecount).Name
  31.     Loop While MyDB.TableDefs(Tablecount).Connect = "" And Tablecount < MaxTables
  32.  
  33.     ' Did we not find one?
  34.     If MyDB.TableDefs(Tablecount).Connect = "" Then Exit Function ' This system has no attached tables
  35.  
  36.  
  37.     '  Continue if attachments are broken.
  38.     On Error Resume Next
  39.     '  Open attached table to see if connection information is correct.
  40.     Set MyRecords = MyDB.OpenRecordset(temp)
  41.     '  Exit if connection information is correct.
  42.     If Err = 0 Then
  43.       MyRecords.Close
  44.       Exit Function
  45.     End If
  46.  
  47.  
  48.  
  49.     '  Get name of directory where MSACCESS.EXE is located.
  50.     AccDir = SysCmd(SYSCMD_ACCESSDIR)
  51.  
  52.     ' See if it is in default location, dbloc
  53.     ' If not, use <Access directory> as starting place for OpenFile dialog.
  54.     temp = Dir$(dbloc)
  55.     SearchPath = AccDir
  56.  
  57.     If (Dir$(SearchPath & dbname & ".mdb") = "") Then
  58.         filename = GetMDBName(dbname)             ' Display Open File dialog.
  59.         filename = Trim(filename)
  60.         If filename = "" Then GoTo Exit_Failed ' User pressed Cancel.
  61.     Else
  62.         filename = SearchPath & dbname
  63.     End If
  64.     ' Loop through all tables, reattaching those with nonzero-length Connect strings.
  65.     For I = 0 To MyDB.TableDefs.Count - 1
  66.         Set MyTable = MyDB.TableDefs(I)
  67.         If MyTable.Connect <> "" Then
  68.             MyTable.Connect = ";DATABASE=" & filename
  69.             Err = 0
  70.             MyTable.RefreshLink
  71.             If Err <> 0 Then
  72.                If Err = NONEXISTENT_TABLE Then
  73.                   MsgBox "File '" & filename & "' does not contain required table '" & MyTable.SourceTableName & "'", 16, "Can't Run system"
  74.                ElseIf Err = DB_NOT_FOUND Then
  75.                   MsgBox "You can't run this until you locate " & dbname & ".MDB", 16, "Can't Run System"
  76.                ElseIf Err = ACCESS_DENIED Then
  77.                   MsgBox "Couldn't open " & filename & " because it is read-only or it is located on a read-only share.", 16, "Can't Run system"
  78.                ElseIf Err = READ_ONLY_DATABASE Then
  79.                   MsgBox "Can't reattach tables because this database is read-only or is located on a read-only share.", 16, "Can't Run system"
  80.                Else
  81.                   MsgBox Error, 16, "Can't Run System"
  82.                End If
  83.                RAT_AreTablesAttached = False
  84.                GoTo Exit_Final
  85.             End If
  86.         End If
  87.     Next I
  88.  
  89.     GoTo Exit_Final
  90.  
  91. Exit_Failed:
  92.     MsgBox "You can't run system until you locate " & dbname & ".MDB", 16, "Can't Run system"
  93.     RAT_AreTablesAttached = False
  94.  
  95. Exit_Final:
  96.     ReturnValue = True
  97. End Function
  98.  
Jun 1 '07 #4

Luigi
P: 4
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?


Expand|Select|Wrap|Line Numbers
  1. Public Const Radice = "C:\Dati\db\"
  2. Public Const Dati_u = "dati utente\"
  3. Public Const Manutenzione = "manutenzione\"
  4.  
  5. Function link_table() As Integer
  6. '  Updates connection information in attached tables.
  7.  
  8. Const NONEXISTENT_TABLE = 3011
  9. Const DB_NOT_FOUND = 3024
  10. Const ACCESS_DENIED = 3051
  11. Const READ_ONLY_DATABASE = 3027
  12. Dim tipi_tab, I, posStart As Integer
  13. Dim MyTable As TableDef
  14. Dim MyDB As Database
  15. Dim direttorio(2), filename As String
  16. Dim varReturn, intCounter As Long
  17.  
  18. Set MyDB = DBEngine.Workspaces(0).Databases(0)
  19.  
  20. DoCmd.Hourglass True
  21.  
  22. Forms![Pannello comandi]![Running].Visible = True
  23. Forms![Pannello comandi]![Running].Value = "Eseguo riconnessione tabelle ..."
  24. Forms![Pannello comandi].Repaint
  25.  
  26. varReturn = SysCmd(acSysCmdInitMeter, "Updating...", (MyDB.TableDefs.Count - 1) * 3)
  27. intCounter = 1
  28.  
  29. direttorio(1) = Left(Radice, Len(Radice) - 1)
  30. direttorio(2) = Radice & Left(Manutenzione, Len(Manutenzione) - 1)
  31.  
  32. On Error Resume Next
  33.  
  34. For tipi_tab = 1 To 3
  35.     For I = 0 To MyDB.TableDefs.Count - 1
  36.         varReturn = SysCmd(acSysCmdUpdateMeter, intCounter)
  37.         intCounter = intCounter + 1
  38.         Set MyTable = MyDB.TableDefs(I)
  39.         filename = MyTable.Connect
  40.  
  41.         If filename <> "" Then
  42.             Err = 0
  43.             MyTable.RefreshLink
  44.             If Err <> 0 Then
  45.                 If tipi_tab = 3 Then
  46.                    If Err = NONEXISTENT_TABLE Then
  47.                       MsgBox "File '" & "xxx" & "' does not contain required table '" & MyTable.SourceTableName & "'", 16, "Can't Run system"
  48.                    ElseIf Err = DB_NOT_FOUND Then
  49.                       MsgBox "You can't run this until you locate " & "cfr.MDB", 16, "Can't Run System"
  50.                    ElseIf Err = ACCESS_DENIED Then
  51.                       MsgBox "Couldn't open " & "xxx" & " because it is read-only or it is located on a read-only share.", 16, "Can't Run system"
  52.                    ElseIf Err = READ_ONLY_DATABASE Then
  53.                       MsgBox "Can't reattach tables because this database is read-only or is located on a read-only share.", 16, "Can't Run system"
  54.                    Else
  55.                       MsgBox Error, 16, "Can't Run System"
  56.                    End If
  57.                    link_table = False
  58.                    GoTo Exit_Final
  59.                 End If
  60.  
  61.                 posStart = InStr(1, filename, ";DATABASE=", 1)
  62.                 filename = Left(filename, posStart + 10 - 1)
  63.                 filename = filename & direttorio(tipi_tab)
  64.                 MyTable.Connect = filename
  65.             End If
  66.         End If
  67.     Next
  68. Next
  69.  
  70. Exit_Final:
  71.  
  72. Forms![Pannello comandi]![Running].Value = ""
  73. Forms![Pannello comandi].Repaint
  74. Forms![Pannello comandi]![Running].Visible = False
  75. varReturn = SysCmd(acSysCmdClearStatus)
  76. DoCmd.Hourglass False
  77.  
  78. End Function
  79.  
Jun 5 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Jun 5 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.