Code to relink a text file

[jdraw]

I'm looking for an example procedure (vba) to relink a table that is a linked external text file.

Have had no luck with google.
Thanks in advance.

 

[Galaxiom]

I expect it would be much the same as changing the location of any other linked object.

Change the value in the DATABASE section of the tabledef's Connect property to link it to a different location.

 

[sneuberg]

I expect it would be much the same as changing the location of any other linked object.

Change the value in the DATABASE section of the tabledef's Connect property to link it to a different location.

I thought that too and was going to recommend the J Street Relinker but I tested it and that one doesn't work (produces errors). I think I may look at it again and try to figure why. It seems like it should as you can relink text files with the Link Manager the same way you do database files.

 

[Galaxiom]

I think I may look at it again and try to figure why. It seems like it should as you can relink text files with the Link Manager the same way you do database files.

Maybe there is a filename in the import specification that needs to match the one in the Connect property?

I recall coming across a way to access the import specs but the only part I remember is that they are XML.

 

[sneuberg]

Maybe there is a filename in the import specification that needs to match the one in the Connect property?

I recall coming across a way to access the import specs but the only part I remember is that they are XML.

The J Street Relinker failed at a point in the code where it was trying to open a database but even before that it was prompting me for a new database location even though the text file (the only linked file) had a good link. I think this was designed with the assumption that it would only be used to relink databases.

 

[jdraw]

I've been golfing so back at it fresh. Yes there is a way to create a specification. It adds data to MsysImexSpecs and MsysImexColumns. I remember doing this when importing text files to a database table years ago. I'm quite sure this follows a similar pattern, and will check it out.

When I look at the properties of the external linked text file, there is no CONNECT, so some of the relink code I have seen will not work.
I will report back.

 

[sneuberg]

I've been golfing so back at it fresh. Yes there is a way to create a specification..

MarkK wrote a class that completely builds an import spec from scratch and executes it. He posted it in this thread. That's good if you are importing something. If the file is just linked I don't think there's a specification at least when you go through the steps to link a text file it doesn't give you the option to save the steps like it does with an import.

 

[jdraw]

Steve and others,

I have resolved this.
As mentioned in earlier post, MSysIMEXSpecs and MSysIMEXColumns contain info about linked table specifications.

I created the specification to create a table to a specific text file as a linked (not imported) table. In the proposed application, a user will have many thousands of image files. He needs to review his database tables with ever changing image files in various directories and sub-directories. So a file of the full names of the existing images will be built, revised and rebuilt over a period of time as images are added, revised etc. So each revised list will be linked as a text table in order to use standard queries for database vs filesystem files comparisons.

I also found if you relink a table, Access will give you a new table with old name as base and increment suffix. So I have to delete the existing table before doing the re-link.

Here is the code for the re-link/create link to a text file.

'---------------------------------------------------------------------------------------
' Procedure : relnk
' Author    : mellon
' Date      : 13-Mar-2017
' Purpose   : This routine links a known .txt file as a named table in this database.
'  It deletes the existing link before doing the actual re-link. If you don't delete
'the existing link to the table, Access creates a new link table with old table name
'and incremental suffix.
'---------------------------------------------------------------------------------------
'
Sub relnk()

    Dim db As DAO.Database
    Dim tbd As DAO.TableDef
10    On Error GoTo relnk_Error
20    Dim sfilename As String: sfilename = "Filelist.txt"
30    Dim sSpec As String: sSpec = "MyLatestJpgs Link Specification"
40    Dim sTbl As String: sTbl = "MyLatestJpgs"
50    Set db = CurrentDb
60    For Each tbd In db.TableDefs
70      If tbd.Name = sTbl Then
80          db.TableDefs.Delete sTbl
90          Debug.Print Now & " -Link to Table (" & sTbl & ") was deleted  "
100     Else
110     End If
120   Next tbd
130   DoCmd.TransferText acLinkFixed, sSpec, sTbl, sfilename, True
140   Debug.Print Now & " -Table(" & sTbl & ") was relinked to file (" & sfilename & ") "
150   On Error GoTo 0
160   Exit Sub

relnk_Error:

170   MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure relnk of Module ModuJED"
End Sub

My debug.print outputs:

13-Mar-2017 8:33:21 PM -Link to Table (MyLatestJpgs) was deleted
13-Mar-2017 8:33:22 PM -Table(MyLatestJpgs) was relinked to file (Filelist.txt)

 

[sneuberg]

Thanks for the the update on this.

DoCmd.TransferText didn't occur to me. It should have as we use Docmd.TransferSpreadsheet to relink some of our spreadsheets.

 

[Galaxiom]

Where do the text files come from? Wouldn't you just read the information directly from the file system?

 

[Cronk]

FWIW, I took a different approach some years ago in a particular application, to importing a daily data file in text format by creating a link to a file and replacing it with each day's data file.

The datafiles had the file name format RAPSyyyymmdd.txt. I created a file linked to the database with the name RAPS.txt, and each day deleted this file and replaced it with a renamed copy of the that day's data.

Maybe not so elegant, but Q and D.

 

[jdraw]

Galaxiom,

Yes read from filesystem. But user in newbie, has equip but very limited in db and Access. Says he has over 500,000 images files and has been creating database tables. He verifies things by creating report and visually checking. I'm looking at a query to compare file system files list against his table(s) with a query(s). And trying to hide some of this from him while reducing his current manual efforts. The filesystem records are being updated while he is working on db.

 

[Galaxiom]

Says he has over 500,000 images files and has been creating database tables. He verifies things by creating report and visually checking.

Has he got the resources to buy and run MSSQL Server? The files and information about them could be integrated using the FileTable feature (available from version 2012 onwards).

FileTables are an adjunct to FileStream where the server stores the files in the database while presenting a standard Windows file system share. The files can be added, moved or deleted in Windows Explorer while simultaneously being available through queries as a file stream along with all the usual file metadata.

Furthermore, although not a useful feature for images, it also supports querying by full text semantic search for dozens of text based file formats.

 

[jdraw]

Still trying to sort out what he has and needs. Seems he has O365, and is brand new to Access??