Front End Links to Back End Tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tom - 31 Oct 2007 21:16 GMT

All,

I have created a monster. I guess it's a fairly stereotypical
situation wherein I inherited a collection of a dozen or so Excel
spreadsheets from a co-worker who got promoted out of the department.
I was given the assignment to do what the last guy did, only faster,
better, and on less pay.

Being the brilliant MBA that I am, I quickly realized there must be a
better way to answer management's constant questions about where we
are to contract, what's the upcoming delivery schedule, etc. So,
after a year or so of poring over Access for Dummies (and a few other
more respectable books) and bugging my friends who actually know what
they're doing with Access, I now have a very serviceable MS Access
2003 database that tracks deliveries against contractual requirements
and generates the most beautiful reports management could ever ask
for. They love it.

The trouble is, they love it so much that they're now bragging about
it to their manager peers on other programs and now everyone wants to
use it. Plus, my boss suddenly feels I'm too valuable to be doing
data entry each time a delivery invoice comes out or a contract
modification is released from one of our customers. Truth is, I don't
much enjoy the data entry part, either, but I'm worried about the
pitfalls of trying to distribute the app in such a way that Contracts
can maintain the contract requirements and Operations can maintain
actual deliveries so that Management can print their pretty reports.

I'll be the first to admit I am not qualified to administer a multi-
user database (if only there were a Dummies book for that topic!).
However, since Management feels the app is great (so why should they
invest in a real developer), there is no support for an upsizing
exercise to let the various departments take ownership of their
respective pieces. I'm faced with two choices: I either continue
maintaining a single file that I post to a shared drive at the end of
each day, or I bite the bullet and split the database into back end/
front end pieces. I've read everything I can find on the subject of
splitting, but I'm still worried about the repercussions of a major
malfunction once I let others into my baby.

What advice can you give me regarding permissions on:

- the back end .mdb?
- the front end .mdb or .mde (not sure which way to go there,
either)?
- the shared network folder where the various pieces will reside
once split?

Best regards,

Tom

Reply to this Message

Douglas J. Steele - 31 Oct 2007 21:56 GMT

Every user should have Change (Read, Write, eXecute and Delete) permissions
on the folder where the back-end database exists. (Okay, they don't actually
need to have Delete permission, but it can cause confusion to you later on
if they don't have it).

The front-end should be put on their hard drive. An MDE is probably the best
way, because that way they can't make changes to your application.

For more information, see
http://www.granite.ab.ca/access/splitapp/index.htm
http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> All,
>
[quoted text clipped - 47 lines]
>
> Tom

Reply to this Message

Albert D. Kallal - 31 Oct 2007 23:29 GMT

Most of the issues that will come up with a split system are outlined here:

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

Note you do often experience a slow down when you split. (eg: the
application design will become more sensitive to things that run slow).

Also, you want to "lock" the design, and keep people out of the design parts
of the appcation. So, it is a good idea to hide the ms-access interface.

You most certainly can, and should hide all of the ms-access interface. The
options to complete hide and keep people out of the ms-access interface can
easily be done using the tools->start-up options. Using those options allows
you to complete hide the ms-access interface (tool bars, database window
etc). Also, using these options means you
do not have to bother setting up security.

Try downloading and running the 3rd example at my following web site that
shows a hidden ms-access interface, and NO CODE is required to do
this....but just some settings in the start-up.

Check out:

http://www.members.shaw.ca/AlbertKallal/msaccess/DownLoad.htm

After you try the application, you can exit, and then re-load the
application, but hold down the shift key to by-pass the start-up options. If
want, you can even disable the shift key by pass. I have a sample mdb file
that will let you "set" the shift key bypass on any application you want.
You can get this at:
http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

Signature

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

Reply to this Message

Tom - 01 Nov 2007 03:42 GMT

On Oct 31, 3:29 pm, "Albert D. Kallal" <PleaseNOOOsPAMmkal...@msn.com>
wrote:

> Most of the issues that will come up with a split system are outlined here:
>
[quoted text clipped - 31 lines]
> Edmonton, Alberta Canada
> pleaseNOOSpamKal...@msn.com

Albert,

I found your excellent site earlier today after the first reply from
Doug Steele, above. Your simple explanation of the logic behind
splitting is the best I've found, and makes perfect sense of a topic
not usually explained very well. Thanks!

I plan to make the split tomorrow using all the tips you have
provided. Wish me luck!

Reply to this Message