Split Database not allowing more than one user

[Thorson]

I recently split my database and put the backend in a shared folder and
distributed the front end. So far we have just tested it with 2 users. When
one user has the front end open and the other user trys to open it an error
pops up saying the file is already in use.

I am using Access 2007. Is there something specific I have to do to allow
multiple users?

 

[Chris O'C via AccessMonster.com]

That happens when the first user opens the db exclusively. Make sure they
open it shared as the default db option and all users have read/write/modify
permissions on the db file's folder. The file must be on a hard drive, not a
cd or dvd.

Chris

 

[Thorson]

I am not sure what yo mean by "Make sure they
open it shared as the default db option"

Is this a option I set in the database before distributing it? Or do they
have to open it from a shared folder?

This is how we have it set up so far. We have a shared folder on the
network with the BE in it. I have the FE on my desktop, I set this up to be
linked to the BE and then distributed a copy of the FE (through E-mail) and
had the users download a copy of the FE to their desktop.

Should I actually place the FE in the shared folder and the open it from the
shared folder every time?

Thanks for your help.

 

[Chris O'C via AccessMonster.com]

Open the back end db file. Tools > Options > Advanced.

Default Open Mode
[X] Shared
[ ] Exclusive

For distribution, you can continue emailing the front end to individual users
who save it on their desktop or you can use Tony Toews's free tool to
automatically update everybody's front end whenever you make changes and are
ready to publish it.

http://www.granite.ab.ca/access/autofe.htm

Chris

I am not sure what yo mean by "Make sure they
open it shared as the default db option"

Is this a option I set in the database before distributing it? Or do they
have to open it from a shared folder?

This is how we have it set up so far. We have a shared folder on the
network with the BE in it. I have the FE on my desktop, I set this up to be
linked to the BE and then distributed a copy of the FE (through E-mail) and
had the users download a copy of the FE to their desktop.

Should I actually place the FE in the shared folder and the open it from the
shared folder every time?

Thanks for your help.

That happens when the first user opens the db exclusively. Make sure they
open it shared as the default db option and all users have read/write/modify

[quoted text clipped - 10 lines]

 

[Thorson]

I checked my back end db file and it is already set to shared as the default
open mode... Any other things I should check?
--
Thorson

Open the back end db file. Tools > Options > Advanced.

Default Open Mode
[X] Shared
[ ] Exclusive

For distribution, you can continue emailing the front end to individual users
who save it on their desktop or you can use Tony Toews's free tool to
automatically update everybody's front end whenever you make changes and are
ready to publish it.

http://www.granite.ab.ca/access/autofe.htm

Chris

I am not sure what yo mean by "Make sure they
open it shared as the default db option"

Is this a option I set in the database before distributing it? Or do they
have to open it from a shared folder?

This is how we have it set up so far. We have a shared folder on the
network with the BE in it. I have the FE on my desktop, I set this up to be
linked to the BE and then distributed a copy of the FE (through E-mail) and
had the users download a copy of the FE to their desktop.

Should I actually place the FE in the shared folder and the open it from the
shared folder every time?

Thanks for your help.

That happens when the first user opens the db exclusively. Make sure they
open it shared as the default db option and all users have read/write/modify

[quoted text clipped - 10 lines]

I am using Access 2007. Is there something specific I have to do to allow
multiple users?

 

[Chris O'C via AccessMonster.com]

The files should be in the users' trusted folders. If you've got queries or
properties which use unsafe expressions, don't allow the users to disable
them when they adjust their macro security settings.

Make sure the network is trusted by the users in Internet Explorer.

Chris

 

[Thorson]

The network is trusted by the users' internet explorer.... I'm not sure how
that would affect whether or not more than one person can open the FE though.
I probably just don't understand.

 

[Chris O'C via AccessMonster.com]

The users who don't have the network trusted can't open or connect to the db
file on the network. They'll get an error message the file isn't trusted and
to download it to their hard drive. That solution doesn't apply to Access db
files.

It's just one more thing to check for avoiding problems with multiuser dbs.
You asked, so I gave it.

Chris

The network is trusted by the users' internet explorer.... I'm not sure how
that would affect whether or not more than one person can open the FE though.
I probably just don't understand.

The files should be in the users' trusted folders. If you've got queries or
properties which use unsafe expressions, don't allow the users to disable

[quoted text clipped - 5 lines]

 

[Thorson]

That makes sense now, when the users first open the database it won't let
them use the switchboard until they say the trust the file, then I have them
go to the trust center and set that up.

but I am still having a problem with more than one user trying to open the
database. When 1 user has the FE open and another tries to open the FE it
gives an error saying the file is already in use. I did check and the
default open mode is set to shared. I still need to fix this problem.

Thank you for your help.

--
Thorson

The users who don't have the network trusted can't open or connect to the db
file on the network. They'll get an error message the file isn't trusted and
to download it to their hard drive. That solution doesn't apply to Access db
files.

It's just one more thing to check for avoiding problems with multiuser dbs.
You asked, so I gave it.

Chris

The network is trusted by the users' internet explorer.... I'm not sure how
that would affect whether or not more than one person can open the FE though.
I probably just don't understand.

The files should be in the users' trusted folders. If you've got queries or
properties which use unsafe expressions, don't allow the users to disable

[quoted text clipped - 5 lines]

Any other things I should check?

 

[Chris O'C via AccessMonster.com]

The file is being opened exclusively. Are you sure both users are opening
the front end as a file located on their own desktops (not a common location
like the network share)? If you are, list both users' permissions on the
folder where the back end is.

Chris

 

[Thorson]

Right now we are just testing it with two users, myself and another user.
both files are located in non-shared folders. I have Full permissions and
the other user has read only permissions.

When the other user opens it and then I do it opens as read only for me. If
I have it open first it gives an error to the other user saying that the file
is already open.

 

[Chris O'C via AccessMonster.com]

As I said in my first post, all users have read/write/modify
permissions on the db file's folder. Add write and modify permssions for the
other user so he has all 3.

Chris

 

[Chris O'C via AccessMonster.com]

Make that "all users *must* have read/write/modify permissions on the db
file's folder".

Chris

 

[Archidrb]

Are you using a replica set or are both of you using the same FE file to
access the database?

I have not personally experienced this before, but I have some pretty
extensive hands-on experience with using an Access BE and a repliated FE set.


These kinds of things took me a while, please forgive me if you already know
this stuff. The shared folder must be set up so that all users have full
"modify" access to the folder itself. Then using the security settings (if
you have set up user groups and such) you can control who can access/see/wrte
whatever. But like all MS programs, the FE is just a file, like a Word
document, or more appropriately an internet web page. Its just a window to
look into the backend. Most MS files have a first in, first user principle.
The first person to open the file have full access and everyone else is read-
only at best.

If you can, create a replica set, a backend (BE), design master (DM), and a
master replica (MR). Have your testing user use the MR while you use the DM.
I suspect this might solve the problem right up front. I am not sure if its
this posting, but the backend should not be replicated, you should split the
database before replication.

Make that "all users *must* have read/write/modify permissions on the db
file's folder".

Chris

As I said in my first post, all users have read/write/modify
permissions on the db file's folder. Add write and modify permssions for the

[quoted text clipped - 3 lines]

 

[Chris O'C via AccessMonster.com]

Pardon me, but you've got your information backwards. Replication is for
data, not db objects. Not that Thorston needs it for his situation, but you
can replicate the data, the back end, to synch it with data in other dbs.

The front end should not be replicated. You don't want to synch up changes
in objects in the front end amongst many users unless you want db corruption.

Chris

 

[Archidrb via AccessMonster.com]

I could not disagree more. When using a FE/BE setup, there is no need to
replicate data because the data is sent from any FE to the BE directly. In
Access replication is for making "clones" of the master FE. When structural
changes are processed in the master FE the only way to get those changes into
the replica is to synchronize - or to have every user replace their cloned
(replicated) FE with a new one.

I have spent many years working in replicated Access databases and I am not
wrong. I know that synchronization can be used to copy data from one FE to
another FE, but replication is a way to clone the FE's.

It may be that you are confusing SQL with Access, an all too common problem
in Access boards. As a SQL Server certified DBA I know that you are using
the SQL definition of replication. Why Microsoft used the same terms in
different programs that do different things is a question for MS.

I have not explored much into ADP files and perhaps in a distributed ADP
synchronization is used to move data back and forth between FE's. When using
replicated MDB files, you go to Tools>Synchronization>Create Replica to make
a clone of the FE. Then to pick up changes to the structure in the front end
a synchronization is run to the replica(s).

As for replicating data back and forth from one FE to another without a BE
would be performed using the synchronization in Access, but users must be
careful to process the changes to a table in only one place. In my current
db's I have local tables that contain static data that only occassionally
needs additions or deletions. In those cases, I change the data and run the
synchronization to copy the data into the replicas. Tools>Replication>Create
Replica creates a FE.

The Tools>Replication menu contains these options: Synchronize Now, Create
Replica, Partial Replica Wizard, Recover Design Master, and resolve Conflicts.
The Synchronize Now option will not even highlight until a replica is created.
The Resolve Conflicts tool is used when data synchronized from a replica to
the master does not match what was previously in the master (two users update
the same table in two sources and try to synch one to the other). In this
case the user has to go through the errors to find which data source should
be kept. There is no "Replicate" or "Replication" menu option as the option,
it is a sub-menu.

So rather than being insulting, I will accept that you appear to be confusing
multiple processes in Access with SQL terminology.

Pardon me, but you've got your information backwards. Replication is for
data, not db objects. Not that Thorston needs it for his situation, but you
can replicate the data, the back end, to synch it with data in other dbs.

The front end should not be replicated. You don't want to synch up changes
in objects in the front end amongst many users unless you want db corruption.

Chris

Are you using a replica set or are both of you using the same FE file to
access the database?

[quoted text clipped - 17 lines]

this posting, but the backend should not be replicated, you should split the
database before replication.

 

[jacksonmacd]

Chris is correct. Replication is NOT for frontends, it is only for
pure Jet objects like tables and queries. Use something like Tony
Toews' application for pushing out updates to frontends.

You are correct that replication is not required for *this* BE because
it's used only on the LAN, but if it were to be used on, say, notebook
computers that are required to update data when disconnected from the
LAN, then replication is exactly intended for that situation.

I could not disagree more. When using a FE/BE setup, there is no need to
replicate data because the data is sent from any FE to the BE directly. In
Access replication is for making "clones" of the master FE. When structural
changes are processed in the master FE the only way to get those changes into
the replica is to synchronize - or to have every user replace their cloned
(replicated) FE with a new one.

I have spent many years working in replicated Access databases and I am not
wrong. I know that synchronization can be used to copy data from one FE to
another FE, but replication is a way to clone the FE's.

It may be that you are confusing SQL with Access, an all too common problem
in Access boards. As a SQL Server certified DBA I know that you are using
the SQL definition of replication. Why Microsoft used the same terms in
different programs that do different things is a question for MS.

I have not explored much into ADP files and perhaps in a distributed ADP
synchronization is used to move data back and forth between FE's. When using
replicated MDB files, you go to Tools>Synchronization>Create Replica to make
a clone of the FE. Then to pick up changes to the structure in the front end
a synchronization is run to the replica(s).

As for replicating data back and forth from one FE to another without a BE
would be performed using the synchronization in Access, but users must be
careful to process the changes to a table in only one place. In my current
db's I have local tables that contain static data that only occassionally
needs additions or deletions. In those cases, I change the data and run the
synchronization to copy the data into the replicas. Tools>Replication>Create
Replica creates a FE.

The Tools>Replication menu contains these options: Synchronize Now, Create
Replica, Partial Replica Wizard, Recover Design Master, and resolve Conflicts.
The Synchronize Now option will not even highlight until a replica is created.
The Resolve Conflicts tool is used when data synchronized from a replica to
the master does not match what was previously in the master (two users update
the same table in two sources and try to synch one to the other). In this
case the user has to go through the errors to find which data source should
be kept. There is no "Replicate" or "Replication" menu option as the option,
it is a sub-menu.

So rather than being insulting, I will accept that you appear to be confusing
multiple processes in Access with SQL terminology.

Pardon me, but you've got your information backwards. Replication is for
data, not db objects. Not that Thorston needs it for his situation, but you
can replicate the data, the back end, to synch it with data in other dbs.

The front end should not be replicated. You don't want to synch up changes
in objects in the front end amongst many users unless you want db corruption.

Chris

Are you using a replica set or are both of you using the same FE file to
access the database?

[quoted text clipped - 17 lines]

this posting, but the backend should not be replicated, you should split the
database before replication.

 

[Thorson]

So I need to give users Read/Write/modify permissions for the shared folder
the BE database is in? What if I want them to have read only permissions?
How do I limit what they can do.

From what I understand 2007 does not have the option to set up permissions
in Access like 2003. Instead the permissions must be limited using the
shared folder...

 

[Chris O'C via AccessMonster.com]

"So I need to give users Read/Write/modify permissions for the shared folder
the BE database is in?"

Yes.

"What if I want them to have read only permissions?"

Use an mdb file, NOT an accdb, and apply user level security. Give the users
group these permissions:

1 - open/run db
2 - open/run on forms, reports and macros
3 - read data on queries (this automatically gives read design permission but
don't worry about it)
4 - run with owner's permissions on queries

That means *DON'T* give the users group any permissions on tables.

Don't give a password to the default admin user. Don't put the mdw file on
the network share for the users. Put the mdw file in the developers' folder
which only developers have access to.

Anybody can open the db without supplying a user name and password, but they
can't make changes except to code - unless you distribute the semi-secured
front end as an mde file (recommended).

When you want to make changes, you (or the developer if it's somebody else)
give a password to the default admin user in the secure mdw file and sign in
as the db owner. Make your changes, convert to mde (recommended), and put
the new front end on the network share (which Tony Toews's autofe updates
automatically for the next users who open the db). If you remove the
password from the admin user on the secure mdw file, you're back to read only
too, so you see what the users normally see whenever you open the db.

"From what I understand 2007 does not have the option to set up permissions
in Access like 2003."

Access 2007 supports user level security for all mdb format files. It
doesn't support user level security on accdb format files because those dbs
use the ACE db engine, not Jet. You need Jet for user level security.

Chris

So I need to give users Read/Write/modify permissions for the shared folder
the BE database is in? What if I want them to have read only permissions?
How do I limit what they can do.

From what I understand 2007 does not have the option to set up permissions
in Access like 2003. Instead the permissions must be limited using the
shared folder...

Make that "all users *must* have read/write/modify permissions on the db
file's folder".

[quoted text clipped - 8 lines]

 

[Chris O'C via AccessMonster.com]

Darren, you don't understand Access replication. David Fenton's advice to
you and assessment of your skills for the last two years are spot on.
Recommend you follow his advice this time. In case you missed it, here you
go:

http://groups.google.com/group/microsoft.public.access.replication/msg/5fa671f0017500ff?hl=en


http://groups.google.com/group/microsoft.public.access/msg/31b0d4609e1d6619?hl=en


http://groups.google.com/group/microsoft.public.access.replication/msg/7881546311eee69d?hl=en


Chris