Linked Tables need to be relinked often

[tayloredsales]

I have two databases. One is the application and the other is the data. The application is loaded on the individual computers and the data is on a microsoft server. I used the link table manager to link the tables but they don't seem to stay linked. The users have to relink the tables. Have not seen this happen before so I was wondering if anyone else has ever seen this problem. Any suggestion would be appreciated.

[ADezii]

I have two databases. One is the application and the other is the data. The application is loaded on the individual computers and the data is on a microsoft server. I used the link table manager to link the tables but they don't seem to stay linked. The users have to relink the tables. Have not seen this happen before so I was wondering if anyone else has ever seen this problem. Any suggestion would be appreciated.

I personally have never seen this problem, but if you cannot correct it, the least you can do is automate the process of Re-Linking the Tables. Here is a custom routine I wrote which will accomplish just that. Use this as a last resort - let's wait and see if someone can come up with a solution.

1. Create a Local Table named tblLinkedTables.
2. This Table should consist of just 2 Fields [ID] (AutoNumber) [Primary Key], and [Table Name] (TEXT).
3. Into this Table, add the Names of all Tables which should be linked.
4. The code will check the Table Names in tblLinkedTables against all the Tables in the Database. If they exist and are Linked nothing happens, but if they do not exist, the code will Re-Link them automatically.
5. You will need to make at least 1 adjustment, being the Path to the Back End Data, but all should work well.
6. I tested the code and it is functional, but then again it was close to bedtime, so perform this operation on backup copies of your Front and Back Ends.
7. Any questions, feel free to ask.
   

   Dim MyDB As DAO.Database, MyRS As DAO.Recordset, tdf As DAO.TableDef
   Dim blnLinked As Boolean, strPathToData
    
   strPathToData = "C:\Haz-Mat\HMAU_Data.mdb"
    
   Set MyDB = CurrentDb()
   Set MyRS = MyDB.OpenRecordset("tblLinkedTables", dbOpenSnapshot)
    
   MyRS.MoveFirst
    
   Do Until MyRS.EOF
     For Each tdf In CurrentDb.TableDefs
       'Table exists, is Linked, and is not named tblLinkedTables
       If tdf.Name = MyRS![Table Name] And Len(tdf.Connect) > 0 And tdf.Name <> "tblLinkedTables" Then
         blnLinked = True: Exit For
       Else
         blnLinked = False
       End If
     Next
       If blnLinked = False Then
         DoCmd.TransferDatabase acLink, "Microsoft Access", strPathToData, acTable, MyRS![Table Name], MyRS![Table Name], False
       End If
       MyRS.MoveNext
   Loop
    
   MyRS.Close
   Set MyRS = Nothing