473,322 Members | 1,403 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,322 software developers and data experts.

Passwords & Forms - Access 2000

I am working on a project for my company, which has a structure of six
major areas. Certain people need to have access to all of the data,
whereas other people only require access to the information for one
area. Since the users do not know Access, I created a form to make it
easy to generate a report. Is it possible, either when the database
is initially opened or when the user clicks on the command button to
execute the report, to have the user enter a password and depending on
the password provided, restrict the information in the report to
either one area, or allow all six areas to be viewed? I understand
that you can password-protect command buttons, but is it possible to
restrict information within a table? If this is possible, how is it
done?

Thanks in advance for any help you can provide.

mark
Nov 12 '05 #1
7 1935
You are describing user-level security. To find out how to set it up
read the Security FAQ at this URL:

http://support.microsoft.com/default...93&Product=acc

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
Mark Thiel wrote:
I am working on a project for my company, which has a structure of six
major areas. Certain people need to have access to all of the data,
whereas other people only require access to the information for one
area. Since the users do not know Access, I created a form to make it
easy to generate a report. Is it possible, either when the database
is initially opened or when the user clicks on the command button to
execute the report, to have the user enter a password and depending on
the password provided, restrict the information in the report to
either one area, or allow all six areas to be viewed? I understand
that you can password-protect command buttons, but is it possible to
restrict information within a table? If this is possible, how is it
done?


Nov 12 '05 #2
"Mark Thiel" <mm*****@optonline.net> wrote in message
news:7c**************************@posting.google.c om...
I am working on a project for my company, which has a structure of six
major areas. Certain people need to have access to all of the data,
whereas other people only require access to the information for one
area. Since the users do not know Access, I created a form to make it
easy to generate a report. Is it possible, either when the database
is initially opened or when the user clicks on the command button to
execute the report, to have the user enter a password and depending on
the password provided, restrict the information in the report to
either one area, or allow all six areas to be viewed? I understand
that you can password-protect command buttons, but is it possible to
restrict information within a table? If this is possible, how is it
done?

Thanks in advance for any help you can provide.

mark

If you are *really* concerned about restricting access to the information,
then Access may not be the right tool. If you provide direct access to the
mdb file then it is possible to crack it.

