Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-12-2017, 06:01 PM   #1
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,238
Thanks: 93
Thanked 2,026 Times in 1,973 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Code to relink a text file

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.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 03-12-2017, 07:51 PM   #2
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,657
Thanks: 99
Thanked 1,500 Times in 1,415 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Code to relink a text file

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.
Galaxiom is offline   Reply With Quote
Old 03-13-2017, 04:08 AM   #3
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,506
Thanks: 362
Thanked 976 Times in 943 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Code to relink a text file

Quote:
Originally Posted by Galaxiom View Post
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.

__________________
And now nothing will be restrained from them, which they have imagined to do. Genesis 11:6

Steve
sneuberg is offline   Reply With Quote
Old 03-13-2017, 01:23 PM   #4
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,657
Thanks: 99
Thanked 1,500 Times in 1,415 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Code to relink a text file

Quote:
Originally Posted by sneuberg View Post
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.
Galaxiom is offline   Reply With Quote
Old 03-13-2017, 02:46 PM   #5
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,506
Thanks: 362
Thanked 976 Times in 943 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Code to relink a text file

Quote:
Originally Posted by Galaxiom View Post
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.
__________________
And now nothing will be restrained from them, which they have imagined to do. Genesis 11:6

Steve
sneuberg is offline   Reply With Quote
Old 03-13-2017, 03:24 PM   #6
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,238
Thanks: 93
Thanked 2,026 Times in 1,973 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Code to relink a text file

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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by jdraw; 03-13-2017 at 04:36 PM.
jdraw is offline   Reply With Quote
Old 03-13-2017, 03:39 PM   #7
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,506
Thanks: 362
Thanked 976 Times in 943 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Code to relink a text file

Quote:
Originally Posted by jdraw View Post
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.

__________________
And now nothing will be restrained from them, which they have imagined to do. Genesis 11:6

Steve
sneuberg is offline   Reply With Quote
Old 03-13-2017, 04:54 PM   #8
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,238
Thanks: 93
Thanked 2,026 Times in 1,973 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Code to relink a text file

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.

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)
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
The Following User Says Thank You to jdraw For This Useful Post:
sneuberg (03-13-2017)
Old 03-13-2017, 05:07 PM   #9
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,506
Thanks: 362
Thanked 976 Times in 943 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Code to relink a text file

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.
__________________
And now nothing will be restrained from them, which they have imagined to do. Genesis 11:6

Steve
sneuberg is offline   Reply With Quote
Old 03-13-2017, 05:14 PM   #10
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,657
Thanks: 99
Thanked 1,500 Times in 1,415 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Code to relink a text file

Where do the text files come from? Wouldn't you just read the information directly from the file system?
Galaxiom is offline   Reply With Quote
Old 03-13-2017, 06:54 PM   #11
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,152
Thanks: 3
Thanked 470 Times in 463 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Code to relink a text file

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.
Cronk is offline   Reply With Quote
Old 03-13-2017, 07:04 PM   #12
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,238
Thanks: 93
Thanked 2,026 Times in 1,973 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Code to relink a text file

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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by jdraw; 03-13-2017 at 07:13 PM.
jdraw is offline   Reply With Quote
Old 03-13-2017, 09:56 PM   #13
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,657
Thanks: 99
Thanked 1,500 Times in 1,415 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Code to relink a text file

Quote:
Originally Posted by jdraw View Post
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.
Galaxiom is offline   Reply With Quote
Old 03-14-2017, 05:05 AM   #14
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,238
Thanks: 93
Thanked 2,026 Times in 1,973 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Code to relink a text file

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

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
relink code not working Rachael Modules & VBA 8 11-21-2005 11:35 PM
Relink Backend code DBL Modules & VBA 5 06-13-2003 05:40 AM
Writing Code to relink tables R2D2 Modules & VBA 2 10-07-2002 11:42 AM
how to relink backend tables in code pauly General 1 08-16-2002 11:10 PM
Can someone take a look at this code to relink ODBC table reena Modules & VBA 1 04-29-2002 12:51 PM




All times are GMT -8. The time now is 09:38 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World