samba, MS Access and file/record locking

[David Collier-Brown]

 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.

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

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


 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.

--dave
-- 
David Collier-Brown,  | Always do right. This will gratify some people
185 Ellerslie Ave.,   | and astonish the rest.        -- Mark Twain
Willowdale, Ontario   | //www.oreilly.com/catalog/samba/author.html
Work: (905) 415-2849 Home: (416) 223-8968 Email: davecb at canada.sun.com