Problem with Multi-users in Access97

[JIM]

I have developed a database linked to SQL server 2000 in access97. When I
test the database on two of the three networks available to me, it will
allow multi-users to connect. But on one of those networks (the one where
the database is supposed to live) it will only allow one user at a time.
The database is not set for exclusive use and the security settings in
access appear normal. The folder in which the database resides is set for
full access for use by a user group set up specifically for this database.

This database was a standalone database which was accessible by more than
one user. I re-created it so it could be linked to the SQL Server. I think
it was originally done in access95 and converted to access97. When I
changed it, I started with Access97. I developed it on the server in
question using remote desktop.

This is not the only database I am having problems with on this server. It
seems that any database that is being converted for SQL Server acts the same
way. One in particular was converted from 2.0 to 95 to 97. Before
conversion it allowed multi-users, after it would not allow some users to
open it if it was already open, but it would allow others. This database was
developed offsite and moved to this server.

Some users are using PCs and others are using terminals to access the
database. Could the fact that there could be different versions of MDAC
being used cause this problem? Can anyone give me an idea of what may be
causing this problem?

 

[Douglas J. Steele]

My first suggestion would be to ensure that the permissions were, in fact,
set up correctly.

Can your users create a text file in the folder where the database resides?
Can they delete it?

 

[Guest]

It sounds like you only have one copy of the database, and
everyone is using it.

That is not the recommended way. The recommendation is to
give everyone their own copy of the database.

And if you give everyone their own copy, you won't have
to worry as much about locks and permissions and exclusive
access.

Access 2.0 handles locks and files and ldbs differently than
any other version, so it is no surprising that you have troubles
with file permissions after conversion.

Access 95 is not compatible with Access 97. If you have
any users using A95, you should forbid them from using the
database that your A97 users are using. Of course, if you
give them each their own database, this is not a problem.

(david)

 

[JIM]

Thanks for your responses.



We verified that the network security for the folder was correct. I also
checked to see if other users were able to open the database at the same
time. As with other revamped databases, some could and some can't. We
believe that the problem may be that these users are using different
versions of MDAC. We are currently in the process of checking if there is a
common MDAC thread for those who cant vs. those who can.



Giving each user his own copy of the database is not really an option for
us. The users have a tendency to modify the queries without really knowing
what they are doing and we end up with dueling conclusions with faulty data.
We are attempting to instill some type of discipline through version
control.



I'll keep you posted as to what we find out.



Thanks

Jim

 

[Douglas J. Steele]

Access 97 doesn't usually use MDAC (although you can add a reference to ADO
if you want), so that's unlikely to be a factor.

You could force each user to use the same version of the front-end (i.e.:
automatically recopy the "master" front-end to their workstation each time
they start the application). See what Tony Toews has at
http://www.granite.ab.ca/access/autofe.htm