Design Changes To Objects In Shared MultiUser Database

[Mike Wiseley]

We recently converted our department wide shared Access97 database to
Access2K. We used to be able to open various reports in design mode and make
changes to the design (or create new reports) even while other users in this
shared database might be using other, unrelated reports.

Now, in Access2k, it appears that whenever you do not have exclusive access,
you cannot make any design changes to reports. It seems that the presence
of any other person in the database prevents us from working the way we did
before.

Am I missing something or is this a design feature of Access 2000? Seemed
like the old A97 required exclusive access at the object (report) level --
whereas Access 2000 does this exclusive access issue at the entire MDB
level.

Is there a work around to this in Access 2000 or will we have to just accept
this situation?

[Allen Browne]

A2000 changed to a monolithic save.

The best solution is to split your database into back end (tables only) and
front end (all other objects, and linked tables). Place the b.e. on a shared
resource (server). Give each user their own local copy of the f.e. This
allows you to perform upgardes on your local copy, and then distribute the
upgrade to other users.

Although it was possible to get away without doing this in A97, it was
subject to corruption, so splitting is the best approach in all versions.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Mike Wiseley" <mi**********@g te.net> wrote in message
news:vX******** *********@nwrdd c02.gnilink.net ...

We recently converted our department wide shared Access97 database to
Access2K. We used to be able to open various reports in design mode and make changes to the design (or create new reports) even while other users in this shared database might be using other, unrelated reports.

Now, in Access2k, it appears that whenever you do not have exclusive access, you cannot make any design changes to reports. It seems that the presence of any other person in the database prevents us from working the way we did before.

Am I missing something or is this a design feature of Access 2000? Seemed
like the old A97 required exclusive access at the object (report) level --
whereas Access 2000 does this exclusive access issue at the entire MDB
level.

Is there a work around to this in Access 2000 or will we have to just accept this situation?

[David W. Fenton]

ab************* **@bigpond.net. au (Allen Browne) wrote in
<Qo************ ********@news-server.bigpond. net.au>:

Although it was possible to get away without doing this in A97, it
was subject to corruption, so splitting is the best approach in
all versions.

And it was also the best approach in A95 and in Access 2.

Indeed, anyone who ever bothered to read the Access help file on
setting up a multi-user database, or browsed the MS Knowledge Base
for articles on the subject, would have found that splitting data
and code was recommended almost 10 years ago.

So, anyone who is *not* splitting their multi-user databases for
production use really isn't competent in using Access at all.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Mike Wiseley]

The tone of your response is a sharply worded, David, but thanks for your
comments anyway. In this case, the front end reports and queries are already
split from the back end SQL server database which is located on a web server
at a different location. There is also second back end Access mdb that
houses some of the local data tables.

However, we have several folks using the same single front end mdb file on a
shared network for running queries and reports. Of the 50 or so reports,
each user really only opens the 5-6 reports that individually speak to their
needs (they are segmented by product line). The issue was that we used to be
able to edit or even create new reports and work in the design mode of
certain reports (if they had not be opened by a particular user already for
their own operational needs).
"David W. Fenton" <dX********@bwa y.net.invalid> wrote in message
news:94******** *************** ****@24.168.128 .86...

ab************* **@bigpond.net. au (Allen Browne) wrote in
<Qo************ ********@news-server.bigpond. net.au>:

Although it was possible to get away without doing this in A97, it
was subject to corruption, so splitting is the best approach in
all versions.

And it was also the best approach in A95 and in Access 2.

Indeed, anyone who ever bothered to read the Access help file on
setting up a multi-user database, or browsed the MS Knowledge Base
for articles on the subject, would have found that splitting data
and code was recommended almost 10 years ago.

So, anyone who is *not* splitting their multi-user databases for
production use really isn't competent in using Access at all.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Pieter Linden]

"Mike Wiseley" <mi**********@g te.net> wrote in message news:<Uy******* ***********@nwr ddc03.gnilink.n et>...

The tone of your response is a sharply worded, David, but thanks for your
comments anyway. In this case, the front end reports and queries are already
split from the back end SQL server database which is located on a web server
at a different location. There is also second back end Access mdb that
houses some of the local data tables.

However, we have several folks using the same single front end mdb file on a
shared network for running queries and reports. Of the 50 or so reports,
each user really only opens the 5-6 reports that individually speak to their
needs (they are segmented by product line). The issue was that we used to be
able to edit or even create new reports and work in the design mode of
certain reports (if they had not be opened by a particular user already for
their own operational needs).

Mike,
how about something like a compromise. Go to Tony Toews's site and
download the Front End Updater code. Then you could have front ends
that are customized for each group (if I'm reading your post right).
Then you reduce the risk of your database corrupting. If you really
wanted to include a report that someone built into the core set or
whatever, you could include code to import a list of objects into your
front ends... (but I think that's what Tony's code does...)

http://www.granite.ab.ca/accsmstr.htm

See Auto FE Updater...

It *might* be what you're looking for.

Peace,
Pieter

[David W. Fenton]

mi**********@gt e.net (Mike Wiseley) wrote in
<Uy************ ******@nwrddc03 .gnilink.net>:

The tone of your response is a sharply worded, David, but thanks
for your comments anyway. In this case, the front end reports and
queries are already split from the back end SQL server database
which is located on a web server at a different location. There is
also second back end Access mdb that houses some of the local data
tables.

However, we have several folks using the same single front end mdb
file on a shared network for running queries and reports. Of the
50 or so reports, each user really only opens the 5-6 reports that
individually speak to their needs (they are segmented by product
line). The issue was that we used to be able to edit or even
create new reports and work in the design mode of certain reports
(if they had not be opened by a particular user already for their
own operational needs).

And that was never a good idea, ever, and it was always at variance
with the standard operating practices recommended in the MS help
files and Knowldege Base.

You were doing it wrong all along, so the fact that is broken now
just shows that you had an unsupportable architecture on the front
end.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Larry Linson]

"Mike Wiseley" wrote

The tone of your response is a
sharply worded, David, . . .
Mike, David has never been one to "mince words". And while it may be a
startling way to do it, it is clear that he got your attention with those
"sharp words".
However, we have several folks using
the same single front end mdb file on a
shared network for running queries and
reports.

But, I'll agree with him that there is an overwhelming mountain of advice
against multiple users logging in to the same front-end or monolithic
database. There is SO much that it is hard to imagine anyone frequenting
this newsgroup, as I know you do, who hadn't seen it time after time.

It is possible to have multiple users concurrently using the same front end
without corruption for a long time . . . that's often reported by people who
ask here "why did my database suddenly start corrupting so frequently?" And
the answer is that some little change somewhere in the database, or outside
it in a .DLL, was "the straw that broke the camel's back".

But, it is bad practice to have multiple users concurrently in the same
front end or monolithic database, and that has been no secret ever since I
began working with Access multiuser and Access client databases back in
1994.