Creating tabledef to linked textfile from code

[Jesper F]

I need to link to a textfile from the database so that the file appears as
an attached datasource in the database window. I can do it manually but I
have to do it with code.
I'm using the following but what else do I need for it to show up in the db
window?
Thanks.
--
Dim db As DAO.Database
Dim tdf As TableDef
Dim sConnect As String

Set db = CurrentDb
sConnect = "Text;DSN=Citymail Link
specification4;FMT=Fixed;HDR=NO;IMEX=2;CharacterSet=1252;DATABASE="c:\testfi
lename.txt"
Set tdf = db.CreateTableDef("test", , , sConnect)

 

[Dirk Goldgar]

I need to link to a textfile from the database so that the file
appears as an attached datasource in the database window. I can do it
manually but I have to do it with code.
I'm using the following but what else do I need for it to show up in
the db window?
Thanks.

It's simpler to use the DoCmd.TransferText method for this. For
example,

DoCmd.TransferText _
acLinkFixed, _
"Testdata Link Specification", _
"test", _
"c:\testfilename.txt"

If you need to specify the character set, because it isn't the default,
I'm not sure what you should do, but I'd try modifying the tabledef's
Connect property after it's already linked.

 

[Dirk Goldgar]

It's simpler to use the DoCmd.TransferText method for this. For
example,

DoCmd.TransferText _
acLinkFixed, _
"Testdata Link Specification", _
"test", _
"c:\testfilename.txt"

If you need to specify the character set, because it isn't the
default, I'm not sure what you should do, but I'd try modifying the
tabledef's Connect property after it's already linked.

To be more in line with your original code, let's make that example:

DoCmd.TransferText _
acLinkFixed, _
"Citymail Link specification4", _
"test", _
"c:\testfilename.txt"

 

[Jesper F]

To be more in line with your original code, let's make that example:

DoCmd.TransferText _
acLinkFixed, _
"Citymail Link specification4", _
"test", _
"c:\testfilename.txt"

Great simple simple solution - thanks! It works great for one file at a
time.
One thing though that complicates it for me -

In the future I'll continiously need to link to new txt-files. They will be
fixed width but with a variable number of columns that the users won't know
ahead of time.
As I understand it I then can't use the same import-specification for new
files.
In the help file it says that one can either use an import-specification
(but I assume this must be created for each file format and column number)
or use a schema.ini file.
The users won't be able to go through manually linking each file through the
wizard so I need to automate this.
Is it possible to use a general importspec?
Or can an import spec be created on the fly automatically?
Or must I use a schema.ini with this setup?

If the latter is the case, do I perhaps need to use FileScriptingObject to
look in the text file first, create a schema.ini from the column number and
run the docmd with that schema.ini.?
Thanks for any thoughts, I appreciate it very much.

Jesper, Denmark

 

[Jesper F]

I found these two articles that might help me.
I'll try'em out and see if I can make it work.

http://support.microsoft.com/?kbid=210073
http://support.microsoft.com/?kbid=210001

 

[Jesper F]

Just as a follow up - this is what I ended up doing:
The files have 5-15 columns (unknown beforehand) and are fixed width and
have column names in the first row.

1) Read the first line of the text file with the filesystemobject
2) Go through that line and extract column names and column widths.
3) Write my own schema.ini with the trick in this article
http://support.microsoft.com/?kbid=210001
4) Create the link from the db to the file with this
http://support.microsoft.com/?kbid=210073

Works fine now. Thanks for your comments.

 

[Dirk Goldgar]

Just as a follow up - this is what I ended up doing:
The files have 5-15 columns (unknown beforehand) and are fixed width
and have column names in the first row.

1) Read the first line of the text file with the filesystemobject
2) Go through that line and extract column names and column widths.
3) Write my own schema.ini with the trick in this article
http://support.microsoft.com/?kbid=210001
4) Create the link from the db to the file with this
http://support.microsoft.com/?kbid=210073

Works fine now. Thanks for your comments.

Well done!

If you want, you can avoid the overhead of the FileSystemObject by using
standard VBA I/O statements to read the text file. But there's no need
to tinker with a solution that is working well for you.

 

[Jesper F]

If you want, you can avoid the overhead of the FileSystemObject by using

standard VBA I/O statements to read the text file. But there's no need
to tinker with a solution that is working well for you.

Thanks! At first I contemplated importing the text files, but they get up to
100 mb so I think that would crash sooner or later.
And I only need to read them, not modify them.
I don't think I've used the VBA I/O for the file access before - how is that
done?

 

[Dirk Goldgar]

Thanks! At first I contemplated importing the text files, but they
get up to 100 mb so I think that would crash sooner or later.
And I only need to read them, not modify them.
I don't think I've used the VBA I/O for the file access before - how
is that done?

For example:

Dim intFileNo As Integer
Dim strLine As String

intFileNo = FreeFile()
Open "C:\My Path\MyFile.txt" For Input As #intFileNo

' Read and print all lines to the Immediate Window.
Do While Not EOF(intFileNo)
Line Input #intFileNo, strLine
Debug.Print strLine
Loop

Close #intFileNo

 

[Jesper F]

For example:

Dim intFileNo As Integer
Dim strLine As String

intFileNo = FreeFile()
Open "C:\My Path\MyFile.txt" For Input As #intFileNo

' Read and print all lines to the Immediate Window.
Do While Not EOF(intFileNo)
Line Input #intFileNo, strLine
Debug.Print strLine
Loop

Close #intFileNo

Great I'll switch to that then. Didn't know it was built in.
Thanks again!