If you are *moderately* concerned about this, you should use Access's
in-built user-level security. This allows you to specify permissions on a
per user or per group level. You would then remove permissions for the
table as a whole, but grant them access to appropriate queries which are set
to RWOP (run with owner's permissions). If you are new to this, it might be
a steep learning curve, but there is plenty written about it. From the FAQ
for this group, you could try this:

Microsoft Access Security FAQ:
http://support.microsoft.com/support.../Q165/0/09.asp

http://download.microsoft.com/downlo...-US/Secfaq.exe
(This is the Security FAQ for *ALL* version of Access)
http://support.microsoft.com/support...ent/secfaq.asp
(On-Line version of the Security FAQ)

If you need *low-level* security to keep out the casual snooper, then you
could do soemthing like this. Go to Tools>Startup and select a form to be a
startup form - also do not display the database window. This form could
have 2 textboxes with username and password and code checks if this is OK
and fills in a combobox listing the reports they can open.
Nov 12 '05 #3
mm*****@optonline.net (Mark Thiel) wrote:
s it possible, either when the database
is initially opened or when the user clicks on the command button to
execute the report, to have the user enter a password and depending on
the password provided, restrict the information in the report to
either one area, or allow all six areas to be viewed?


In addition to the other replies, you might find the beginner's guide to
security on my website useful.

Keith.
www.keithwilby.org.uk
Nov 12 '05 #4
Thanks for your help. I'll read through the security FAQs for further
help. I am new to developing a database of this magnitute, and am
feeling like I got myself way in over my head. Anyway, I am not too
concerned about the overall security of the database - the users are
not familiar with Access at all so I doubt they could crack it, and,
it wouldn't be the worst thing in the world if someone somehow saw all
of the data. Anyway, so just to make sure I understand you correctly,
once I establish the password for the form, I will be able to restrict
what information is included in the report?

For example, we divide the country into 6 geographic areas. I would
like each area to have its on password. Will I be able to set up
security on a form such that if someone from the Northeast enters
their password, the data in the report will only be from the
Northeast, vs. someone from the Southeast entering a password and only
obtaining info related to the Southeast?

Also, can anyone recommend a good Access "Bible"? Based on what I've
seen in some books, the stuff I need to do is somewhat advanced.

Thanks again for all of your help!





"Fletcher Arnold" <fl****@home.com> wrote in message news:<c1**********@hercules.btinternet.com>...
"Mark Thiel" <mm*****@optonline.net> wrote in message
news:7c**************************@posting.google.c om...
I am working on a project for my company, which has a structure of six
major areas. Certain people need to have access to all of the data,
whereas other people only require access to the information for one
area. Since the users do not know Access, I created a form to make it
easy to generate a report. Is it possible, either when the database
is initially opened or when the user clicks on the command button to
execute the report, to have the user enter a password and depending on
the password provided, restrict the information in the report to
either one area, or allow all six areas to be viewed? I understand
that you can password-protect command buttons, but is it possible to
restrict information within a table? If this is possible, how is it
done?

Thanks in advance for any help you can provide.

mark

If you are *really* concerned about restricting access to the information,
then Access may not be the right tool. If you provide direct access to the
mdb file then it is possible to crack it.

If you are *moderately* concerned about this, you should use Access's
in-built user-level security. This allows you to specify permissions on a
per user or per group level. You would then remove permissions for the
table as a whole, but grant them access to appropriate queries which are set
to RWOP (run with owner's permissions). If you are new to this, it might be
a steep learning curve, but there is plenty written about it. From the FAQ
for this group, you could try this:

Microsoft Access Security FAQ:
http://support.microsoft.com/support.../Q165/0/09.asp

http://download.microsoft.com/downlo...-US/Secfaq.exe
(This is the Security FAQ for *ALL* version of Access)
http://support.microsoft.com/support...ent/secfaq.asp
(On-Line version of the Security FAQ)

If you need *low-level* security to keep out the casual snooper, then you
could do soemthing like this. Go to Tools>Startup and select a form to be a
startup form - also do not display the database window. This form could
have 2 textboxes with username and password and code checks if this is OK
and fills in a combobox listing the reports they can open.

Nov 12 '05 #5
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Good "bible" _Access ## Developer's Handbook_ by Litwin, Getz &
Gilbert. Sybex Publishing. The ## represents the Access version
number. Acc2000 has 2 volumes (desktop & enterprise). Haven't seen
any versions for AccXP nor Acc2003.

To restrict users access to certain areas:

Create a table like this:

CREATE TABLE UserAreas (
UserID INTEGER NOT NULL ,
AreaID INTEGER NOT NULL ,
CONSTRAINT PK_UserAreas PRIMARY KEY (UserID, AreaID) ,
CONSTRAINT FK_AreaID FOREIGN KEY (AreaID)
REFERENCES Areas (AreaID)
)

The Primary Key of UserID, AreaID avoids duplicates. The Foreign Key
ensures there is an AreaID in the table Areas before the AreaID can be
added to UserAreas. Change column names as required.

The query/view that shows only the Area data for the users:

SELECT SD.AreaID, <... other cols ... >
FROM UserAreas AS UA INNER JOIN SalesData SD
ON UA.AreaID = SD.AreaID
WHERE UA.UserID = CurrentUser()

If you set up the application to use user-level security the method
CurrentUser() will return the logon name of the current user.

The query will only show Area info where the user has their UserID
associated w/ AreaID in the table UserAreas.

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA+AwUBQDZdYIechKqOuFEgEQLYvgCgn3zIW80OROgVTjwy3z l5aJ8ta/UAmMu5
OeVzLzYHyrugaaG40CHCgoM=
=BdRf
-----END PGP SIGNATURE-----
Mark Thiel wrote:

< SNIP >
....Anyway, so just to make sure I understand you correctly,
once I establish the password for the form, I will be able to restrict
what information is included in the report?

For example, we divide the country into 6 geographic areas. I would
like each area to have its on password. Will I be able to set up
security on a form such that if someone from the Northeast enters
their password, the data in the report will only be from the
Northeast, vs. someone from the Southeast entering a password and only
obtaining info related to the Southeast?

Also, can anyone recommend a good Access "Bible"? Based on what I've
seen in some books, the stuff I need to do is somewhat advanced.


Nov 12 '05 #6
http://www.peterssoftware.com/las.zip

I have used the software above to have light security on a multi user
database, give it a try as it is very inexpensive and simple to implement.
In addition the support from the author is first rate.

Don
Nov 12 '05 #7
"Mark Thiel" <mm*****@optonline.net> wrote in message
news:7c**************************@posting.google.c om...
Thanks for your help. I'll read through the security FAQs for further
help. I am new to developing a database of this magnitute, and am
feeling like I got myself way in over my head. Anyway, I am not too
concerned about the overall security of the database - the users are
not familiar with Access at all so I doubt they could crack it, and,
it wouldn't be the worst thing in the world if someone somehow saw all
of the data. Anyway, so just to make sure I understand you correctly,
once I establish the password for the form, I will be able to restrict
what information is included in the report?

For example, we divide the country into 6 geographic areas. I would
like each area to have its on password. Will I be able to set up
security on a form such that if someone from the Northeast enters
their password, the data in the report will only be from the
Northeast, vs. someone from the Southeast entering a password and only
obtaining info related to the Southeast?

Also, can anyone recommend a good Access "Bible"? Based on what I've
seen in some books, the stuff I need to do is somewhat advanced.

Thanks again for all of your help!

It all really depends on whether you can write code or not or whether you
want to learn. Most of the people who post to this group will tell you that
you can't get very far designing databases before you have to start writing
code.

If you worked out how to use user-level security then you would create users
and groups, eg

NorthEastUsers (Bill, Bob, Ben, Brian)
NorthWestUsers (Dave, Derick, Dawn)
.....
.....
Managers (Martin, Mary)
DatabaseDesigners (Mark)

You then have a number of queries like "qryNorthEast", "qryNorthWest",
"qryAllRegions" and assign permissions so that NorthEastUsers can run
qryNorthEast, but managers can run all of the queries including
qryAllRegions. But you, as a member of the DatabaseDesigners group, will be
able to change the design of the queries, reports and tables.

****
My guess is that it would be more useful to learn a bit of code to implement
the low-security option. Mostly on the basis that it's so useful to be able
to write a few lines of code.
The code you write could be as simple as putting something in the report's
open event, like this:

Private Sub Report_Open(Cancel As Integer)

If InputBox("Enter password") <> "secret" Then
Cancel = True
MsgBox "Wrong Password", vbCritical
End If

End Sub

Which is as simple as it gets, however using the Inputbox function would
need to be replaced with something where the user didn't type in plain text
on the screen. However, if you gave access to the mdb file, then anyone who
knew Access could look at the code.
One final thought - many databases are split front and back end. You could
distribute different versions to different people so although they shared
the same data (back end), the reports held in the front end would be
different. At least it is a solution which requires neither coding nor
user-level security.
Fletcher


Nov 12 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: kma | last post by:
I am designing an Access 2000 database on a computer running Windows 98. I have one form with several tabs; all of which have sub forms, some with a subform on a subform. For example, on my...
3
by: Tim Marshall | last post by:
My A2003 is set up so that the default file format is Access 2000 (options, advanced tab). I've just tried to make my first A2003 mdb into an mde and I get an error saying: "you cannot create a...
1
by: Ray Watson | last post by:
ACCESS 2000 Just a quick note to leave for future developers. This problem has been well documented in previous posts, but this little trick may help future poor souls. The error is when you...
15
by: Joshua Kendall | last post by:
I have a script in which it keeps opening the same form instead of only one instance. I also need help with a form that has a password. Where do I put the actual password? can I use a database for...
15
by: Dino Vliet | last post by:
Hi folks, probably this is a question you've heard so many times but I wasn't able to find a solution to it. I'm using a shell script to create a textfile for me. It looks like...
2
by: egoldthwait | last post by:
I need to convert a 17mb access 2000 db to Oracle and house it in a Citrix farm. The issue: we have never converted an Access Db to Oracle but can probably use Oracle's Workbench to assist with...
3
by: Marina | last post by:
Ok here's my problem.... I have a custom dialog box that allows the user to enter information to run a Parameter Query. This works GREAT, but I need that query to show as a form, not just the...
2
by: Simon.Whiteside | last post by:
If someone has created a database for me and transferred it over is there any way that I can check I have full access to all areas? I am a beginner with Access and so the development has been...
4
by: ThePhenix | last post by:
Hi everybody, I have recently been doing a conversion for my boss for a access 2000 database (both front and backend) to a MSSQL backend. I finished the conversion today (as quite a lot of the...
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: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
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: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
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.