relink to relative location txt files

[tobynegus]

I have a access 2000 database (currently running jet3.6) that has linked TXT and CSV files
I need to be able to move the location of the database often, the linked data will always be in the same place /Rawdata/ relative to the MDB file
is there anyway this can be automated to relink into the current location?


Thank you for your attention

Toby

[ADezii]

1. Create a Table named tblTablesToLink with the following 2 Fields:
   1. FileName {TEXT} - Name only of Text File, e.g. March Sales.txt
   2. TableName {TEXT} - Linked Name as you would like it to appear in DB Window
2. Populate this Table with all the Names of the Text Files you would like to dynamically Link, as well as the Linked Names as you would like them to appear in the DB Window.
3. Make sure to Copy these Files to the RawData Directory one level under the Directory where the Database resides.
4. Run this code:
   

   On Error Resume Next
   Dim MyDB As DAO.Database
   Dim rstFilesToLink As DAO.Recordset
   Dim strPathToFiles As String
   
   strPathToFiles = CurrentProject.Path & "\RawData\"
   
   Set MyDB = CurrentDb
   Set rstFilesToLink = MyDB.OpenRecordset("tblTablesToLink", dbOpenForwardOnly)
   
   With rstFilesToLink
     Do While Not .EOF
       'DELETE each existing Link, then RECREATE it!
       CurrentDb.TableDefs.Delete ![TableName]       
       DoCmd.TransferText acLinkDelim, , ![TableName], strPathToFiles & ![FileName], False
       .MoveNext
     Loop
   End With
   
   rstFilesToLink.Close
   Set rstFilesToLink = Nothing

5. Good Luck and Good Night!