By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,137 Members | 2,282 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,137 IT Pros & Developers. It's quick & easy.

MS Access filter linked tables, front and back end. Can someone help?

P: n/a
Hello there.

I have a question somewhat related to this topic, and I don't know
where else to go. I hope somebody can help. I've created a database
in access, that I'd like to share with less than 10 users. I'd like
each user to see only some of the records in the database. So for
example, if I have eight users in eight countries, I want each of them
to only see information for their country.

I was told that the easiest way to do this is to split my database into
frontend and backend. If I have ten copies of the database, I can put
filters in relevant queries and reports, so that each user only sees
the stuff that's applicable to his region.

There are two problems with this approach. First, I don't know enough
about access to know what people mean by front and back end. I
somewhat understand that it would involve creating multiple copies and
linking tables, but don't know how it will affect my database. Second
problem is the filtering. i have several dozen each of tables,
queries, and forms. putting a filter in each of them, and then
repeating it ten times will take forever.

My question is this: are there any easier ways to acheive what I need?
Perhaps I could filter just the main table that's being linked? Is
there some VBA code that can achieve the same goal? Keep in mind, I
know very little about VBA...

Thanks in advance for your help.

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Often good practice to split a database into Front End and back end, and
essential if it is to be used on more than one computer.

Front End is what the user sees. It should contain forms, and reports. It
will also contain queries, code and linked tables. It may also contain a few
non linked tables.
Back End contains only tables.

You can link the tables from the Back End to the Front End so that the user
can see (and possibly modify, if allowed) the information held in the tables
and to all intent he has a complete database, but the critical thing is the
information is only held on one computer.

I am sure other people will come up with ideas of restricting information to
certain users, but my approach would be this.
On the most important table (is it customers or members or something like
that?) I would add a field PermittedUsers. This would contain an integer
indicating which user(s) could view the record.
User Number
1 1
2 2
3 4
4 8
5 16
6 32
7 64
etc etc
So to allow user 6 to view the information, the PermittedUser field would
hold 32
to allow users 3 and 5 to view the information the field would hold 20 (4 +
16)
to allow users 1, 6 and 7 to view the information the field would hold 97 (1
+ 32 + 64)
Each user would have a individual table in their front end database
containing their user number. Then you could have some fun with code to
apply a filter on the PermittedUser field to see whether that record was
displayed. Needs some coding

HTH

Phil
"Julia" <mo******@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
Hello there.

I have a question somewhat related to this topic, and I don't know
where else to go. I hope somebody can help. I've created a database
in access, that I'd like to share with less than 10 users. I'd like
each user to see only some of the records in the database. So for
example, if I have eight users in eight countries, I want each of them
to only see information for their country.

I was told that the easiest way to do this is to split my database into
frontend and backend. If I have ten copies of the database, I can put
filters in relevant queries and reports, so that each user only sees
the stuff that's applicable to his region.

There are two problems with this approach. First, I don't know enough
about access to know what people mean by front and back end. I
somewhat understand that it would involve creating multiple copies and
linking tables, but don't know how it will affect my database. Second
problem is the filtering. i have several dozen each of tables,
queries, and forms. putting a filter in each of them, and then
repeating it ten times will take forever.

My question is this: are there any easier ways to acheive what I need?
Perhaps I could filter just the main table that's being linked? Is
there some VBA code that can achieve the same goal? Keep in mind, I
know very little about VBA...

Thanks in advance for your help.

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.