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

x
?
Solved

Possible to change the ForeignName of MSysObjects?

Posted on 2011-09-26
14
Medium Priority
?
831 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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.
Suggested Courses

824 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