Macro can't change report design when multiple users are logged on

[Vivian Carroll]

DESCRIPTION OF PROBLEM:

I created a "Contractors" database application using Access 2000. It links
to dept.mdw. I have a Contractor Query form where the user types in what to
search by (e.g., "ABC Corp"). The procedure takes the user's input, runs the
appropriate query, displays the data in the "Contractors Report" and changes
the label at the top of the report so that it says "Listing of Contractors
with "ABC Corp" in the Name".

All works just fine when only one person is in the database. However, when
two people are in the database, the report displays the correct records but
the title of the report (label at the top of the report) does not change. My
assumption is that Access has some "built-in protection feature" that won't
allow the report design to be modified if more than one user is in the
database.


QUESTION:

How do I get around this problem?

PERMISSIONS:

The "engineers" group that I was testing this with has these permissions for
that report: Open/Run, Read Design, and Modify Design. (Adding "Administer"
didn't help. The last 4 choices are grayed out: Read Data, Update Data,
Insert Data, and Delete Data.) (I tried making the code create a copy of
the "Contractors Report" and modify that, but the same problem still
happens-the label on the report copy does not get changed.)

Under Tools, Options, Advanced tab, "Default Record Locking" = "No Locks"
and below the following is checked "Open databases using record-level
locking."

TIA,

Vivian Carroll

 

[Cheryl Fischer]

Vivian,

In Access 2000 and newer versions, it is no longer possible to make design
changes to objects when others have the database open.

It sounds as if all of your users are opening the same copy of the database.
In addition to the prohibition against making design changes while others
have the database open, you are also opening yourself up to potential
corruption of the database with this method of deployment. A "best
practice" is to split your database into a Front-End (containing Forms,
Queries, Reports, Modules and Macros - and Links to tables in a Back-End)
and a Back-End (containing shared Tables only). Each user should have a
copy of the Front-End installed on his or her own computer. Using this
method of deployment, when the report is run, each user should be able to
make the appropriate design change because he or she is changing their own
copy of the report.

hth,

 

[Vivian Carroll]

Thanks Cheryl,

I read about the front end/back end idea but didn't try it because the
database forms etc may undergo continuous improvement/additions. If I put
the front end on 30 computers, that makes a lot of work each time I make a
change! Do most people totally develop the application before implementation
and then not revise it?

If I leave everyone using the same copy of the database, does it still make
sense to split the tables out from the rest? Would that help with the
possible corruption issue?

Vivian

 

[Cheryl Fischer]

I read about the front end/back end idea but didn't try it because the

database forms etc may undergo continuous improvement/additions. If I put
the front end on 30 computers, that makes a lot of work each time I make a
change! Do most people totally develop the application before implementation
and then not revise it?

Of course not. For most developers, there are always going to be tweaks,
upgrades and modifications necessary. Sometimes, in the businesses I work
with, upgrades are an "asap" requirement. I cannot imagine having to try to
get 30 users to log out of a database in the next five minutes because a
senior partner wants a new report! Some developers put a short-cut to a
BAT file on users' desktops so that they can copy a new version of the
Front-End to their computers when they are notified that there is one
available. I have worked in one firm with a very sophisticated App Launcher
application which required no user intervention at all. In addition, Access
MVP Tony Toews has written and made available to all an AutoUpdaterFE
program, which is available at:

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

If I leave everyone using the same copy of the database, does it still make
sense to split the tables out from the rest? Would that help with the
possible corruption issue?

Perhaps minimally. But it won't do a thing to eliminate the unnecessary
network traffic caused by running a single copy of a front-end database from
the server - not to mention the poor performance as compared to users
running the front-end from their own computers. And, leaving that single
copy of the front-end on the server for all users to open and run will still
leave you with your original problem - that design changes may not be made
to objects in a database if that database is already opened by another user.

 

[Vivian Carroll]

Thanks so much Cheryl. Your information is very useful for me!

Vivian