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?

 

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

 

[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?

 

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

 

[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?

 

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

 

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

 

[David W. Fenton]

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.

ACE is Jet 5, just with a different name. MS purposely chose to omit
ULS and replication because that fit their agenda for what they want
to do with Jet going forward.

 

[David W. Fenton]

I have some pretty
extensive hands-on experience with using an Access BE and a
repliated FE set.

Then you have extensive experience with completely misusing Jet
replication.

There is no justification for replicating a front end. Front ends
have no data that needs to be synchronized -- they have only
front-end user interface objects, and when those are updated, one
need only replace the old front end with the new one. There a number
of tools for automating that process so the user never needs to
worry about it.

Replicating a front end will eventually corrupt the VBA project and
it may make it impossible to synch. Worse still, if the corruption
gets bad enough, you could lose your entire project.

But since there's nothing gained by replicating a front end, it's
quite obvious there's no need to even take the risk.

Please stop giving all this bad advice about replication. This is
the second thread I've seen in the last couple of days in which
you've horned in and given completely erroneous recommendations for
using replication.

PLEASE STOP.

You don't know what you're talking about.

 

[David W. Fenton]

I could not disagree more.

Then you are an igorant moron who really doesn't understand enough
about Jet replication to be using it, let along posting advice for
other people.

Everything Chris wrote was spot-on correct.

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

There is no such thing as "Access replication" -- there is only JET
replication. The reason you can replicate a front-end MDB is because
it's a Jet data store, and the front-end objects are stored in Jet
data tables.

But it's completely inadvisable to do so, because the Access project
(i.e., all the code-bearing objects, forms/reports/modules) is
stored in a single BLOB field in a single record in one of the
system tables. The internal structure of that BLOB field is
COMPLETELY UNKNOWN to Jet, as it entirely Access-controlled.

In a front end, your user is not making changes to the front end
objects, so there is no need to actually synchronize anything from
the user back to the developer. Front end changes are ONE WAY, so
there is no utility in using replication, *except* if you haven't
split your app. In that case, you've fracked things up already by
not splitting, and rather than going the replication route for the
unsplit MDB, you should split and then you won't need replication at
all for distributing updates to the front-end application.

is for making "clones" of the master FE.

YOU ARE WRONG.

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.

Yes, exactly. They replace the UNREPLICATED front end with a new
copy of the front end.

NO REPLICATION NEEDED.

I have spent many years working in replicated Access databases and
I am not wrong.

YOU ARE COMPLETELY 100% 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.

This is a completely brain-dead stupid statement, with no basis in
fact.

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.

You are a world-class moron, quite obviously.

An explanation of Jet replication:

http://dfenton.com/DFA/Replication/index.php?title=What_is_Jet_Replic
ation%3F

You obviously have read nothing about the actual definitions of
database replication, or you would know how completely wrong
everything you've written about Access and Jet replication is.

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

You will only do this IF YOU ARE COMPLETELY STUPID.

Or, I guess, a novice.

As for replicating data back and forth from one FE to another

There is no need for this. There is nothing in a front end that
needs to be synchronized.

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.

You are a very, very confused person, and what you've written above
is completely gibberish. It shows that you really know nothing at
all about Jet replication and don't even have any real understanding
of Access development.

[]

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

You are a complete moron.

I suspect that you might be a sock puppet of Aaron's, given that you
don't exist in Google's archives until the last few days.

If you are Aaron and we are able to detect it, that will be reported
to your probation officer and you'll be in really deep shit.

If you're not Aaron, then you will be landing in killfiles
everywhere along with Aaron, and will be ignored just like he is.

 

[David W. Fenton]

You [i.e., Chris O'C] said:

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.

Okay, since I am a complete moron

If you're not a complete moron, why are you posting such completely
moronic advice?

who has no value and knows absolutely
nothing, why in over five years (closer to six to count it out)
have I not had a single incident of this?

My guess is because you are completely lying about what you're
doing.

Another alternative is that nobody actually uses the app that you
claim has a replicated front end.

We only converted to the SQL backend at the
very end of 2007 and used single network Access BE's for the three
databases the previous four+ years before. Perhaps its that we
have a different concept of many users.

This subject has nothing to do with replicating an Access front end,
so I'm not sure why you throw it into the mix.

I will accept that I need to learn a lot more about Jet
Replication; I am stuck in the mud with my understanding of it.
But you and others - who claim to be experts and MVPs - have
chosen to tell me how useless I am instead of taking the teaching
opportunity to explain to me (and anyone else reading the post)
what the point of having multiple backends in a multi-user network
solution would be?

Where has anyone suggested this? No one has done so.

How did you learn it? Did you spend day after day
reading books and online articles or did someone teach the basics
to you?

I've been creating replicated Access applications since 1997. I'm
the creator of the Jet Replication Wiki:

http://dfenton.com/DFA/Replication/

I do know a bit about the proper use of Jet replication and how it
works internally. I certainly don't know as much as people like
Michael Kaplan, but I have learned many things over the years, and
one of the things I've learned is that the things you recommend are
simply WRONG.

I get it that remote, non-networking users (I use the generic term
travelling salesman) don't want to be tied down to a network while
on the road or at home. Why would that user need a FE and a BE on
their laptop in order to replicate over the data? And if you are
not replicating the FE, how do you get and keep an up-to-date
version of the FE on their machine?

This is not a replication question.

Even so, if they can synchronize their front end, then they are
obviously connected to a network from which they could also download
a new front end.

In other words, the environment you assume for distributing
front-end changes via replication of necessity also provides the
appropriate environment for simply updating the front end when
needed.

Please tell me that your DB's are perfect and ready for full-time,
permanent use the day they implement. Teach me how to get my end
users never to ask for enhancements or new pieces. Teach me how I
get my DB's to be complete and perfect at implementation, because
I don't know how to do that. Then tell me why you choose to make
me (or anyone) feel like my solution is the complete anti-Christ
of networked Access databases that will bring about the end of the
database world as we know it? What's up with that?


I looked at Tony Toewe's piece today and emailed him directly for
more information. I had only looked at products that required
licensing in the past. His tool is for networked pieces,
potentially usable in my situation. No licensing fees is good.

Tony's utility has been around for many, many years. And there are
others much like it (though I've not found any as full-featured, or
so well-cared-for). You can do it with batch files, ferchrissakes.

In the mean time, my anti-Christ solution is and has been working
since late November of 2002 and growing significantly, but at some
impending moment in the coming future is will destroy itself and
everyone in the building.

It *will* corrupt. That it hasn't yet is just luck. If you would
take the time to understand how Jet works, and how the Access
project is stored in your front end, you'd understand that the
reason you can't use replication for Access objects is the same
reason you can't share a front end -- it's because of the structure
of the way Access stores its front-end object definitions.

Please, compel me to change it, apparently I am too stupid to do
it on my own.

Stop posting advice until you learn something about Jet replication.
Right now, you're posting damaging and erroneous information that
could lead others to make huge mistakes.

Just stop.

 

[David W. Fenton]

I suspect that you might be a sock puppet of Aaron's

I retract this, I guess. I did not know that this person had been
around before until Chris O'C posted some earlier instances of *me*
taking him down for offering bad information before.

 

[Thorson]

How do change my current .accdb database to .mdb? What if I am using
features only available in 2007 how will those be affected?

 

[Tom van Stiphout]

On Thu, 19 Mar 2009 09:31:01 -0700, Thorson

Office Key > Save As

They won't work.

-Tom.
Microsoft Access MVP

 

[Thorson]

So if I am understanding everything correctly the only way to allow multiple
users to open a "Microsoft Access 2007 Front End" is to allow all users full
permissions (read/write/modify), and if I want to limit certain users'
permissions I must convert the database from .accdb to .mdb and loose the
features of the database that are only available in 2007?

Is there any other way? I would like multiple users to be able to open the
Front End of the database while some users have full permissions and some
users have read only permissions and still maintain all the features of
Microsoft Access 2007.

 

[Thorson]

Yes, each user is opening his or her own copy, I am using an auto-updater
program (http://www.granite.ab.ca/access/autofe.htm).

I had previously understood that I could set up user level security by
setting permissions for the shared folder the back end is in, however this is
preventing multiple users from opening the front end at the same time.

I am not using the Sharepoint Server. The features I would like to keep are
things such as when a animal health diagnosis is entered in a form it
automatically pops up the options for the drug in another field; and if a
drug is entered in that is not in the list a message box allows the users to
select to edit the list.

I am sure these features can be written using code in mdb format, but I have
very little experience with Access and I currently have the database close to
distribution, the ability to share the database is the only problem I am
having. I do not want to have to go back and spend days re-writing code and
re-designing the database.

 

[Tom van Stiphout]

On Tue, 24 Mar 2009 09:13:03 -0700, Thorson

What is your question?

Your previous understanding is incorrect.

-Tom.
Microsoft Access MVP

 

[Thorson]

I have a 2007 Access Database ready to distribute. The back end is in a
shared folder which currently has restrictions on the permissions for
different users.

Before distributing it I wanted to do some checking to make sure things
were working. So while I sent a copy of the front end to another user (who
had read-only permissions). I had that user download the front end to their
desktop. The database works fine for both my self (full permissions) and the
user, however we cannot both open the Front end at the same time. If the
other user has it open and I try to open the front end it gives me read-only
permissions, if I have the front end open and the user tries to open it
Access states that the file is already in use.

I thought the point of splitting the database was to allow multiple users to
access the front end of the database at one time, however it is not working
out that way. I need to know how to fix this problem.
Thanks,

 

[Douglas J. Steele]

The point of splitting the database is to allow multiple users to access the
back end (i.e.: the data) at the same time.

Every user requires a minimum of Read, Write and Create on the folder in
which the back-end database exists (even if they don't have Write permission
on the database itself). Without the ability to update the locking (.LDB)
file, you get the symptoms you're experiencing.