Distributed use of a centralized access database

[Guest]

Hey all, I'm trying to setup an access database for a pricing/ordering
procedure within my company. I want to create all of the product BOMs within
the database, and provide an easy mechanism for the field to quote products
using that database. The database is in pretty good shape, and I am entering
the product data this weekend, but I still have NO idea how to go about
enabling remote access.

What I want to do is allow field people to access the forms and enter data
without having a copy of the database locally, and preferrably without having
Access installed on their machine. Is there a web interface that is
available for this type of use? I've heard some people talking about
distributing a project, but I'm not really sure if that's what I need.
Assume that the user has VPN access to the same network as the database, or
at least SSH tunnel access via selected ports (usually 80). I would much
prefer a client/server approach allowing synchronization, however, if all I
can do is package a file and distribute (without everybody having Access
installed) I'll take it. I was looking for something a bit more
sophisticated, and error proof, than just mailing out updated versions of the
mdb file.

Any advice would be most appreciated.

 

[Joseph Meehan]

Hey all, I'm trying to setup an access database for a pricing/ordering
procedure within my company. I want to create all of the product
BOMs within the database, and provide an easy mechanism for the field
to quote products using that database. The database is in pretty
good shape, and I am entering the product data this weekend, but I
still have NO idea how to go about enabling remote access.

I am going to suggest that assuming you have a god LAN you will want to
split the database and likely also implement Access security.

That will require copies of Access on their machines or that you have
the developers kit that includes a run time version of Access that can be
legally distributed to each machine to run the front ends that are on their
machines. That options cost about $700 once.

Since you now have data on different machines and I am going to guess
that the data is not the same on each of those machines, you will need to
combine most, if not all of that data onto one copy of Access. This will be
complicated if the tables and data are not formatted exactly the same, For
example 123 in one data base may be text and it may be a number is a
different machine. One may use Three fields for a name FirstName, IM and
LastName and another may put it all in one field.

I suggest you start by deciding exactly how you want the final product
configured and then how you are going to get all that data into that one
database in the same format. Your final design may not be the same as any
of the existing designs.

Next you need to decide the split. What parts of the database will be
on the "server" and will be called the Back end database from now on and
which parts will be on each user's machine and will be called the front
ends. The back end should hold all data that is shared and may be changed
by the users. It should also contain all or most data that more than one
user will need access to and may be changed by you from time to time. Most
other data that does not change or that will only be used by that particular
user should be on the Back end databases on the users machines.

For example you may have all the sales made by a unit on the back end
along with the price list. The sales may been to be shared by everyone so
they all know what has been done or pending. The price list may not be a
field they will change, but you may need to change to assure everyone has
the same current price available.

Each individual machine may have something about your company like
addresses that does not change or even product descriptions etc. You may
want each user to be able to store personal information about customers like
their kids names or shared information about sports teams or you may want to
put this on the server so everyone will have this information.

This is an art form and a science to get this part of the planning
designed and will be an ongoing job and should include the users in the
planning.

Access works best if it does not need to move a lot of information over
the LAN which means static data is best kept on the front end databases.
Also kept on the front end machines will be most forms, reports queries etc.
This will allow the whole system to work faster and in some cases allow for
customization of some forms reports etc.

This may seem like a lot of work and off the point of the question you
were asking, but it is very important that this part of the job be done
first and right.

Next is the mechanics of setting up the back end on the server, dumping
in the data and putting the front end copies on each user's machines and
assuring that the links work. Access has a built in database splitter that
may make this part of the job (moving from a single database with all the
data and forms etc. to two databases a front end and a back end.) easier.
Look under the Tools menu for it.

You may also want to look into user level security to protect the
database and data before you finish.

I suggest you start by reading
http://support.microsoft.com/default.aspx?scid=kb;[LN];207793

Access security is a great feature, but it is, by nature a complex product
with a very steep learning curve. Properly used it offers very safe
versatile protection and control. However a simple mistake can easily lock
you out of your database, which might require the paid services of a
professional to help you get back in.

Practice on some copies to make sure you know what you are doing.

Splitting a database can be a big job, but done right everyone will
thank you and wonder how they did their jobs without it.

Note: back ups become more important here. If you LAN does not support
automatic backups you should provide a method of backing up the data, even
if that means you do it manually.

 

[Van T. Dinh]

1. I don't use this but check Access Help topic "About data access pages".

2. You can, of course, write your own Web application that uses your
database as the back-end for data only.

3. Even with a good network (at best, you look at 1 or 2 MBits/sec), it is
still not an optimal solution with remote Access Front-End accessing a
cnetral JET Bck-End. My preferred method is to use Citrix or Windows 2003
Terminal Server so that remote users can access you pricing / ordering
application remotely in this case.

See Albert Kallal's article:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html