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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

If you have data that should be accessed only by specific users, you should store it in the shared back-end, but use user-level security to control who has access to what data through your user interface.

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).
yballanAuthor Commented:
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.
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.

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

  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.


yballanAuthor Commented:
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?
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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? >>

  Yes, that's one way to do it.  A DB should be split anyway for a number of reasons and this is one of them.

  If you decide to create the temp tables in the FE, compact on close should be set.  And yes, the tables will appear as local tables.

  The other approach (which involves a little more work) is to create a scratch DB and create the tables in there.  When your done, then you simply delete the file.  Here the tables would end up as linked.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
yballanAuthor Commented:
Thank you, that makes a lot of sense.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.