Situation: I am using a MS Access database with linked tables and want multiple users to work in this database. Another application overrides the linked tables (CSV-files) frequently, to provide updated data. These CSV-files are my interface to this other application, therefore I cannot import these files directly into my Access database. I only have to read data from these CSV-files, I do not want to write anything into them.
Problem: As soon as one user opens a form which uses data from any of the linked tables, no other user can access the data from the linked tables, altough multiple usage of the “internal” tables in Access works fine.
Approach: I tried multiple users to work in my db-file as well as to split my database into front and back end, but the result is always the same: only one user can access (read) data from the CSV-files, multiple access to this data at the same time is not possible.
Further Ideas: I was thinking of the following possibilities to solve this problem, but I am not sure if they will work or if they are feasable:
- Is there a possibility to include linked tables as read only, so that multiple users can access them at the same time?
- Does it make sense to load the data from the CSV-files when the correspondend forms are opened (via macro in the “open form” event) and write it into an “internal” table in Access? Thus this table would be overwritten every time an user opens this form. Is this method recommendable?
- Same as 2., but only update the table by deleting all records which differ from the records in the CSV-file and expand the table with all data from the CSV-files, which isn´t included yet.
- Is there a way to read the data from the CSV-files when opening the form, but store the data in a temporary table in Access?
Thank you very much for your help!