How to change the source of a linked table?

[KitaYama]

I have 3 linked tables to 3 csv files. These csv files are downloaded from an on-line order system.

Because of some changes that our IT has put in place, I have to change the folder the csv files will be downloaded to.
(From shared folder of domain controller to another File server).

Is there anyway to change the source of the linked tables without deleting and recreating them?
Each csv has more than 150 columns and it's like hell if I go through the linking wizard and selecting the columns I need and changing their names & data types.

Thanks.

 

[Minty]

They should show up in the linked table manager, so you can simply use that if it's one off change.

 

[sonic8]

Each csv has more than 150 columns and it's like hell if I go through the linking wizard and selecting the columns I need and changing their names & data types.

That's why it is advisable to store the specification used to link (or import) a CSV file. Then you can recreate the table with one line of VBA code.

You can try whether it works to just change the path to the linked file in the TableDef.Connect property, but I don't think it is possible.

 

[KitaYama]

They should show up in the linked table manager, so you can simply use that if it's one off change.

I can't find any.
Is this what you mean?

 

[KitaYama]

That's why it is advisable to store the specification used to link (or import) a CSV file.

If only I knew ....

 

[sonic8]

If only I knew ....

Maybe you can build the import spec by analyzing the existing tables/files and writing their structure into the ImEx-tables thus creating an import specification retroactively. - This is going to be a bit of work. Not sure if it worth it for a one time operation.

 

[Minty]

I can't find any.
Is this what you mean?

View attachment 111232

Yes, if you tick the top box (text) it should give the option to pick a new location at the bottom of the form?
It does for Excel sheets

 

[KitaYama]

Yes, if you tick the top box (text) it should give the option to pick a new location at the bottom of the form?
It does for Excel sheets

I've already tried that. Seems excel options are different than csv.
Here's what I have

 

[ebs17]

Each csv has more than 150 columns and it's like hell if I go through the linking wizard and selecting the columns I need and changing their names & data types

If this is necessary and the use of these linked tables has worked so far, then the necessary import specifications should already exist and can therefore be used immediately.
For a “TransferText acLinkDelim” you only need the names of these specifications, for example from the associated system table.

 

[KitaYama]

For a “TransferText acLinkDelim” you only need the names of these specifications, for example from the associated system table.

I'll work on this to see if I can find something usable.
Thanks.

 

[isladogs]

Yes you can alter the source path for CSV files in the LTM

OR if you save the path using the new IMEX data tasks, you can edit the path in a different way. See

View & Edit IMEX Data Tasks

Use to view & modify saved import/export XML data tasks

www.isladogs.co.uk

 

[CJ_London]

Is there anyway to change the source of the linked tables without deleting and recreating them?
Each csv has more than 150 columns and it's like hell if I go through the linking wizard and selecting the columns I need and changing their names & data types.

have you considered using a sql query instead of a linked table? then all you need to do is change the path

basic query is

SELECT *
FROM (SELECT * FROM [TEXT;DATABASE=C:\Path;HDR=Yes].filename.csv)  AS csv;

modify your select statement to just include the fields you require and do any renaming or data conversions if required (using cdate, cdbl etc for example)

you can also convert the query to an append, update or upsert query

Assuming you are using a query to take data from your linked tables to append/update you can probably modify that instead

 

[KitaYama]

have you considered using a sql query instead of a linked table?

No, never. I think it would be the best method. And now that you suggested it, I feel stupid why I didn't thought of it.

It would be the first thing I'll test Monday morning.

Million thanks.

 

[CJ_London]

the method only works as shown if it is a true csv (comma separated), if a different character is used you need to create a schema.ini file to specify the separator - and you can also specify other things like names, datatypes etc. If you have an automated importer, easy enough to create on the fly as part of the import process

Schema.ini File (Text File Driver) - Open Database Connectivity (ODBC)

Schema.ini File (Text File Driver)

learn.microsoft.com

 

[KitaYama]

Yes you can alter the source path for CSV files in the LTM

View attachment 111234

OR if you save the path using the new IMEX data tasks, you can edit the path in a different way. See

View & Edit IMEX Data Tasks

Use to view & modify saved import/export XML data tasks

www.isladogs.co.uk

@isladogs As @CJ_London suggested, I'll use a select query to read what I need from the csv. Just wondering why what you have is different with what I showed in #8.
Edit button is grayed out for me. Is it something because of the versions of Access? I'm on M365.
It's too late here and I haven't read the link you suggested. Maybe I find the answer tomorrow when I'm finished reading it.


thanks.

Thanks.

 

[CJ_London]

As @Minty suggested,

??

 

[isladogs]

@isladogs Just wondering why what you have is different with what I showed in #8.
Edit button is grayed out for me. Is it something because of the versions of Access? I'm on M365.
It's too late here and I haven't read the link you suggested. Maybe I find the answer tomorrow when I'm finished reading it.


thanks.

Thanks.

Nothing to do with the Access version. The Edit button is enabled when one or more items are selected.

The method described in my article involves a completely different approach

 

[KitaYama]

??

Appologies. It was a stupid mistake. I’m terribly sorry.
Corrected.

 

[KitaYama]

SELECT *
FROM (SELECT * FROM [TEXT;DATABASE=C:\Path;HDR=Yes].filename.csv) AS csv;

@CJ_London I'm not too good in queries, but is there any specific reason you used 2 Select statements? I used the following and the result was just the same as your suggestion.

SELECT * FROM [TEXT;DATABASE=D:\Downloads\;HDR=Yes].s0502.csv;
(In fact, instead of *, I used 10 fields xxxx As xxxx)

the method only works as shown if it is a true csv (comma separated), if a different character is used you need to create a schema.ini file to specify the separator

The downloaded csv files where tab separated. So at first I couldn't use them in above query. I've never used a schema.ini file, so started reading about them. I have a time limit set by IT, so while reading and learning about the ini file, I did did following as a work around and it worked.
I wrote a tiny function and used FSO and opened the csv files, read the contents, replaced vbTab to , and saved back the csv.
Then the above query showed the result perfectly.

Do you think I'll be OK with this method, or still a schema.ini is preferrable.

Thanks a lot.