samba, MS Access and file/record locking

[Dave Reed]

> Sender: davecb at scot.Canada.Sun.COM
> Date: Thu, 15 Jun 2000 08:36:49 -0400
> From: David Collier-Brown <David.Collier-Brown at canada.sun.com>
> 
>  Dave Reed <dreed at capital.edu> wrote:
> > Anyway, my wife's office is having some computer stability problems
> > and I'm wondering if Linux with samba might give them more stability.
> > They've got about 3 or 4 computers that share an Access database.  All
> > the machines are running Windows 9[58] (no NT/2000).  I don't know the
> > exact setup (and my wife doesn't have the computer knowledge to tell
> > me), but I'm assuming the database is stored on one computer and
> > they're all shared with Network Neighborhood.
> 
> 	This is a famously bad idea, which I usually describe as
> 	"a free sample of crack cocaine"
>  
> > A friend of a friend stopped by the office today and suggested they
> > get an NT server with Back Office and SQL server 
> 
> 	This is the configuration that MS recommends (quite correctly!)
> 	that you migrate to when you find that Access sharing files
> 	over SMB doesn't scale.
> 
> 	Access is a good "access method" to a back-end database,
> 	and, as you and Peter Samuelson suggest, will work with
> 	a Linux sql database.
> 	 
> > I'm thinking of getting a box running Linux with samba and putting the
> > database on it and then have all the Windows clients access (no pun
> > intended) the database off of it.  I don't understand exactly how
> > Access (I think they have the Office 97 version) works exactly as far
> > as file/record locking, etc. when multiple computers are sharing the
> > same database and how this might interact with samba.
> 
> 	If you use a back-end database, this will not be a program.
> 	If you share the database **file** via smb, locking will 
> 	work until such time as a new version of access or NT
> 	changes the implementation. For right now, Samba locks work
> 	properly with Access, but more than a few simultaneous users
> 	will bottleneck on network lock/data throughput.


I don't understand why you emphasized **file** - what else would I be
doing?  I just want to put the Access .mdb file on a samba server
instead of the setup they have now.  Also, you make the current setup
sound horrible (which I'm not disagreeing with), but to me it doesn't
sound like the new setup is that much better (other than the machine
with the .mdb file is not a client itself and of course it's samba/vs
Win95/98).  Why is the other one that bad and this one so much better
- is ther more to it?  Feel free to mail me off-list if you think it's
not relevant to the samba list.


> 	You do need to turn off "oplocks" on the database share,
> 	as the server won't recognize access automagically and
> 	do that for  you.


No problem.


> > The database file is probably around 50MB in size and there will only
> > be 2 or 3 simultaneous clients (accessing very small portions of the
> > database) so I don't expect any performance problems.  I figured a
> > Celeron 400 with 128MB of memory should be more than enough power to
> > do the job.
> 
> 	Tons!
> 
> > Can anyone tell me I don't have to worry about file corruption and
> > simultaneous connections will work fine (as long as they are accessing
> > different records in the database) with this setup?
> 
> 	You're ok there, just don't scale up...


This should be all they need.  It's not like it's constant use either.
It's an optometry/optician office so they are times where two or three
people might be updating a patient records from different computers,
but most of the time there will just be one person using the database
at a time.


>  Peter Samuelson <peter at cadcamlab.org>
> > My suggestion, if you're willing to invest a little more setup time,
> > would be to migrate to the free, industrial-strength PostgreSQL
> > database backend as opposed to the rather pathetic Jet Engine (the
> > Access `*.mdb' backend).  PostgreSQL comes with an ODBC driver (which
> > you can find precompiled for Windows if you look around a bit), so if
> > you install that on all the client machines, they can continue to use
> > Access as the frontend.
> 
> 	This is the better approach, assuming you can put
> 	some small amount of Lunux/database expertise in place
> 	at the office.


Right now I don't have the time to learn how to do this.  I'll let
them know that if they ever want to expand, they'll need to do this.
The staff there has minimal computer skills - they just know how to
use what you give them.  The current setup was made by a friend of the
owner (before my wife started working there).  Once the owner found
out I was a computer person, he's talked about me "fixing" things, but
I've been too busy and didn't really want to do it.  It sounds like
the problem is getting worse and this $700 solution is so much less
expensive than the $3500 NT/Back Office/SQL server that someone else
is recommending and I figured it would be enough for the job.

Thanks for all the info.

Dave