Solved

Possible to change the ForeignName of MSysObjects?

Posted on 2011-09-26
14
768 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
 
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

809 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