Create linked table to text file, DAO (A97 )

Topic

New Reply

I have a series of text files of identical structure, and I want to create new linked tables for each of the files. I can do them one at a time manually, using the link/import specification created on the first table. How can I create the tables using DAO?

This is how I tried to create a new tabledef using the connect string from a working linked table. It issues Error 3001 – Invalid Arguement on the Append statement.

dim db as dao.database
dim tdf as dao.tabledef
….
Set db = CurrentDb
sConnect = “Text;DSN=UNOUTPUT Link Specification;FMT=Delimited;HDR=NO;IMEX=2;” & FilenameFolder(sFilename)
sSource = FileNameFullFromPath(sFilename)
‘ and create a new table
Set tdf = db.CreateTableDef(sTableName)
tdf.Connect = sConnect
tdf.SourceTableName = sSource
db.TableDefs.Append tdf ‘<<<<<<<<< error 3001 happens here

using Debug, the values of sTablename, sConnect and sSource are:
tbllnkEvent
Text;DSN=UNOUTPUT Link Specification;FMT=Delimited;HDR=NO;IMEX=2;C:Temp
Event.asc

The DSN part is copied from an existing, working linked table, and is the same specification that I see when I create the linked table manually.

Reply | Quote

Replies

Why not use the TransferDatabase method instead?
Pat

Reply | Quote

Why indeed? ‘cuz I can’t see the forest for the trees??

Actually, I struggled with TransferDatabase for a while after reading your message, then gave up. Then revisited the TransferText method that I had used in a different application. I thought it was just for importing text files, not for linking. Lo and behold, it has a parameter to specify a link. Too easy.

Thanks for setting me close to the right track.

Reply | Quote

At least you got there, despite some brain fade on my part.
Pat

Reply | Quote