Multiple Users from differant sites accessing the same Database

[Matt]

I am currently in the process of designing a database and my knowledge of
Access 2002 is not too strong. We have 26 remote sites which provide
Employment Services to the public, the database I'm wishing to create will
record all job applications. Currently we have a database for this in each of
our agencies but we would like to host one single version on our servers. Is
it possible to assign a username and/or password which will apply a filter so
that when they open the database only their site information is displayed.
For example, if someone from site X has a site code of HEAZ, they open the
database, they type in the username and/or password associated to the HEAZ,
it shows them any previously entered HEAZ information as well as assigns the
HEAZ sitecode to all entries they make for future referance and sorting.

I hope this makes sense, If anyone has any ideas or suggestions, or would
like further information I would be more than glad to supply it.

Thankyou

 

[Tim Ferguson]

We have 26 remote sites which
provide Employment Services to the public, the database I'm wishing to
create will record all job applications.

Depending on how remote you mean, Access itself might not be the best
choice for this. It's fine for half-a-dozen users on a LAN; but
performance will be extremely disappointing over huge distances. There is
a great web page about this but I'm afraid I can't find the URL just now.

You might want to think about a proper web page/ IIS/ ASP kind of
approach. No problem using a single Access database behind it, but you
have quite a lot of technology to learn!

Is it possible to assign a username and/or
password which will apply a filter so that when they open the database
only their site information is displayed.

All this is eminently do-able, but again is not necessarily trivial.

Hope that helps -- and that it's not too depressing!

B Wishes



Tim F

 

[Matt]

Thankyou for your reply.

At the moment all of our sites are connected to us through ADSL or via
Fibre, so hopefully bandwith won't be an issue. If you do happen to stumble
across that site that would be great, if not, I will try look at a differant
approach.

Thankyou. again.

 

[Rick Brandt]

Thankyou for your reply.

At the moment all of our sites are connected to us through ADSL or via
Fibre, so hopefully bandwith won't be an issue. If you do happen to
stumble across that site that would be great, if not, I will try look
at a differant approach.

It most definitely WILL be an issue. The very best WAN connection is still
MUCH slower than a LAN and Access struggles even on a poor LAN.

What is your connection capable of? 1Mb at the most? A crappy LAN is ten
times that and a decent one is 100 times that.

Access cannot be used in file share mode over such a connection. Don't
waste your time.

 

[Matt]

We are currently running a Records Management Database via Citrix to our
remote sites which can has on average 40 Users at one time just on that
database alone and we have not had any reported slow-downs or problems.

If anyone has some positive feedback it would be greatly appreciated. All
I'm really looking for is to be able to assign a hidden attribute (Site Code)
to each entry a user makes. A username\password system would be handy as
then we wouldnt have to worry about another user from a differant site
accidentally entering data to an incorrect site.

Or, is it possible to have a drop down box which will assign the Site Code
to each entry they make?

Thankyou.

 

[John Vinson]

We are currently running a Records Management Database via Citrix to our
remote sites which can has on average 40 Users at one time just on that
database alone and we have not had any reported slow-downs or problems.

If anyone has some positive feedback it would be greatly appreciated. All
I'm really looking for is to be able to assign a hidden attribute (Site Code)
to each entry a user makes. A username\password system would be handy as
then we wouldnt have to worry about another user from a differant site
accidentally entering data to an incorrect site.

Or, is it possible to have a drop down box which will assign the Site Code
to each entry they make?

Citrix is indeed a good way to handle this. One of my clients is doing
so.

He has multiple users logging on to a Citrix server. Each of them has
a personal folder containing the frontend (a .mde file, though a .mdb
would work as well) Access database; all of these are linked to the
shared backend database on the same server. He's done it running the
Access instances on the Citrix server, and on a different machine on
the same LAN - no major differences. He's implemented Access security
on the database so that each user is identified; it would be very easy
to have each frontend .mdb file have a site code default of its own,
if that were necessary.

As noted elsethread, what you do NOT want to do is have the Access
backend database on one end of the remote network, and an Access
frontend on the other. However, if the "wire" is only transmitting
terminal information (as it is with Citrix) this is a very practical
approach.

John W. Vinson[MVP]

 

[Matt]

Thankyou for your reply.

If anyone out there is able to help me with the direct question - "All
It would be greatly appreciated.

 

[Lynn Trapp]

Matt,
The ideal solution sould be to implement Access's user level security, but I
have never implemented it in a Citrix environment and don't know how well,
if at all, it would work. You may need to create a table in the database
that stores the site code and a password associated with it. Then create a
form that the users see when they open the data base and allows them to
enter the site code and password. Then, that value could be used throughout
the session. The hard part is that you would need to add a "Who" field to
all your tables and have your application enter the site code in that field
whenever a user creates a new record.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

 

[John Vinson]

If anyone out there is able to help me with the direct question - "All

It would be greatly appreciated.

Here's one scenario:

Each Citrix client is connected to a separate, Windows Security
protected folder on the Citrix server; they'll only be able to see
their own folder. In each such folder there is a customized Access
frontend database.

The backend database has a field SiteCode (don't use blanks in
fieldnames!) field in each table where you want to track the data.

Each frontend has Forms to update the data in the tables. On each Form
there is a textbox bound to that table's SiteCode field; the
properties of the textbox are:

Enabled = No
Locked = Yes
Visible = No
TabStop = No
DefaultValue = <that user's Site Code>


John W. Vinson[MVP]

 

[SAM]

You could use CurrentUser() with some sort of Site Code tacked on that you
parse.