Resetting Links to Delimited Text Files

[LarryP]

The Environment: Windows XP/Access 2003
My database uses two linked, pipe-delimited text files -- one is current
week data, one is prior week, with a new file being created weekly. Need
code to reset the links each time a new file comes out, so the "new" table in
Access links to the newly generated source file and the "prior" table now
links to the one from the previous week. Following code, though, fails. For
awhile I was getting an "object not current" error as soon as I tried to do
anything using tdfLinked, but somehow I apparently cured that. Now when it
hits the Refresh line it tells me that c:\PR111505.txt is not a valid path.
(that's an example of what my source file names look like -- the user selects
the two appropriate files on a form, and the filepathnames are passed to my
procedure.) I guess I can't see the forest for the trees here -- what am I
doing wrong?

Private Sub RefreshLinks(strPriorFile As String, strNewFile As String)
Dim dbsCurrent As Database, tdfLinked As TableDef
Set dbsCurrent = CurrentDb
Set tdfLinked = dbsCurrent.TableDefs("PIRPrior")
tdfLinked.Connect =
"Text;DSN=PIRLinkPrior;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=437;DATABASE=" & strPriorFile
tdfLinked.RefreshLink
Set tdfLinked = CurrentDb.TableDefs("PIRNew")
tdfLinked.Connect =
"Text;DSN=PIRLinkNew;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=437;DATABASE="
& strNewFile
tdfLinked.RefreshLink
Set tdfLinked = Nothing
End Sub

 

[John Nurick]

Hi Larry,

If you check the Connect property of a tabledef linked to a text file,
you'll find that the DATABASE argument refers to the folder that
contains the text file. The filename itself is in
TableDef.SourceTableName and not in the Connect string.

 

[LarryP]

Thank you, John. I noticed that when I tried to Debug.Print the connect
string, but didn't know why or what to do about it. If I follow you, then,
my code should look like this instead of what I originally wrote:

tdfLinked.Connect =
"Text;DSN=PIRLinkPrior;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=437;DATABASE=c:\"
tdfLinked.SourceTableName = " & strPriorFile {or strNewFile}
tdfLinked.RefreshLink

Have I got it right?

 

[John Nurick]

I feel that in principle that should work, but there seem to be
complications in practice.

Usually if I need to work with a variety of text files I don't try to
manipulate linked tables but instead write VBA code to assemble the
necessary SQL statements for whatever queries are required. The syntax
is pretty much the same as in TableDef.Connect. Here's one from my test
database (I've split it into multiple lines for easy reading, but in
fact the whole FROM clause is on one line):

SELECT *
FROM [Text;
DSN=AddrNew Link Specification;
FMT=Delimited;
HDR=YES;
IMEX=2;
CharacterSet=850;
DATABASE=C:\TEMP].[AddrNew1#txt];

You can see how the filename "AddrNew1.txt" is bracketed at the end of
the FROM clause, with a hash substituting for the normal dot. To work
with a different file in the same folder, all you have to do is alter
the filename. You can save this as a query, and then manipulate the
corresponding QueryDef's SQL property, or you can assign it as the
recordsource of a form.

With straight CSV files, most of the details in the [...] can actually
be omitted. If the first row of the file contains the field names you
don't need to pass the import specification as the DSN argument; and the
IMEX and CharacterSet parameters are also defaults. So I'd normally just
use

SELECT *
FROM [Text;;FMT=Delimited;HDR=YES;DATABASE=C:\TEMP].[AddrNew1#txt];

 

[LarryP]

Thanks again, John. It probably would've taken me three months to figure
that out on my own!

I feel that in principle that should work, but there seem to be
complications in practice.

Usually if I need to work with a variety of text files I don't try to
manipulate linked tables but instead write VBA code to assemble the
necessary SQL statements for whatever queries are required. The syntax
is pretty much the same as in TableDef.Connect. Here's one from my test
database (I've split it into multiple lines for easy reading, but in
fact the whole FROM clause is on one line):

SELECT *
FROM [Text;
DSN=AddrNew Link Specification;
FMT=Delimited;
HDR=YES;
IMEX=2;
CharacterSet=850;
DATABASE=C:\TEMP].[AddrNew1#txt];

You can see how the filename "AddrNew1.txt" is bracketed at the end of
the FROM clause, with a hash substituting for the normal dot. To work
with a different file in the same folder, all you have to do is alter
the filename. You can save this as a query, and then manipulate the
corresponding QueryDef's SQL property, or you can assign it as the
recordsource of a form.

With straight CSV files, most of the details in the [...] can actually
be omitted. If the first row of the file contains the field names you
don't need to pass the import specification as the DSN argument; and the
IMEX and CharacterSet parameters are also defaults. So I'd normally just
use

SELECT *
FROM [Text;;FMT=Delimited;HDR=YES;DATABASE=C:\TEMP].[AddrNew1#txt];

Thank you, John. I noticed that when I tried to Debug.Print the connect
string, but didn't know why or what to do about it. If I follow you, then,
my code should look like this instead of what I originally wrote:

tdfLinked.Connect =
"Text;DSN=PIRLinkPrior;FMT=Delimited;HDR=NO;IMEX=2;CharacterSet=437;DATABASE=c:\"
tdfLinked.SourceTableName = " & strPriorFile {or strNewFile}
tdfLinked.RefreshLink

Have I got it right?

 

[John Nurick]

Don't worry, Larry: I never figured it out on my own either!

Thanks again, John. It probably would've taken me three months to figure
that out on my own!

I feel that in principle that should work, but there seem to be
complications in practice.

Usually if I need to work with a variety of text files I don't try to
manipulate linked tables but instead write VBA code to assemble the
necessary SQL statements for whatever queries are required. The syntax
is pretty much the same as in TableDef.Connect. Here's one from my test
database (I've split it into multiple lines for easy reading, but in
fact the whole FROM clause is on one line):

SELECT *
FROM [Text;
DSN=AddrNew Link Specification;
FMT=Delimited;
HDR=YES;
IMEX=2;
CharacterSet=850;
DATABASE=C:\TEMP].[AddrNew1#txt];

You can see how the filename "AddrNew1.txt" is bracketed at the end of
the FROM clause, with a hash substituting for the normal dot. To work
with a different file in the same folder, all you have to do is alter
the filename. You can save this as a query, and then manipulate the
corresponding QueryDef's SQL property, or you can assign it as the
recordsource of a form.

With straight CSV files, most of the details in the [...] can actually
be omitted. If the first row of the file contains the field names you
don't need to pass the import specification as the DSN argument; and the
IMEX and CharacterSet parameters are also defaults. So I'd normally just
use

SELECT *
FROM [Text;;FMT=Delimited;HDR=YES;DATABASE=C:\TEMP].[AddrNew1#txt];