relink to relative path txt data file

Discussion in 'Database' started by tobynegus, Aug 19, 2008.

  1. tobynegus

    tobynegus New Member

    Aug 19, 2008
    Likes Received:
    Trophy Points:
    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 :pleased:
  2. skp819

    skp819 New Member

    Dec 8, 2008
    Likes Received:
    Trophy Points:
    1. Create a Table named tblTablesToLink with the following 2 Fields:
      • FileName {TEXT} - Name only of Text File, e.g. March Sales.txt
      • 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:
      Expand|Select|Wrap|Line Numbers
      1. On Error Resume Next
      2. Dim MyDB As DAO.Database
      3. Dim rstFilesToLink As DAO.Recordset
      4. Dim strPathToFiles As String
      5. strPathToFiles = CurrentProject.Path & "\RawData\"
      6. Set MyDB = CurrentDb
      7. Set rstFilesToLink = MyDB.OpenRecordset("tblTablesToLink", dbOpenForwardOnly)
      8. With rstFilesToLink
      9. Do While Not .EOF
      10. 'DELETE each existing Link, then RECREATE it!
      11. CurrentDb.TableDefs.Delete ![TableName]
      12. DoCmd.TransferText acLinkDelim, , ![TableName], strPathToFiles & ![FileName], False
      13. .MoveNext
      14. Loop
      15. End With
      16. rstFilesToLink.Close
      17. Set rstFilesToLink = Nothing


Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice