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

relink to relative location txt files

P: 3
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
Aug 19 '08 #1
Share this Question
Share on Google+
1 Reply


ADezii
Expert 5K+
P: 8,669
  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:
    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.  
    6. strPathToFiles = CurrentProject.Path & "\RawData\"
    7.  
    8. Set MyDB = CurrentDb
    9. Set rstFilesToLink = MyDB.OpenRecordset("tblTablesToLink", dbOpenForwardOnly)
    10.  
    11. With rstFilesToLink
    12.   Do While Not .EOF
    13.     'DELETE each existing Link, then RECREATE it!
    14.     CurrentDb.TableDefs.Delete ![TableName]       
    15.     DoCmd.TransferText acLinkDelim, , ![TableName], strPathToFiles & ![FileName], False
    16.     .MoveNext
    17.   Loop
    18. End With
    19.  
    20. rstFilesToLink.Close
    21. Set rstFilesToLink = Nothing
  5. Good Luck and Good Night!
Aug 20 '08 #2

Post your reply

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