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.
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.
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.
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.
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.
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.
Code:
'---------------------------------------------------------------------------------------
' 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)
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.
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.
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.