change form

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

when i try to change a form and other users are in this
form it tells me that i cannot open it exclusive and may
have to make my changes later. my problem is everytime i
want to change this i have to wait til everyone leaves
work and make changes after hours. is there a way to do
this while im at work.
 
You should consider splitting your database. Here is how I do it.

Depending on your version look for Database Splitter under Tools | Add-ins
or Tools | Database Utilities. This utility will break your database into a
front-end/backend. The front end has all your Queries, Forms, Reports,
Macros and Modules. The backend file has all your tables. Once split you
will also need to know about the Linked Table Manager which is in the same
location you found the splitter.

Each user has their own copy of the front-end which is copied from the
server whenever they login. Each front-end is linked to the same backend
file so all users are accessing the same data.

This way you can work on a front-end working mdb on your PC anytime you
want. When you are done, copy it to the server as a master copy and email
everyone about the changes and that they should logoff/login to get the
updated version. Or for less important updates don't even email them and
they will get the update tomorrow when they login.

You still need to kick everyone out when making changes to the
tables/relationships but that is usually much less often.

Post again if you need more details.
Tony
 
Dan,

Sounds like you and your users are all using a single copy of the database,
which is not a good idea. I believe Access 97 was the last version in
which design changes could be made to a form while others had it open. Due
to the potential for corruption, later versions of Access do not have this
capability.

Recommended "best practices" are:

1. If you have not already done so, split your database into a front-end
(containing all Queries, Forms, Reports, Macros and Modules) and a back-end
containing shared Tables. The back-end resides on the server. A copy of
the front-end should be deployed to each user's local hard drive. Each
front-end will have links to the tables in the back-end.

2. A copy of the most current front-end should be kept on the server. You
should be doing your design work on a copy of the most current front-end on
your own computer. When you have tested all of your design changes, you can
then copy your new front-end to the server.

3. Updating your users' copies of the front-end can be relatively easily
done. Check out Access MVP Tony Toews' Auto-Updater at:

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

I realize that this sounds like a lot of work, but you and your users will
be happier in the long run.

hth,
 
I myself do not like puting different interfaces on users
desktops. It makes it quite difficult to issue updates.
My suggestion would still be to spit the database,
leaving one with tables only and the other with
everything else. However the difference would be that
both are on the server and only a link to each is given
to each desktop.
Now to your problem. I always have a backup copy of my
database in another area on my desktop so that I can have
test data in the table mdb file and the working version
linked to it. I make all of the changes during work
hours and when they go home or early in the morning, I
post the new interface so the next time that they log in,
they get the changes. Remeber to update the linked
tables if you choose this route.
 
This still requires kicking everyone out if you need to make changes during
the day or if there are people working late in the evening. Also execution
of the mdb still occurs on the PC requiring that the entire front end get
copied down to memory to run, potentially making it too slow. And managing
linked tables can be a pain sometimes depending on if you link from multiple
files or if there were recent table changes. Lastly even if you are not
making changes I have had problems with users getting messages like "Someone
has made changes since you last opened the form. Do you want to save your
changes?" this can get confusing and annoying to users.

If you simply add a file copy routine to everyone's login script you are
good to go. Everyone is so used to restarting their PC anyway that having to
just logoff/login to get changes would be considered a welcome change. I
have also created functions where people can update from within the db - it
calls a vbsctipt which has a 2 second wait for the db to close, copies the
front-end file and then restarts the db. This is be far the easiest and
fastest (this one doesn't work well in Access 97 or on slower PCs).

I would be happy to provide samples of my scripts and functions.

Tony
 
Back
Top