Question Password Protection

[klix]

Hello,
I'm wondering if there's some way I can require a password to edit a table in Access 07. I know how to require one for the whole db. But I want this db open to everyone for searching and viewing, while restricting access to edit the tables. Hence the password only for tables. Is it possible? Or if there's some other solution to accomplish what I've described, that would be fine also.
Thanks.

 

[Trevor G]

Hello,
I'm wondering if there's some way I can require a password to edit a table in Access 07. I know how to require one for the whole db. But I want this db open to everyone for searching and viewing, while restricting access to edit the tables. Hence the password only for tables. Is it possible? Or if there's some other solution to accomplish what I've described, that would be fine also.
Thanks.

What you will need is to create an autoexec macro that will open a form when the database opens, then give access to the areas you want people to have access to, like working with forms. If you have an adminsitration area you can add the password feature there so you can get to the tables.

Then hide the navigation window (database window) and also restrict the use of the F11 function key.

Password would be something like this

create a command button on the form.

Behind the button look for the events and on Click and place in the following code:

Dim StrPassword as String
srtPassword = "What you want as your password"
if inputbox"Please enter the password"=strPassword Then
docmd.OPenTable "The table name"
End IF

 

[klix]

Trevor,

Thanks for the response. I have a comment and a question. When I copy-pasted the code you gave, the 'if inputbox...' line was colored red. I tried to space it differently, then to put it back exactly as you had it, but every time a box poped up that said 'Compile error:/Expected: Then or GoTo'. Nothing happened when I saved the code and clicked the button on my form. I'm not good with code, just so you know.

Also, what do you mean by 'administration area'? I haven't had any formal training with Access; all self-taught. So if my questions are basic, that's why. Thanks.

 

[Trevor G]

I have just tested this:

Dim strPassword As String
strPassword = "Sample"
If InputBox("Enter Password") = strPassword Then
DoCmd.OpenForm "frmStaff"
Else
MsgBox "Please contact the administrator"
End If

Regarding administrator, what I basically mean is that you have an area in your database which needs maintaining, like adding to lists etc, rather than allowing anyone to update sensitive areas you designate someone. Therefore you are looking to assign permissions for that person or persons. This can be achieved either with password and enviormental checks or you could set up a user group and add permissions.

Trevor

 

[Mr. B]

klix,

While Treavor G has provided you with correct information when answering your origianl question, I felt that I should just let you know that is it not a good practice to allow users to work directly in tables. Tables are only used as containers to hold data. Data entry and access to the data should be accomplished through the use of Forms. When you implement the use of Forms in you applicaiton you will then be able to control the user's ability to access the information in you tables.

Just my thoughts.

 

[klix]

Trevor,
Thanks for the update. I'll try it and let you know. (Also, read below. I use forms as Mr. B mentioned so I'm not sure if that will affect the code...) In the meantime, I have another question. I understand the concept of an administrative area but I'm not sure if there's special formatting, a special way to 'make' and administrative area, etc. How do you 'designate someone' like a 'user group' or 'assign permissions'? Is it basically through the password I'm trying to make, or is there more? Thanks.

Mr. B,
Thanks for sharing. I should've been more specific in describing my db. I actually do use forms to edit the tables.

 

[vbaInet]

One thing to point out is that since its a password you're testing against, you may also want to ensure that the CASE is matched. To do this, you use the StrComp() function and in the 3rd argument use the vbBinaryCompare as the compare type.

To elaborate, if (for example) Joe Bloggs' password is 'Pk12Hj4' and you did a comparison using the equal to "=" operator to match it against 'pK12hJ4', the result will be TRUE. It shouldn't be for a password. That is:

pK12hJ4 = Pk12Hj4_____________Result: True

Unless you're not going to have a CASE-sensitive password in which case you shouldn't bother about the StrComp() function.

To answer your question, you can create a table like this:

tblUserAccount
---------------------------
E_UserName (PK)
E_UserPassword
E_UserGroup

E_UserName___________E_UserPassword____________E_UserGroup
jbloggs__________________Pk12Hj4_______________Admin
mbaskin__________________secret________________User

Lookup the DLookup function too because that will come in handy

 

[klix]

Trevor,

The code worked perfect! I appreciate your help. I'm still curious about the questions I asked in my previous post and have one more thing to add. I need a way of not displaying the tables.

I've tried splitting the db and requiring a password for the back end, but then the password is required for all my query/report display functions. Unusable. I've changed the format of the db to .accdr, which is handy, but users could just change the file format as I did. Unreliable. I also know I can 'hide' them. But I'd rather have a more secure way of preventing access to the tables also. Can you (or anyone) think of anything?

Thanks!

 

[Pyro]

If you want to just hide the table with your user data, you could rename that table with the prefix "Usys". Access automatically hides tables with that prefix.

Or as trevor mentioned at the top you can hide the navigation window in the Access Options->Current Database settings. You may also want to restrict the F11 key.

I think ultimately you will find that holding user data (passwords etc) in a table in Access is not very secure - or so i've been led to believe.

 

[ghudson]

FYI: Your database setup will not prevent a user from linking your tables to another database or prevent a user from importing the objects from your database into another database.

 

[umpire43055]

I am having trouble getting back into my database. I created it for a little league here and now it asks for a password. I did not set one up to begin with and I have tried reinstalling the product. Any suggestions on how to reset or find out what the password is?

 

[Mr. B]

Have you tried opening the database while holding down the Shift until it completly opens?

 

[umpire43055]

Yes I have tried Holding shift while opening the file and still the same error..

 

[Trevor G]

Yes I have tried Holding shift while opening the file and still the same error..

What I would suggest is create a new database, then look to import the working database you are using, but do each area 1 at a time, so import the tables, then see that everything is accessible, then repeat this for queries, forms, reports, macros etc. If this then cures the issue great if not then you might want to think about uploading the database to see if we can help.

Trevor

 

[ghudson]

I am having trouble getting back into my database. I created it for a little league here and now it asks for a password. I did not set one up to begin with and I have tried reinstalling the product. Any suggestions on how to reset or find out what the password is?

Does the dialog box state "password required" enter database password: ?

Or is it asking you for a user ID and password ?

 

[umpire43055]

It is asking for a login in and password. I think I am going to have to wipe the hard drive and start over. It appears to be something in the registry is not clearing out. When I email it to myself of someone else and check it on another PC it opens just fine.

 

[ghudson]

You have altered the default system.mdw workgroup file on your computer. Find it, copy and rename it [just in case] and then get a copy from one of your coworkers computer [using the same version of access as you] and copy their file to your computer. All access db's are secured to the default system.mdw file unless you change it. Workgroup security is great when you know what you are doing but you can easily lock yourself out of your db's if you do not know how to correctly secure a database.

 

[umpire43055]

The wierd thing is, I cannot find that file. I have searched with no results. I even tried looking for the folder it may be in with no luck. I really hate to reinstall everything, but it may be necessary.

 

[ghudson]

It is a system file [might be hidden on your pc]. Should be @ C:\windows\system32\system.mdw

 

[umpire43055]

I will have to search a little more...Does it matter that it is WIndows 7?