Private tables in Access 2007
Dear Experts,
In order to allow multiple users to edit the same table, I am trying to split my database into frontend/backend. My question is, if I kept some tables as private, can they act as local copy, and be manipulated by the user at that PC? What I am trying to do is to allow multiple users to have almost like a local copy of a portion of the main table, then only edit the main table after the user is done looking at the data. For that, I wanted each user to have local image of the main table, and was wondering if a private table would act as such.
Or am I approaching this in the wrong way? Please advise.
In order to allow multiple users to edit the same table, I am trying to split my database into frontend/backend. My question is, if I kept some tables as private, can they act as local copy, and be manipulated by the user at that PC? What I am trying to do is to allow multiple users to have almost like a local copy of a portion of the main table, then only edit the main table after the user is done looking at the data. For that, I wanted each user to have local image of the main table, and was wondering if a private table would act as such.
Or am I approaching this in the wrong way? Please advise.
ASKER
Dear mbizup,
Actually, all users are allowed to access the main table. It is just that each one looks at it in different way, so I wanted to create a local copy of a section of table for each user, modified slightly differently for each case.
Once the user looks at the data, every one of them can update the main table.
Actually, all users are allowed to access the main table. It is just that each one looks at it in different way, so I wanted to create a local copy of a section of table for each user, modified slightly differently for each case.
Once the user looks at the data, every one of them can update the main table.
What are the modifications you would have to make to the table for each user? You can do a lot through a form interface to make it *appear* as though the table is customized for each user without the complication of grabbing the latest data to local tables and updating the shared table when done.
Having different versions of tables for different users in multiple copies of the database would also greatly complicate improving and maintaining your database.
My gut feeling is that you should completely rule out other options first.
Having different versions of tables for different users in multiple copies of the database would also greatly complicate improving and maintaining your database.
My gut feeling is that you should completely rule out other options first.
I've seen and done that type of setup in a couple of cases:
1. Main/subform combinations where the user wants to roll back all the records as a unit (you copy the main record and subform records to local tables, update, the copy back or simply drop to cancel the edit).
2. SQL backend with heavy reporting - Often it's faster to pull data in and report off local tables rather then going against SQL.
But I'm with mbizup on this one; if you don't absolutly have to, then don't.
Jim.
ASKER
Dear mbizup & JDettman,
The modification is every user looks at different subset of main table. It makes it a lot faster for them to traverse the only necessary data, then make changes. To simplify the forms, I created a subtable for the user, but everytime another user tries to get a different subset, the subtable is erased.
How can I set up a local table? Is it done by first spliting the Database into frontend/backend, then creating tables in front end? Would that table act as a local table for each user?
The modification is every user looks at different subset of main table. It makes it a lot faster for them to traverse the only necessary data, then make changes. To simplify the forms, I created a subtable for the user, but everytime another user tries to get a different subset, the subtable is erased.
How can I set up a local table? Is it done by first spliting the Database into frontend/backend, then creating tables in front end? Would that table act as a local table for each user?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you, that makes a lot of sense.
In other words, give users access to tables and queries through a form based user interface, and use code to determine which users or user groups can see, add or edit the data (who, what and when).