Linked .csv File Locking

[BWS]

Access 2003 problem

I have a daily automated job that creates multiple .csv files on a UNIX box,
and then FTP's these files to a read-only directory on an NT box, stomping on
top of the previous day's files.

There are many users opening and linking to these files via Excel and MS
Access.

I was surprised to learn that MS Access will still lock these .csv files
even though the user only has read-only permission.

This effectively does two bad things. If a user keeps Access open through
the night, my job fails because the files are locked. Also, there seems to
be more and more contention problems between users of this data, when one of
them is using MS Access.

Is there any way to force MS Access to play nice with Excel concerning
multiple users of a read-only .csv file?

 

[Tom van Stiphout]

On Mon, 14 Apr 2008 09:43:02 -0700, BWS

Rather than linking to the files, import them. Either into Access or
into the free SQL Server Express.
-Tom.

 

[BWS]

Tom,

It isn't me that is using this data for a back end... remember that I'm
creating these files for many users spread out all across the U.S.A. I
couldn't possibly police all of their activities concerning how they want to
use this data.

It just seems odd that MS Access doesn't have the capability to hook to a
read-only file... using and keeping it read-only without locking it up.

If I loaded these files into SQL Server Express as "read-only", would that
allow everyone access to them using Excel and MS Access without locking them
up?

 

[Tom van Stiphout]

On Tue, 15 Apr 2008 07:35:01 -0700, BWS

From your OP it wan't clear to me that the users were in different
locations and not under your control. Not knowing more details I can
only offer general suggestions.
I don't know if you are offering this MsAccess app that links to the
daily csv files, or if your user community has written their own apps
and are complaining to you - the data provider - that they can't use
the files multi-user. If the former: write a stronger app that imports
the data like I suggested in first instance. If the latter, offer an
MsAccess db in the ftp download.

-Tom.

 

[BWS]

Hmmmm... you got me thinking differently about the solution.

I have been coding on a UNIX platform for so long, I completely missed the
fact that these files are loaded to an NT box, who's directory is read-only.
The files themselves are not read-only, so that's why MS Access is able to
lock them.

I think the solution is to have an NT admin create an auto job to delete
these files at 3:00 AM... set my UNIX job to FTP these files to the NT box at
4:00 AM... then have the NT admin create another auto job to set all of these
files to read-only at 3:10 AM.

Please tell me that MS Access can't lock a file who's permissions are set to
read-only.

 

[BWS]

Never mind... I tested it.

MS Access can lock a linked .csv file even though the file permissions are
set to read-only, and the directory permissions are read-only.

I need Microsoft to chime in here. I think that maybe the NT admins we have
here are missing something important.