Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 18th, 2007, 03:20 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 126
Thanks: 5
Thanked 0 Times in 0 Posts
Default User security

Good afternoon,
I was playing around with the user-level security, but am having a problem. (I am probably overlooking something that once I am told...will make me feel a little on the stupid side!)

I made the .mdw file, but how do I get the database to launch using only that .mdw file regardless of which of the 400+ PC's on our network open the database?

Can I have this all set up, then create an .mde file to distribute to the users and the security still work?

(I was maintaining security through the design of the forms, but I am getting too many versions of the same form and design changes have become a nightmare...but that was the only way I could make the security work when I originally created the database.)

Any help would be much appreciated.

Regards,
Laura

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!
__________________
Regards,
Laura

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!
 
Old April 19th, 2007, 06:46 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Now you are using Access security instead of programmed security? If that is the case, perhaps you want to get: "Real World Microsoft Access Database Protection and Security", ISBN-10: 1590591267. (Sorry Wrox).

What was wrong with programming security? I like that better for some of the reasons you have here.

I am not sure if the security works or how if the database is split. You may have to secure the back end, and not the front ends since the security works on tables, I think.


Did that help any?


mmcdonal
 
Old April 19th, 2007, 07:55 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 126
Thanks: 5
Thanked 0 Times in 0 Posts
Default

A novel to help explain...:D

Here is an example of the security I was using: I had the exact same form in 3 different versions. Each form would look identical visually, but in one version certain fields would be enabled, and in another version they wouldn't. Then another version of the form would have everything locked to serve the purpose of "read-only". Then I would create different menus for start-up depending on who I would send that .mde to, and write the program to open the correct forms for data entry based on that menu...then once I made the .mde, they couldn't get around my security.

I know there must be a better way to handle this...because examples aside, one of my actual databases has 6 different versions of the same form. We use this database to create our TS16949 forms, so each version has to stay visually identical on the screen and identical to the one common report used to print out the official hard-copy. Because of the magnitude that this database has reached, design is slow because of so many forms, and a 5 minute change really takes me almost an hour due to the application being slow and having to change all those versions. (Change happens quite often here too.)
This being said...are we on the same page when talking about programming security?

I didn't take any basic Access classes...I just taught myself the application through trial & error, so I don't know if I started off incorrectly in how I handled my security or not...but I didn't know how else to manage it. The only training I have had is very basic programming of docmd & if statements, so there is a ton that I still don't know.

In more detail of my databases I create...I split all of them.
Tables/back ends are located in one common folder on the network regardless of who needs access to them. (I was forced into this because out network software used for nightly back-ups doesn't pick up Access databases for some reason, so we had a programmer write a script to make a complete copy of that folder each night so I have something to go back to if we have issues.) I create a password form on the back ends that displays as popup and modal at startup to prevent other users from getting into the tables without using the front end that I provide them.
Then all the queries, forms, & reports are in the front ends that I also store in one folder that only I have network security to modify...no one else can even see them. Then when I am ready to distribute, I create the .mde file and e-mail them out to the different groups of users once they complete the training on how to use the database.

Regards,
Laura

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!
 
Old April 19th, 2007, 08:11 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I am still not sure HOW you were implementing security. Was it with code, or Access security. If with code, what you should do is to use the user's name to determine what fields do what on the form. So if I opened the database, it would look at my name, and then on the On Load event of the ONE form, do this:

If Username = "me" Then
   Me.TextBox1.Visible = False
Else
   Me.TextBox1.Visible = True
End If

That sort of thing.

I used to create users and put them in groups, and then open a hidden form with the username and group. Then check the vlaues on the hidden form when each form opened, and based on the group, hide or disable controls. No need to create a seperate version of the form.

Anywho, are we any closer...?


mmcdonal
 
Old April 19th, 2007, 08:23 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 126
Thanks: 5
Thanked 0 Times in 0 Posts
Default

mmcdonal,
I was using the form design for security...never used Access user-level security before I started dorking around with it yesterday.

What you listed to do sounds much simpler, but how do I get their usernames? We use Microsoft Network here on a Windows 2003 server...can I get their network login ID from code to use instead of making them type in a user id?

Do you have a non-confidential databases you could send as an example for how you did this? (I tend to grasp concepts better and remember them if I see it and try to mimic it.)

Thanks for your help...I think we are getting much closer!

Regards,
Laura

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!
 
Old April 19th, 2007, 08:41 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Here is what I do:

Create a Module, then put this code in:
Public pUser As String

Function sU()
sU = (Environ$("Username"))
End Function

Then when you need the username, just do this, say within a sub on an event:

pUser = sU()

If you put this event on the Main On Load event, then you can pop open a hidden form and take the credentials or user level from the form. So, if I created groups, like, User, and Admin, I would do this when the Main form opened:

(First create a User table, with Username as the PK, and Group as an internal look up)

Dim sDocName, sLink As String

pUser = sU()

sLink = "[Username] = '" & pUser & "'"

sDocName = "frmHideLogin" 'use the create form wizard on the User table - nothing fancy

DoCmd.OpenForm sDocName, , , sLink, , acHidden

Then create an On No Data event for the frmHideLogin form that pops up an error message, like "Get Out!" and then closes the database.

When you open forms, do this:

Dim sUser, sGroup As String

sUser = [Forms]![frmHideLogin].[Username]
sGroup = [Forms]![frmHideLogin].[Group]

If sGroup <> "Admin" Then
   Me.AdminButton.Visible = False
Else
   Me.AdminButton.Visible = True
End If

That sort of thing.

Since I mostly do SQL Server back ends, my code is ADO and checks SQL Server tables etc.



mmcdonal
 
Old May 3rd, 2007, 06:37 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 126
Thanks: 5
Thanked 0 Times in 0 Posts
Default

mmcdonal,
I ended up swamped at work and haven't been able to apply the examples you gave me yet. (Databases are considered my lowest priority responsibility at my job...because they really are not in my job description, they just use me for databases because I am the only one here with an advanced knowledge of Access, and the only one that knows any programming where I work.) Once I get back to this, I will let you know if I have further questions.

Also, I finally received the book last week that you suggested. It will take me some time to get through it, but in reading through the table of contents, it does look like a really good book to have when using security in databases.
Thanks again for the suggestion...it was a great one!

Regards,
Laura

FYI...My user id has changed. My old id was lbreitenbach

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!





Similar Threads
Thread Thread Starter Forum Replies Last Post
Permissions on DB without user-level security nbourre Access VBA 4 June 7th, 2008 08:09 AM
User Level Security cpbsmw Access 1 February 1st, 2007 08:41 AM
User Level Security cpbsmw Access 2 January 26th, 2007 04:07 PM
user password and the security question within ASP harrison4411 BOOK: Beginning ASP.NET 2.0 BOOK VB ISBN: 978-0-7645-8850-1; C# ISBN: 978-0-470-04258-8 0 March 27th, 2006 05:39 PM
Problem creating user-level security JackNimble Access 2 October 11th, 2004 11:20 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.