Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 706
  • Last Modified:

cannot change linked text file path

hi guys,

i've a linked textfile in my database and i'm trying to change this link path to the new text files that are generated every day.

here is my database and the 2 text files attached.

i have some code for this but when it wasn't working, i tried reducing everything back to basics - e.g. not using code by relinking the text file by hand.

when i tried to relink the text file by going to the link manager, i realised even though i chose the other text file i could not relink it.

question --> 1) can linked text files have their paths changed?
2) how can i do this in code? e.g. if i can't just simply change the path, how can i create a linked text file through vba?

thanks guys!
TextFileLInking.zip
0
developingprogrammer
Asked:
developingprogrammer
  • 2
  • 2
2 Solutions
 
Gustav BrockCIOCommented:
That is because what you relink is not the table but the database, and for text files the "datebase" is the folder, thus the connect string doesn't contain the file name.

The file name is hold in the SourceTable property which you can't refresh.
You have to delete the old link and recreate it using the new file name.

Also, note the link uses the 8.3 (short) file name, so you may get in trouble with long file names.

To relink to another file, it must be placed in another folder and have the same (short) file name as the original.

/gustav
0
 
developingprogrammerAuthor Commented:
Whao gustav!! I would have never figured that out in a million years!!

the short name probably also explains the error 3011 that arises when I have multiple .s in my file name when I use transfertexf. What fantastic gustav, thanks so much for your help = )
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
What I do for this is have a working "scratch" file in a known location.

I then kill the scratch file and copy my input file to that scratch file spec and continue on.

Jim.
0
 
developingprogrammerAuthor Commented:
hey gustav i read up a bit on the 8.3 short file name on wiki and that's really cool. i'm not sure how i would ever come across this 8.3 short file name if you didn't mention it ha. haven't had time to go through the whole thing but really interesting and helpful. thanks once again!! = ))

thanks for your help Jim! in the end i resorted to the DoCmd.TransferText function cause i think linking to a textfile is less stable than importing it in - as in one fixed state to another fixed state. something like deterministic state. DFSM i think it's called - not sure if i'm using it correctly though ha. but thanks for your sharing!! = ))
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now