473,320 Members | 2,146 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
1 4273
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Random Person | last post by:
Does anyone know how to use VBA to relink tables between two MS Access databases? We have two databases, one with VBA code and the other with data tables. The tables are referenced by linked...
4
by: intl04 | last post by:
How do I create a data input form in Access that is external to the Access database to which it's connected (if that's possible, which I believe it is)? For example, if someone clicks on an Access...
5
by: dave71 | last post by:
Hi Could someone please advise me how to remove the schema name from linked tables within Access. For example when I connect to a Oracle database via Microsoft ODCB for Oracle the list of...
3
by: Alienz | last post by:
Hey guys heres my network question: 1- when I selet Tools-startup and limit the access to access, it applies to all of the users on the network who are accessing the database through a network...
7
by: ddsvi78 | last post by:
I am a complete idiot when it comes to access. Now that said, I work for a computer security company and one of our customers came to us with an access problem. They had been running fine for a...
8
by: rdemyan via AccessMonster.com | last post by:
I've converted my application from A2K format to A2003 format. I tried to follow Allen Browne's protocol in getting my app into A2003 (although I was unable to find informtion on the conversion...
5
by: Kip | last post by:
I have an office with approx 8 people. I have used Access with a Form on my personal PC for client records. I was wondering if I could put the Access table on a server and put shortcuts on each...
6
by: tony.abbitt | last post by:
I have recently installed Office 2007 (SP1) retaining the previous installation of Office 2003. I have converted an Access 2003 database to Access 2007. The database contains the VBA code...
10
by: Les Desser | last post by:
In article <fcebdacd-2bd8-4d07-93a8-8b69d3452f3e@s50g2000hsb.googlegroups.com>, The Frog <Mr.Frog.to.you@googlemail.comMon, 14 Apr 2008 00:45:10 writes Thank you for that. It was very...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.