Possible to change the ForeignName of MSysObjects?

I would like to change the name of a linked text file that I spent considerable time customizing some 500 fields only to realize my input text file has a date stamp in the name and I would like to remove the date stamp by renaming the ForeignName.  

I tried an Update query without success (says it's not updateable).  Is there a way to change the name of imported file?
WilbertWaterburyAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
<I can see that the export wizard has placed the name of the external text file in the MSysObjects table in field ForeignName. It would seem that if I could change that field my problem would be solved, but I cannot directly change this field.>

sorry, but as i stated above..

"i don't think  you can change that ForeignName  :-(  
you have to change the external file and do it again. "


and delete the previously linked file from the database window and do a compact and repair to remove the entry of the "FUBAR_20110926.TXT" in the ForeignName column of the msysobjects table..
0
 
Rey Obrero (Capricorn1)Commented:
< Is there a way to change the name of imported file?>
pls clarify, an imported file is  a table in Access , is this the one you want to rename ?
if this is what you want to rename, right click on the table and select rename..


if that is not the case, give more info..
0
 
Rey Obrero (Capricorn1)Commented:
btw, what version of Access are you using ?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
WilbertWaterburyAuthor Commented:
I did an "External Data" tab "> Text FIle", "Link to the data source by creatig a linked table" command which invokes a wizard that creates a linked table to an external text file. I would like to change the name of the external file and change the link to point at the new file name without having to spend the two hours recreating it.  The table is in 2002-2003 format, but I can use any version 2003-2010. It was created with 2010.
0
 
Rey Obrero (Capricorn1)Commented:
<I spent considerable time customizing some 500 fields> are you sure ?
a table is limited to 255 fields..

i don't think  you can change that ForeignName  :-(  
you have to change the external file and do it again.
0
 
WilbertWaterburyAuthor Commented:
Correction 195 fields over 500 characters per record. The point is the over 2 hours to re-map the fields.

Anyone else? Is there a VBA solution?
0
 
Jeffrey CoachmanMIS LiasonCommented:
<No Points wanted>

I have re-read your question a few times.

It is not clear if you tried capricorn1s first suggestion:
    "if this is what you want to rename, right click on the table and select rename.."

This will do what you are asking:
"I would like to change the name of a linked text file"

If not, then post exactly what did not happen, that you wanted to happen.

Also then, clearly state what you mean by:
"change the ForeignName of MSysObjects"
"my input text file has a date stamp in the name"


Or simply post an example of this textfile for us to import, then tell us "Exactly" what you want to happen.

JeffCoachman
0
 
WilbertWaterburyAuthor Commented:
I was not clear in my initial question which is why capricorn's 1st reply did not need to be tried.  The question is whether I can change the database to effect a different external file name for the linked text table. The table name in the database would remain the same. For example, I created a link to text file as a table called FUBAR. The external text file was named FUBAR_20110926.TXT. The internal table is and should remain FUBAR.  I should have created the link to an external text file called FUBAR.TXT as the external file name will change daily and I should not have had the date in the name.

I can see that the export wizard has placed the name of the external text file in the MSysObjects table in field ForeignName. It would seem that if I could change that field my problem would be solved, but I cannot directly change this field.
0
 
peter57rCommented:
Use a Deleteobject command followed by a Docmd.Transfertext command to delete the existing link to the file and create a new one.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Or try using the linked table manager:

Open the Linked table manager
Select your linked table
Select: "Always prompt for new location"
Click OK
Select the new table
Click Open
0
 
Jeffrey CoachmanMIS LiasonCommented:
cap, Pete,

I'll bow out now, to avoid confusion.
I am sure you guys can get this resolved...

Jeff
0
 
WilbertWaterburyAuthor Commented:
Muchas Gracias
0
 
Rey Obrero (Capricorn1)Commented:
de nada...
0
 
csaintazCommented:
I realize this is an old thread, but I needed to do something similar and the following worked for me.  Hopefully this will help someone who landed on this thread.  It worked for me as of 07/14/2014.

1) Make a copy of the CSV file (in the physical folder) that is currently referenced from your linked table in Access.  
2) Rename the file (again, in the physical folder) to the new file name you were trying to change it to.
3) Link to the newly named CSV file from Access (In Access 2003, File -> Get External Data -> Link Tables, then select your new CSV file.
Here's where you use your previous spec, avoiding re-creation of the whole thing:

4) The "Link Text Wizard" box will appear.  Click on the Advanced button in the lower left corner.
5) Click the "Specs" button on the right side.
6) Select the Spec from your original CSV file where you did all your setup, and click "Open".
 - This will pull in the all the spec values, but match them up with your new csv file name.
7) Click "Save As", and name your new spec to match your new csv file.  I think you can name this whatever you want, because the link between the spec and the new csv file has already been established.   For clarity, you may want to use the new csv file name to name your spec.
8) In the "Link Text Wizard" click Finish.

Your new linked table will now reference the new csv file, using the settings established in the old spec..

If all your queries are still referencing the old linked table name, you can do the following to change the reference to the new linked table:

1) Go to Tools ->Options -> General -> Name AutoCorrect, and make sure the "Track Name" checkbox is unchecked.
2) Note the exact name of the old linked table, and then delete it
3) Rename your new linked table (in Access) to that of the old linked table.
If the Track Name checkbox was originally on, you could re-check if you need to.

Sorry this example used Access 2003.  The later versions should follow a similar methodology.  I find the substitute for the Database Window in the later access versions unusable. so I do much of my setup in Access 2003.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.