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

relative, relink to csv files useing connect

P: 3
I am trying to relink to existing linked csv and txt files, although the location may be different it is always realtive to the mdb file
I have tried using the following code
(GetPath returns the location of the database, the data is stored in the /RAWDATA folder
the tdf.SourceTableName returns the name of the file i want relinked BHIFEE
strFileName returns:
C:\Users\toby\Documents\Clients\TDL Mick\July\RawData\BHIFEE.TXT



Private Sub cmdLinkNew_Click()
Dim strFileName$
Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
If tdf.SourceTableName <> "" Then
strFileName = GetPath & "\RawData\" & tdf.SourceTableName
' tdf.Connect = strFileName
tdf.Connect = ";Text=" & strFileName
tdf.RefreshLink
End If
Next

End Sub

I get an INVALID argument error
any help on what I am doing wrong or how I may do this much appreciated.

Toby
Aug 21 '08 #1
Share this Question
Share on Google+
2 Replies


puppydogbuddy
Expert 100+
P: 1,923
I am trying to relink to existing linked csv and txt files, although the location may be different it is always realtive to the mdb file
I have tried using the following code
(GetPath returns the location of the database, the data is stored in the /RAWDATA folder
the tdf.SourceTableName returns the name of the file i want relinked BHIFEE
strFileName returns:
C:\Users\toby\Documents\Clients\TDL Mick\July\RawData\BHIFEE.TXT



Private Sub cmdLinkNew_Click()
Dim strFileName$
Dim tdf As TableDef
For Each tdf In CurrentDb.TableDefs
If tdf.SourceTableName <> "" Then
strFileName = GetPath & "\RawData\" & tdf.SourceTableName
' tdf.Connect = strFileName
tdf.Connect = ";Text=" & strFileName
tdf.RefreshLink
End If
Next

End Sub

I get an INVALID argument error
any help on what I am doing wrong or how I may do this much appreciated.

Toby
Hi Toby,

You did not provide the specific line of your code that errors out, so it is hard to tell...it could be due to missing set statements, etc, but I think the following User Defined Function (UDF) code from the tips page of

www.aadconsulting.com will help. You can try substituting this code routine for yours...Or if you want to use your routine, you can use the code below as a guide to troubleshoot your code.

Public Function RefreshLinks(strFilename As String) As Boolean
' Refresh table links to a backend database - strFilename (full path)
' Returns True if successful.

___Dim dbs As Database
___Dim tdf As TableDef

___' Loop through all tables in the database.
___Set dbs = CurrentDb
______For Each tdf In dbs.TableDefs
_________' If the table has a connect string, it's a linked table.
_________If Len(tdf.Connect) > 0 Then
____________tdf.Connect = ";DATABASE=" & strFilename
____________Err = 0
____________On Error Resume Next
____________tdf.RefreshLink ' Relink the table.
_______________ If Err <> 0 Then
__________________RefreshLinks = False
__________________Exit Function
_______________ End If
_________End If
______Next tdf

___RefreshLinks = True ' Relinking complete.

End Function
Aug 21 '08 #2

ADezii
Expert 5K+
P: 8,669
  1. Isn't this a Double Post?
    http://bytes.com/forum/thread831151-...+location.html
  2. 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
  3. 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.
  4. Make sure to Copy these Files to the RawData Directory one level under the Directory where the Database resides.
  5. 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
  6. Good Luck and Good Night!
Aug 22 '08 #3

Post your reply

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