Multiple forms, multiple users

[Marie-Lynn]

Hello,

Preamble:
I have created several tables and forms within one database. These
databases are being used by my organization to capture various pieces of
information. When certain users use the forms associated with this database,
certain fields are always going to contain the same information (e.g.
'employee name,' 'program name'). Addidtionally, I would like certian users
to be able to browse the entire database, while other users should only be
able to browse information that they entered.

Questions:
Is there anyway to define specific forms to certain users? (If I could,
somehow, have a form specific to a user on their local machine linked to the
database on the server, that would be ideal. I have not yet found a way to
export a form from the database and still have it linked)

Is there anyway to set what information can be seen be specific users?

If multiple users are using the same form, will Access allow concurrent
writing to the database, or will will the multiple users be on a write-only?

 

[John W. Vinson]

Hello,

Preamble:
I have created several tables and forms within one database. These
databases are being used by my organization to capture various pieces of
information. When certain users use the forms associated with this database,
certain fields are always going to contain the same information (e.g.
'employee name,' 'program name'). Addidtionally, I would like certian users
to be able to browse the entire database, while other users should only be
able to browse information that they entered.

Questions:
Is there anyway to define specific forms to certain users? (If I could,
somehow, have a form specific to a user on their local machine linked to the
database on the server, that would be ideal. I have not yet found a way to
export a form from the database and still have it linked)

Is there anyway to set what information can be seen be specific users?

If multiple users are using the same form, will Access allow concurrent
writing to the database, or will will the multiple users be on a write-only?

Access databases can theoretically be shared by 255 concurrent updating users.
In practice more than 50 or so can get tricky.

You should CERTAINLY use a "split database" structure, though, to avoid bad
performance, bloating and corruption. This uses one .mdb file (or SQL/Server
instance, or MySQL database, or any of a lot of other choices) as a "backend"
to hold the tables; each user will get their own "frontend" containing the
forms, reports, queries, and links to the tables in the backend. Tools...
Database Utilities... Database Splitter Wizard will start the process for you;
see the references at
http://www.granite.ab.ca/access/splitapp.htm
for a thorough discussion.

Given a split application, you could (at the cost of a lot of work for you and
a maintenance headache) give each user a custom-tailored frontend with only
the forms and reports that person needs, and queries tailored to return only
the data they should see.

Alternatively, with A2003 and earlier (the feature was removed in 2007), you
can implement Access Workgroup Security and define security groups, with each
group having permission to only certain items. See the Microsoft Access 2000
Security FAQ:

http://support.microsoft.com/kb/207793/en-us

 

[Marie-Lynn]

Thank you! This is very useful! It will take time to impliment, but this
sounds like the solution I was looking for!

 

[Tom Wickerath]

To add some to John's answer, you can also use the "roll-your-own" method of
security to control who gets to browse which records. Either method that you
use (ie. the "roll-your-own") or the build-in Access User Level Security
(ULS) are not all that secure, but they can be used with honest users who
have no intentions of hacking your database. In addition, the "roll-your-own"
method continues to work just fine in Access 2007, whereas the built-in
Access ULS was removed from Access 2007. Here is an broad overview of how one
creates the "roll-your-own" method:

1.) You start with a split application (see the link below for a definition
of the terms split, "front-end" or "FE", and "back-end" or "BE"). The BE
includes a table of people with a field that contains their Windows NTLogin
username. It also includes a field that defines their access rights (ie.
Admin, User, ReadOnly, etc.).

2.) In the FE application, you have VBA code that runs during the startup of
the database that queries the user's computer for their NTLoginID, ie their
Username that they use to log in with. This value is then looked up in the
table of people, and their corresponding access rights are determined. If no
records are returned (because their NTLoginID is not in the people table),
then you throw up a message indicating that they must contact you, followed
by a DoCmd.Quit to close Access.

3.) Once you have their NTLoginID and/or Access rights determined, you can
store these values in global variables. You could, for example, also store
the NTLoginID in a field for each record that this user creates. Then, a
simple query would include a criteria for NTLoginID if this person was a
normal user. On the other hand, if the person is an Admin, the NTLoginID
would not be included as a critieria.

That's a general high-level overview of the process. If you are interested
in pursuing the "roll-your-own" method, I can help with more specific details.

On the first part of your question, see this article:

Implementing a Successful Multiuser Access/JET Application
http://www.accessmvp.com/TWickerath/articles/multiuser.htm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[John W. Vinson]

Either method that you
use (ie. the "roll-your-own") or the build-in Access User Level Security
(ULS) are not all that secure, but they can be used with honest users who
have no intentions of hacking your database.

"Locks keep out honest people"... :-{(

 

[Marie-Lynn]

Thank you all. I have implimeted the methods that you speak of here and they
work flawlessly.