Changing name of linked CSV file.

Hi Experts,
I have a linked table to CSV file, and after finally finishing defining all field types and linking (hundreds of fields all different types, dont ask why...), I realized file was not named correctly.
Now just trying to change the name of file and getting the attached error.
Can someone please help me out here, so I dont have to redo the entire process...
Thanks
Untitled.png
LVL 6
bfuchsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertConsultingCommented:
Hi,

When the application start, you should copy your file at a constant location, and give it a constant name, like the temp folder (for performances reasons).
Gustav BrockCIOCommented:
Remove the link to the file (delete the linked table) and relink it.
PatHartmanCommented:
I don't see the file name in the linked tables list.  Did you rename it there?
Before you loose what you have done in the query (if it is not too late already), open the query in design view and copy the SQL String to a safe place.

If the query still works in QBE view, alias the table to some simplified name without the date suffix.  That will fix most references but you will have to fix any references that are inside functions.

So Select tbl.fld1, tbl.fld2, Left(tbl.fld3, 2, 4) as xx

the first two instances of tbl will be changed to a1 but you will have to change the reference inside the Left() from tbl to a1 manually.  This will make it easier to rename the actual table later so that in stead of being xxx_somedate, it is just xxx.
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

bfuchsAuthor Commented:
Hi Experts,
Guess did not express myself well what am I trying to accomplish/avoid...
Please see attached what I had to do in order to link properly, mapping all those fields to the right datatype...
Since all these were done already, for same file just under a different name, would it be possible to get that w/o having to go over the process of remapping again?
I don't see the file name in the linked tables list.  Did you rename it there?
No, See 2nd attachment where renamed file is located.
Thanks,
Ben
Untitled1.png
Untitled.png
Gustav BrockCIOCommented:
Click Save As to save the specification.

Then link the right file and load that specification when linking.
Adjust the name of the linked file object as needed.
bfuchsAuthor Commented:
Hi,
Click Save As to save the specification.

Then link the right file and load that specification when linking.
Can you elaborate more on this, perhaps post some screenshots.

Thanks,
Ben
Gustav BrockCIOCommented:
Oh, missed that your picture is for exporting. Had import/link in mind.

But why not just relink that table? By now, you could have done that a dozen times. If you had two dozens of tables to relink, you could run some code, but it won't pay off for one table only.
PatHartmanCommented:
There is another thread in progress on this topic.
bfuchsAuthor Commented:
@gustav,
Oh, missed that your picture is for exporting. Had import/link in mind.
Actually it is for linking, not for exporting.
If you had two dozens of tables to relink, you could run some code
Beleive me this 2 tables I'm currently dealing with are as difficult as dozens of normal designed tables

@Pat,
No, this thread is just for changing the name of file, or perhaps other minor changes like location of file, after file was already linked.

P.S. If you think there is no work around, I may consider closing this thread as is...

Thanks,
Ben
bfuchsAuthor Commented:
Just for you get an idea how frustrating it is, I just finished re-linking/mapping the entire table again (see attached), and then realized a problem, now will have to redo everything from scratch again...
Thanks,
Ben
Untitled.png
PatHartmanCommented:
Ben, you accepted code as the answer for renaming the files in the other thread.

The mapping wizard is clunky.  However, these mapping tables are among the few MSys tables that can actually be modified directly.  Sometimes you can directly edit the table to solve a problem.
bfuchsAuthor Commented:
However, these mapping tables are among the few MSys tables that can actually be modified directly.
That would be cool, hope it does not come with a risk of corrupting things...

btw, where do you enable view of sys tables in A2016?

Thanks,
Ben
PatHartmanCommented:
Right click on "All Access Objects" at the top of the nav pane
Choose Navigation Options.
Check the Show Hidden Objects box
bfuchsAuthor Commented:
@Pat,
I cannot modify anything under mSysObjects.
getting msg "control can't be edited, its bound to replication systam 'Database'
Thanks,
Ben
Fabrice LambertConsultingCommented:
Mappings are saved in the MsysIMEXSpecs and MsysIMEXColumns tables.
bfuchsAuthor Commented:
@Fabrice,
So by changing the datatype field in MsysIMEXColumns I can change something w/o having to relink?
Is that safe?
And how will I go for changing the file name/location?
Thanks,
Ben
bfuchsAuthor Commented:
Also I dont see column indicating which table those columns belong to, looks like missing/hidden columns...
Thanks,
Ben
PatHartmanCommented:
Ben, I didn't say MSysObjects.  I didn't specify the exact table names because I didn't know them off the top of my head.  If you had looked for them, you could have found them but Fabrice helped you out.

