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?
--
Senior WebFOCUS Programmer

On Mon, 14 Apr 2008 09:43:02 -0700, BWS
<(E-Mail Removed)> wrote:

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


>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,

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?

--
Senior WebFOCUS Programmer


"Tom van Stiphout" wrote:

> On Mon, 14 Apr 2008 09:43:02 -0700, BWS
> <(E-Mail Removed)> wrote:
>
> Rather than linking to the files, import them. Either into Access or
> into the free SQL Server Express.
> -Tom.
>
>
> >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?
>

On Tue, 15 Apr 2008 07:35:01 -0700, BWS
<(E-Mail Removed)> wrote:

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.



>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?

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.
--
Senior WebFOCUS Programmer


"Tom van Stiphout" wrote:

> On Tue, 15 Apr 2008 07:35:01 -0700, BWS
> <(E-Mail Removed)> wrote:
>
> 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.
>
>
>
> >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?
>

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.
--
Senior WebFOCUS Programmer


"BWS" wrote:

> 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.
> --
> Senior WebFOCUS Programmer
>
>
> "Tom van Stiphout" wrote:
>
> > On Tue, 15 Apr 2008 07:35:01 -0700, BWS
> > <(E-Mail Removed)> wrote:
> >
> > 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.
> >
> >
> >
> > >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?
> >