Need access advise regarind linked versus imported tables - perfor

[AJ]

I have designed an application that uses a linked csv file as the main table.
The reason for this is because another user creates this file and jsut puts
it into a designated folder on their computer.
Here is the issue.
The csv file contains approximately 20K records. I cannot create any type of
index on a linked file. Am I better off importing the file into access (or
reading the linked table and inserting them into another table with an
index)? Is there an easy way to perform something like this? Will this
increase performance?
I am pretty new to access and am looking for any and all thoughts and
opinions. Also suggestions on how to do it would be great.
Thanks in advance!!

 

[John W. Vinson]

I have designed an application that uses a linked csv file as the main table.
The reason for this is because another user creates this file and jsut puts
it into a designated folder on their computer.
Here is the issue.
The csv file contains approximately 20K records. I cannot create any type of
index on a linked file. Am I better off importing the file into access (or
reading the linked table and inserting them into another table with an
index)? Is there an easy way to perform something like this? Will this
increase performance?
I am pretty new to access and am looking for any and all thoughts and
opinions. Also suggestions on how to do it would be great.
Thanks in advance!!

Yes, having the data in a table and indexed (on any fields used for sorting or
searching) will unquestionably improve performance. You can use File... Get
External Data... Import, selecting text as the "files of type"; or you can do
it programmatically using the TransferText method. If you need to just use
whatever current file the user provides, I'd suggest having a permanent
indexed table (locally if it's a single user database, in the backend if it's
a split multiuser database); run a Delete query without criteria to empty the
table, compact the database (backend if split), and then import into the
table.

 

[AJ]

Thanks John -
Just to be clear.
1. I should create a table with an indexed field.
2. Delete all from the table upon each run.
3. THen you mention import the table. Would I just do this by creating a
select / insert based on the linked table?
4. Can I do all this as a macro or VBA when I open the application? Would
you recommend this or create a button of sometype for the user to click when
they have a new file.
Thanks a ton!!

 

[John W. Vinson]

Thanks John -
Just to be clear.
1. I should create a table with an indexed field.
2. Delete all from the table upon each run.

Unless you want to keep an archive of the historical data.

3. THen you mention import the table. Would I just do this by creating a
select / insert based on the linked table?

That's one way; the Import process also offers the option of importing
directly into an existing table.

4. Can I do all this as a macro or VBA when I open the application? Would
you recommend this or create a button of sometype for the user to click when
they have a new file.

Sure. Look at the VBA help for "TransferText"; it's got examples of how to
write the code.

 

[AJ]

Thanks a TON John! I manually imported the file to see if the application
works better with a index and a PK. It is SOOOOO much faster. I am going to
look into the TransferText option.
Thanks again!!