If everything is the right width and in the correct order, just use the wizard to change the data type.  That is a little more intuitive than changing the tables directly.

Remember, the only way to get to the table layouts in the wizard is by starting an import/export and then pressing the advanced button once the wizard starts.  Clunky but doable if you know what you are looking for.

If you change the tables manually and do something illogical such as overlapping fields or putting in an invalid data type, you won't get an error until you try to do an import.  I don't know what error you will get but you will get one.
bfuchsAuthor Commented:
Hi Experts,
So you all agree that there is no way simply to change a name of linked CSV file or its location, the only way is to run the wizard again and to redo all the mappings (select which datetype for each field) ?
Thanks,
Ben
PatHartmanCommented:
So you all agree that there is no way simply to change a name of linked CSV file or its location
Ben, if you made the mistake of using the "new, Improved" import wizard you are SOL.  It cannot be changed unless you can find the table that stores the file name and that table happens to be updateable.  For this reason, I NEVER< EVER use the "new, improved" wizard.  I ALWAYS use the old wizard which allows me to specify a file name at run time.  You will have to do the import one more time manually but this time use the old wizard which allows changes after the fact.  That will solve the problem.

The trick is before you get to the end of the import wizard, press the Advanced button and save the Import/Export spec.  People have had trouble with this since the beginning of time but rather than making the interface clearer so the user knew how to proceed, they overlayed a new, not-improved method which locks everything into one place and does not allow changes.  When I first started answering questions in the late nineties, that was the single most common problem everyone had.  Even experienced people since the process is non-intuitive.
bfuchsAuthor Commented:
Hi Pat,
if you made the mistake of using the "new, Improved" import wizard you are SOL.
Can you please clarify which is the old wizard and which is the new one, I only have one wizard in my Access version (A2016).
Perhaps include some screenshots so I have clear understanding what are you referring to.
The trick is before you get to the end of the import wizard, press the Advanced button and save the Import/Export spec...
Will this save me from redo everything in case I realized something was wrongly mapped, (for example a short text should have been long text)?
Thanks,
Ben
PatHartmanCommented:
You will have to go back one more time and make sure that you used the "Advanced" button to save the import spec rather than the option in the last step of the dialog.  the "new" method is embedded in the old method.  To execute the import/export, you would use TransferSpreadsheet or TransferText and specify the import/export spec name as well as use a variable for the file name.  

If you use the other method - DoCmd.RunSavedImportExport ("Import-SSKCallsToImport")
you're SOL if anything changes.
bfuchsAuthor Commented:
Hi,
You will have to go back one more time and make sure that you used the "Advanced" button to save the import spec rather than the option in the last step of the dialog
Okay lets try that.
To execute the import/export, you would use TransferSpreadsheet or TransferText and specify the import/export...
This is not for an import, I need it to establish a permanent link to that file, how do I go about this using that specification?
Thanks,
Ben
bfuchsAuthor Commented:
I just finished saving an link specification, and then realized I need to change something, how can I modify that specification w/o having to re-crate it from scratch?
Thanks,
Ben
Fabrice LambertConsultingCommented:
Mappings are saved in the MsysIMEXSpecs and MsysIMEXColumns tables.
Edit values in those tables.
PatHartmanCommented:
how can I modify that specification w/o having to re-crate it from scratch?
Start the manual import/export process again.  On the first wizard screen that shows the "Advanced" button, click it.  Choose the spec you want to modify from the saved list.  Don't forget to save before you exit.

This is a very confusing method, you are not alone in being unable to figure out how to modify existing specs.  MS should have fixed this 20 years ago.  But, they haven't.  If you have a Contact us option on your Access back stage page, feel free to use it and complain.  It is only through complaints from users that stupid things get changed.  They have never listened to the MVP's who have always complained about the difficulty of this interface.

As I told you earlier, you can use the MsysIMEXSpecs and MsysIMEXColumns tables and modify them directly for simple changes.  Otherwise, use the Wizard.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
Thanks to all participants!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
System Programming

From novice to tech pro — start learning today.