relink relative TXT files using connect 1

[tobynegus]

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

 

[Remou]

I do not think a text file uses that kind of connect string.

Here is one I made earlier;

Text;DSN=NameOfFile Link Specification;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=850;DATABASE=C:\Name\Of\Directory

I would imaging that what you want to do, is to change the connect string DATABASE.

 

[jrbarnett]

A couple of points:

1. What does "GetPath" return?
Is it a function returning a string variable, a variable declared outside this sub etc?

2. In the sub, you declare the variable "strFileName$"
However later on you use the name "strFileName"

Address these and try your routine again.

John

 

[Remou]

jrbarnett

Isn't "$" a now deprecated way of declaring a string?

 

[tobynegus]

the get path returns the path of the database
the $ extensions denotes a string

 

[jrbarnett]

Remou - Yes, using a $ symbol is deprecated as a way of declaring a string. However, it still works at the moment.

Dim variablename As String

is far better (take note tobynegus)

John

 

[tobynegus]

If possible i would like to be able to just relink and provide a new path, i don't really want to have to connect, there must be some way of doing this via code, I can;t be the first person who wanted to do this?

 

[jrbarnett]

In the debug window (press Ctrl G and its at the bottom), type

Debug.Print tablename.Connect

where tablename is one of the tables that connects via your text driver, to return the current connection string.

John

 

[Remou]

Please consider my post. All you have to do is to replace the database portion of your current string.

[tt]strConnect=Mid(astr,1,instr(astr,"DATABASE=")+8) & NewPath[/tt]

 

[Remou]

JBarnett, I will avoid rhetorical questions in future, they do not translate well, although, to me, they appear more polite.

 

[tobynegus]

Thanks John sounds like it might help but....

I am using debug.print filename.connect and get
error 424 object required
I have tried BHIFEE(name of file in access ) and BHIFEE.TXT name of file to connect to error on both

I am doing somthing stupid, any help much appreciated
Toby

 

[Remou]

tobynegus

Is it that you do not find my posts helpful or that they do not work for you/

 

[tobynegus]

Remou
I do appreciate your help!
I am just looking at the
strConnect=Mid(astr,1,instr(astr,"DATABASE=")+8) & NewPath

forgive my dumbness, what is the astr?
Toby

 

[Remou]

Beg pardon, I did not tidy the sample string:

strConnect=Mid(tdf.Connect,1,instr(tdf.Connect,"DATABASE=")+8) & NewPath

 

[tobynegus]

Remou
thanks, I will try it now, I assume that this works on TXT and CSV files?

Toby

 

[tobynegus]

Remou
this looks great!
I get
Text;DSN=BHIFEE Link Specification;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=850;DATABASE=
C:\Users\toby\Documents\Clients\TDL Mick\July\RawData\BHIFEE.TXT
But...
Also an error of invalid path
This is the path, i must be missing something can you help

toby

 

[Remou]

You must use the path, not the path & file name:

Text;DSN=BHIFEE Link Specification;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=850;DATABASE=
C:\Users\toby\Documents\Clients\TDL Mick\July\RawData\BHIFEE.TXT

 

[tobynegus]

Remou
thank you Brill!!
another star for your patience

Toby