Question about multiple users

[thenoisydrum]

Good morning all,
I am designing a fairly simple database for a client. Top level, they have asked for a couple of forms to be designed for inputting data into tables.
They have a set number of admin people that they would like to be able to have access to all tables within the database - the whole shebang.
They also have a number of users, that will be responsible for simply entering the data into the forms.

My client would like it to be that user A (for example) can only see the records that they have entered.

Does MS Access have the ability to set this type of thing up? I don't believe that it does. Please let me know your thoughts.

I think that the best way to manage this would be to have a central database with the tables for users A - H (for example).
These tables would be linked to 8 databases that live on the 8 user's individual 'drives'.
That way, each user would only be able to see the records that they entered.
Each of the 8 source tables could be added to a 'master' table every week, or what ever.

The central database would live on a 'drive' where the admin users could open it and view and edit the objects and do all the analysis.

Thanks in advance for your help

Drum

 

[arnelgp]

I think that the best way to manage this would be to have a central database with the tables for users A - H (for example).
These tables would be linked to 8 databases that live on the 8 user's individual 'drives'

you use only 1 database.
you can save the Computer Name/IP Address of the user when a record is saved.
you use a Query and filter the Computer Name or IP Address for that specific user.

 

[Gasman]

I would just have a field in each table that stores the userID of the person who created the record.
Then filter on that with a query.

Then if someone leaves, you can just amend that field for another user, who is to take over his/her work.

You could also have security levels that only allow thenm to see what they need to see, and that might cut down on the number of tables that need a UserID field for security purposes.

You would have A FE on each user computer and a BE on a server PC.

 

[CJ_London]

Does MS Access have the ability to set this type of thing up? I don't believe that it does.

Of course it does

You also need to be clear about your setup - are all users working in the office on a LAN? Do some work from home? etc

In any situation- and definitely multi user - the access app should be split - tables in the BE (back end) and queries/forms/reports/modules in the FE (front end).

The BE should be located on a server where all users can see it. For the FE, all users should have a copy on their local machine and it is linked to the BE.

with regards users only seeing their own records, this is achieved by having a 'userID' field in your table(s). The userID field is set when the user logs in - either with your own bespoke login process or by using the windows login name. As each user creates a new record, this field is populated with that ID.

All forms/reports then use a query as its recordsource that filters the records to only show those with that users userID.

The maximum quantity of data that can be stored in a single access file is 2Gb. Depends on how large your records are but this will allow for millions of records. So unless you anticipate 10's of millions of records, an Access BE will be fine, otherwise look to use sql server/express to store your data

The central database would live on a 'drive' where the admin users could open it and view and edit the objects and do all the analysis.

Be clear about terminology - objects such as forms/reports are not data, so are not a database. If admin want to edit a form/report they should be working on a copy of the FE as the development copy (ideally linked to a development copy of the BE) and once happy that the edits are complete, release the FE to 'production' i.e. supply each user with the new copy of FE.

There are many ways to do this automatically - just google something like 'how to distribute access front end to multiple users'

 

[GaP42]

The input tables should be shared by users - not multiple versions. Each row in the table can have a column identifying the user (userID) that performed the input. You need a Login form to capture the userID.
As advised by others - one BE database, multiple copies of the same front end for your users, connecting to that BE database.
Those users with access to "all tables" should really only have access to the same forms but not limited by userID

 

[bob fitz]

Also, keep in mind that you would need something other than Access for the backend if the network is not a wired network.

 

[Pat Hartman]

One of the things you will need to account for is allowing certain Administrators the ability to see all data or all data in their department or data for a specific user "this time" so the queries will always need compound conditions.

Where UserID = Forms!frmLogin!UserID OR Forms!frmLoginUserTyp;e = "Admin" --- something like that. Or you might want to have the Admin user specify an additional UserID when he logs in and so he will see only that user's data. You need to clarify with the system owners how to handle that. You also need to handle how to pass records to different users, either en masse or one at a time. Get all the details worked out and we'll help you to put it together.