Solved

Possible to change the ForeignName of MSysObjects?

Posted on 2011-09-26
14
723 Views
Last Modified: 2014-07-15
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?
0
Comment
Question by:WilbertWaterbury
  • 5
  • 4
  • 3
  • +2
14 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36667290
< 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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36667754
btw, what version of Access are you using ?
0
 

Author Comment

by:WilbertWaterbury
ID: 36671303
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36675845
<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
 

Author Comment

by:WilbertWaterbury
ID: 36692321
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36707637
<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
 

Author Comment

by:WilbertWaterbury
ID: 36707673
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 77

Expert Comment

by:peter57r
ID: 36707752
Use a Deleteobject command followed by a Docmd.Transfertext command to delete the existing link to the file and create a new one.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36707808
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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 36709111
<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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36710117
cap, Pete,

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

Jeff
0
 

Author Closing Comment

by:WilbertWaterbury
ID: 36710936
Muchas Gracias
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 36710997
de nada...
0
 

Expert Comment

by:csaintaz
ID: 40198101
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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now