Linked CSV file locking

[ions]

Dear MS Access Expert,

I have a table link to a CSV file in my Ms Access database.

I use this table as the record source for a comboBox.

If one person opens the form that has the comboBox all works well.

If a second person opens the form on their computer while the form is opened on another computer the ComboBox is empty.

I am confused why this file locking is occuring. The combox is just reading the file not writing to it.

Is this an unfortunate feature of csv files? This does not happen when I link to a regular Access Table in the backend.

Thank you

 

[RossWindows]

I have experienced this while importing an excel file into Access. The import fails if someone else (or myself) already has the excel file open.

You may be experiencing a similar situation.
If you are not writing data/changes back to the CSV file, then you might try writing a function to copy the CSV to a temporary table each time the user accesses that combobox; then your combobox's recordsource would have to point to the temporary table and use the data from there.

 

[ions]

Yes I also thought of that Ross, but was hoping there is a more elegant solution. Why does it happen with CSV files specifically?

 

[RossWindows]

Not sure, although I have seen it happen on .xls too, so it might be an Excel issue.

 

[Galaxiom]

Not a solution to the problem but something I thought I would mention for anyone who stumbles upon this thread searching for detection of csv files that are already in use by a database.

I have a database that links to a text file. This file starts out from a Unix print spooler and is preprocessed using a DOS batch file which converts the linefeed characters, adds a date to each line, casts out the page headers and unwanted records. Another file holds information written by Access about which records have already been imported.

It is essential that Access does not have the files open during this process otherwise the batch file cannot delete the original and replace the file with one including new records.

This is DOS so is crude but effective. To check for the file being locked, include a command to copy the file to itself with the command output redirected to another file.

If the file is locked the command window shows:
The process cannot access the file because it is being used by another process.

And the output to the file is:
0 file(s) copied.

If the file is not locked it will return:
The file cannot be copied onto itself.
0 file(s) copied.

Simply parse the output file with the For command to determine which outcome has occurred.

copy file1.txt file1.txt > file1.lok
for /F %%a in (file1.lok) do if "%%a"=="The" del file1.lok
 
copy file2.txt file2.txt > file2.lok
for /F %%a in (file2.lok) do if "%%a"=="The" del file2.lok
 
if exist *.lok del *.lok & echo Table locked - Import aborted>> import.log & exit

 

[DCrake]

Another workaround is that when you first launch the mdb part of the startup routine opens the csv using the Open File for Input method and copies the contents into an array then what you load the form that contains the combobox the onload event uses the AddNew method to at the items to the rowsource. This way you do not need to touch the csv again during the lifetime of the session. The only issue you may have with this is that if the csv file is amended during the open session.

David