Sharing CSV files with Access

[Bobk]

I have 10 csv files on our server. I have written some queries in Access and
have linked to the csv files succesfully. What I want to do is have Access
reside on the server where anyone can run it and run the queries. So far
multiple users can run the program, but Access locks the files when the
queries are run so that only one user at a time can run the queries.
Apparently Access locks the specific csv files it needs for the queries.
Access is set to share files without record locking, but the csv files are
being locked while in use. Is there a way I can link to csv files where they
can be simultaneously open and used by multiple users?

 

[Stefan Hoffmann]

hi Bob,

Is there a way I can link to csv files where they
can be simultaneously open and used by multiple users?

As far as I know: No.


mfG
--> stefan <--

 

[Jackie L]

Bob,

It will be best to have the queries running on separate front end databases.
I could not tell from your question if that is how you have it set up. I
would then think that importing the csv data into temporary tables would
help your problem. Just create a temp table (like from a Make Table or
Append query) each time and then have your queries run from the temp tables.
So, each user would be running their own queries and temp tables and the
original csv files would only be locked during the import.

Depending on the size of the data, you could restrict the import so that
temp tables are not too large.

Hope this helps.
Jackie

 

[Bobk]

I have linked Access to the csv files. I have not split Access. Normally I
would split the database into fe and be, but I wasn't sure I could do it with
linked files. Should I split the database in this case?

 

[Jackie L]

Bob,

You would only need the fe/be set up if you have other data you are linking
to. Otherwise, you would only have a front end on each workstation with the
link to the .csv files (which would technically be the back end).

It is important to have the program by workstation for this scenario to
work. Actually, it is not ever recommended that users share a front end
database.

If your data is not too large, you do not even need to have the link to the
..csv file if you are going to convert it to temp tables. Instead, create an
import specification for each file to put the data into a table on the front
end. If you are dealing with data that should be filtered before coming into
the database, keep the links and use the Make Table or Append query scenario.

 

[Bobk]

I have a couple of files that are over 100,000 records. I'm not sure whether
Access has a table limit or not. My whole intent is to convert the csv files
to Excel spreadsheets. I have set up menus in Access where the user can
select the data he wants and would not exceed the record limits of Excel. In
some cases the queries require data from more than one file. The csv files
are updated daily. I wanted the csv files to reside on the server, available
to all users using the menus in the Access fe. Because the csv files are so
large it takes 10 or 15 minutes to create an update on the server for all 10
files. I was tring to avoid importing data into Access. I wonder if there is
another file format I could use for the server data or does Access always
lock linked files?

 

[John W. Vinson]

I have a couple of files that are over 100,000 records. I'm not sure whether
Access has a table limit or not.

No. Only a 2GByte limit on the database, not relevant for linked files.

My whole intent is to convert the csv files
to Excel spreadsheets. I have set up menus in Access where the user can
select the data he wants and would not exceed the record limits of Excel.

What version of Excel? The most recent versions let you store millions of
rows.

In some cases the queries require data from more than one file. The csv files
are updated daily. I wanted the csv files to reside on the server, available
to all users using the menus in the Access fe. Because the csv files are so
large it takes 10 or 15 minutes to create an update on the server for all 10
files. I was tring to avoid importing data into Access. I wonder if there is
another file format I could use for the server data or does Access always
lock linked files?

Linked *external text files* are locked because they're so limited: no
indexing, no blocking, they're just raw data. It's the most primitive
imaginable way to store data on a computer! If you store the data in an Access
database it will be available to Excel, Access, and to any ODBC or JET
compatible software. You can store it in SQL/Server - SQL Express is free.

I think you're causing yourself more problems than benefits by insisting on
..csv text files!

 

[Bobk]

Thanks for the input. I thought this would be much simpler. What I want to do
is to have some files reside on the server which would be updated daily. They
would be linked to Access fe's. They would not have to be re-linked or
imported after they were initially linked, even though the files on the
server were updated. I thought using the csv format would work - and it
does, except it gets locked by a single user
and cannot be shared. What is the best way to accomplish this? I want to be
able to set things up one time, update files on the server and have everyone
be able to share. Is there a way?

 

[John W. Vinson]

Thanks for the input. I thought this would be much simpler. What I want to do
is to have some files reside on the server which would be updated daily. They
would be linked to Access fe's. They would not have to be re-linked or
imported after they were initially linked, even though the files on the
server were updated. I thought using the csv format would work - and it
does, except it gets locked by a single user
and cannot be shared. What is the best way to accomplish this? I want to be
able to set things up one time, update files on the server and have everyone
be able to share. Is there a way?

Sure. A shared Access JET/ACE backend if you can deal with the 2GByte limit;
SQL/Server, Express if you want it for free; another client-server database
such as MySQL (also free, but I've seen some posts griping about its
problems), or any other ODBC compatible *database* solution. As I say, csv
files are not in any real sense a database.

 

[Bobk]

Thanks for the help.
I only want to read the data. I just want to be able to open and read
simultaneously with multiple users.
Do you know where to find a good tutorial describing the methods you have
suggested?
Thanks again in advance.