Sharing Database problems

[tlc]

Please forgive me if I have missed a post on this. I spent the last few hours searching here and the internet for some things to try.

We currently have three computers running a database. Each of them has a front-end and a back-end is located on my computer. All front-ends are linked to the back-end. The two remote computers are using 2007, my computer is still using 2000. Only recently has the OS been ungraded on the remote computers, but the networking/sharing problem was around then as well.

We are using a wireless-g connection. All computers have mapped the network drive to my back-end share. On one remote computer(#2), the password to access the mapped drive is not saved (an unrelated issue), but she could access and edit the database while I was doing the same.

On the other remote computer (#3), they can only use the database when it is not in use by me (computer #1). And when #3 has it open, it locks out everyone else. With #3 opening it first, it puts me on "read-only, not updateable" status and it puts #2 on similar or worse.

When I have the database open first, #3 cannot access the backend at all. She gets "file is in use by another user" or something to that extent.

The back-end is set to Shared, No Locks and "Open database with record level..." checked.

At this point, we would like to set it up so that ONLY I can enter/delete/edit data and all remote computers can read but not change. I have tried a number of things, including some Windows XP permission changes, but nothing seems to work.

Any help would be great. Thanks

 

[GohDiamond]

Some thoughts: I've seen this problem. Multiple users trying to login to the database and getting the error you mentioned. I found that if a user had a TABLE open in the middle of editing a record, even if the table was being accessed by a form based on the table, it caused the collision that produced that error. It may be a bit of a pain but if you use Queries to represent your tables and base all other forms and reports and queries on the queries that represent the tables you'll experience much smoother simultaneous operation.

I have 12-14 users of a process tracking database which I built for a college financial aid department. I haven't heard a complaint about the type of problem you've mentioned since I switched from directly accessing the tables. The Query acts like a buffer and takes a fraction of a second to just dump or retrieve records from the tables making the table resources more available. Multiple instances of a query working on fields in the same table don't create the collisions that we saw before.

In addition I used replication on the front end after splitting. If you try this it's a bit dodgey so be sure to make regular backups before proceding. The long and short of it however is that each computer has a replicated Front End and there is a Design Master which I use for changes to the database objects. Having an individual REPLICA on each desktop has been great on cutting down network traffic. Local user data entry runs off the individual desktop CPU resources and access the backend momentarily for data which is then stored in local memory.

You should use the replica yourself unless you're changing something in the objects.



Goh

 

[The_Doc_Man]

There are many reasons this can occur. The SIMPLEST one that comes to mind is that the Workgroup Security is not set up correctly. Search this forum for "Securing a Database" and "Workgroup Security."

The mechanism by which this lock occurs if my guess is right is this: The person with whom you cannot "play nice" has a copy of a default workgroup file and your workgroup file was only partly secured. This other person comes in as a member of the Admins group and as a result has OpenExclusive.

Alternative: There is another meaning of "workgroup" that has to do with permissions and user accounts in Windows itself. If the person in question somehow lacks appropriate permissions to create, open for write, update, close, and delete the LDB file, that would have the effect you are describing. Using {file} >> Right-click >> Properties >> Security, that person should have gross privileges MODIFY or FULL ACCESS. Search the forum for "Windows Permissions" to see what the Advanced permission set should resemble.

 

[dfenton]

Two comments:

1. GohDiamond suggests replicating the front end for no good reason I can think of. The standard archictecture for *any* Access application is split front end/back end, with a front end on each workstation. Jet Replication is a Jet technology that does not work reliably over the long run in front-end MDBs, because forms/reports/etc. are Access objects and Jet just doesn't handle synching them well. There's no jusification for synching anyway -- you don't need to have two-way communication between the front ends on the workstations and the DM for the front end. All you need to distribute an updated front end is to simply copy it over top of the old one. This can be done manually or via some kind of automation (a batch file, or something like this: http://www.granite.ab.ca/access/downloadsindex.htm).

2. You simply cannot use Access safely over a wireless network. The bandwidth for most wireless standards is too small (and it's shared with everyone within range of your network, anyway, so you can never know how much of the bandwidth is going to be used up by people not on your network but in your neighborhood), and the connection is much, much too unreliable. A tiny dropout like happens very often with wireless connections can easily corrupt a back end data file, or, at the very least, result in the very annoying but fatal DISK OR NETWORK ERROR message which requires shutting down Access and starting over.

Wired networks are the only way to go with an Access application using an MDB as the data back end.

--
David W. Fenton
David Fenton Associates
http://dfenton.com/DFA/