469,330 Members | 1,275 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,330 developers. It's quick & easy.

How to set up db so one user sees only forms and other user has access to all?

AccessIdiot
493 256MB
I'm sure this has been asked a million times but I can't seem to find the answer I need.

I am working in Access 2007. I have a very simple project composed of one main table, several look up tables, one switchboard form, two other forms (one data entry form and one query/search form), and one report.

I need to set this up with two levels of access:

1) people entering data can only see the forms and report, no access to any tables. It would be great if they couldn't even see the tables.

2) one person with access to everything (not me, since i'm shipping this whole thing off to the folks I'm creating it for). This person may need to delete records from the main table or add values to the look up tables, etc.

One idea is to password protect the tables. Another idea is to split (?) the project into front end/back end. I've seen people mention this, and have seen NeoPa's write up (here) but in reality I have no idea how to do either. I'm not a coder, but can hack things together fairly well.

I'm told the project will be on one computer. There is no access to SQL Server or anything fancy/complicated. We're not concerned with people hacking the data, just want to avoid mistakes or people poking at tables they shouldn't.

Ideas? Thoughts? Advice?
Aug 11 '10 #1
58 9148
NeoPa
32,181 Expert Mod 16PB
I suggest you have the database start in a mode where the database window is not visible, but your main controlling form (could be switchboard) starts automatically.

It's not clear yet how you would envisage determining which user is the current operator, but I would have code that executes when the main form is closed that checks the user, if they are allowed then proceed to show the database, whereas if they are not it would close the database itself.

Does this sound like it would suit? Is this secure enough for you?
Aug 11 '10 #2
AccessIdiot
493 256MB
Yes I think so. So basically when you launch the database (double click on the file name) is the first window that shows up something that asks for a password? Or something similar?

How do I hide the database window? Or password protect it? I'm a little unclear on this part.
Aug 11 '10 #3
AccessIdiot
493 256MB
How about this? Split the database into front end and back end. Change the front end to an accde file. Rename the accde to accdr so it is a run time file.

The accdr file resides in a main location where data entry folk can launch it. The accdb_be (back end) file resides in a hidden location on the same computer so the data entry folks don't know where it is and won't open it but the database admin person can launch it to make changes to the tables.

Anyone see anything wrong with this?
Aug 11 '10 #4
NeoPa
32,181 Expert Mod 16PB
AccessIdiot: Yes I think so. So basically when you launch the database (double click on the file name) is the first window that shows up something that asks for a password? Or something similar?
That's what I was asking you. You can get any form to start up automatically by playing in Tools / Startup.
AccessIdiot: How do I hide the database window? Or password protect it? I'm a little unclear on this part.
You can also control from there whether or not the database window shows.

As far as determining whom to allow in goes, that rather depends on what you want. You can set up a form to make the user log into a session. Alternatively you can get the Windows account name that was used to log on to Windows (or the domain). As security isn't an issue, it makes perfect sense to get the user name from the environment variable. It's quite easily cracked, but if that isn't likely to be an issue then the method is fine.
Aug 11 '10 #5
NeoPa
32,181 Expert Mod 16PB
AccessIdiot: Anyone see anything wrong with this?
Only that it's not exactly what you asked for. It would be tighter security, but splitting FE & BE in this case would not be relevant. I wouldn't advise against it as such, but I don't see it pertains to this unless you are, after all, unhappy with my earlier suggestion. You certainly wouldn't want to do both (although simply splitting the FE & BE in itself isn't incompatible). Actually this is hard to comment on as a bunch of ideas have been thrown across each other and dealing with the full ramifications of each would be a whole pattern that needed unpicking.

Only you know what you need. From your earlier comment that my suggested solution was adequate, there is little point in complicating the matter further. If that no longer holds, or you've changed your mind on it, then we'd need to know that before proceeding to avoid confusion and talking at cross purposes.
Aug 11 '10 #6
Jerry Maiapu
259 100+
Hi,
Just want to contribute a few ideas here.

I am using a custome API function to completely hide/remove Access window while showing the forms only.

I think that will prevent people from viewing any Access Objects,like Tables Reports, etc.. apart from the forms.

If you think this will solve your problem post to inform for the function.
Aug 12 '10 #7
AccessIdiot
493 256MB
Whew. I'm certainly not looking for anything complicated and I don't think it needs to be mega-secure. I'd just like to create something where only one person has access to the tables and everyone else has access to the forms only. I don't really like the idea of splitting the database - one too many parts to keep track of. I think I'd prefer to have a start up screen that asks if you have a password (and it can be one I assign, or one that's stored in a table that he can change?). If you do, enter it and have access to everything. If you don't have a password, then move on to the regular old switchboard which leads to only forms and the one report.

Thanks for the advice and help!
Aug 12 '10 #8
NeoPa
32,181 Expert Mod 16PB
Are you sure you wouldn't like simply to check his Windows Logon? It's so easy and doesn't require any forms or special handling that needs to be rerun if the project ever resets itself.
Aug 12 '10 #9
AccessIdiot
493 256MB
If its easy then yes I'd love to do it! No need to remember separate passwords. I need to try and get this implemented fairly quickly.
Aug 12 '10 #10
NeoPa
32,181 Expert Mod 16PB
So this is pretty much what I was outlining in post #5 then I believe.

Have you set up the main form to start automatically (from post #5)?

Have you set it so that the database window doesn't show?
Aug 12 '10 #11
AccessIdiot
493 256MB
Yes, sort of. I have a form that launches when you launch the project. So right now when you launch the program you see a form with two buttons:

1) "login as administrator" (no code yet)

2) "go directly to the switchboard" with the following code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSwitchboard_Click()
  2.   DoCmd.Close acForm, "frm_Welcome"
  3.  DoCmd.OpenForm "Switchboard"
  4. End Sub
  5.  
On the OpenForm event of the form I have
Expand|Select|Wrap|Line Numbers
  1. DoCmd.NavigateTo "acNavigationCategoryObjectType"
  2.  DoCmd.RunCommand acCmdWindowHide
That's as far as I've gotten. And of course if you hit F11 you get the navigation pane back.

Should I go and hunt down the code to prevent F11 or Shift Key on launch?
Aug 12 '10 #12
NeoPa
32,181 Expert Mod 16PB
Before we go to much further, we'll need to set up some code to call an inbuilt OS procedure. I normally have this in a separate module (I call modOS), with any other such OS procedures, but for this exercise I will include only code relevant to this issue.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. 'Windows API Variable Prefixes
  5. 'cb = Count of Bytes (32-bit)
  6. 'w  = Word (16-bit)
  7. 'dw = Double Word (32-bit)
  8. 'lp = Long Pointer (32-bit)
  9. 'b  = Boolean (32-bit)
  10. 'h  = Handle (32-bit)
  11. 'ul = Unsigned Long (32-bit)
  12.  
  13. Private Const conWinVis As Long = &H10000000
  14. Private Const conGWLStyle As Long = -16
  15.  
  16. Private Declare Function FindWindowEx Lib "user32" _
  17.     Alias "FindWindowExA" (ByVal hwndParent As Long, _
  18.                            ByVal hwndChildAfter As Long, _
  19.                            ByVal lpszClass As String, _
  20.                            ByVal lpszWindow As String) As Long
  21. Private Declare Function GetWindowLong Lib "user32" _
  22.     Alias "GetWindowLongA" (ByVal hwndID As Long, _
  23.                             ByVal nIndex As Long) As Long
  24.  
  25. Public Function DBWindowVisible() As Boolean
  26.     Dim hWnd As Long, lngStyle As Long
  27.  
  28.     'Get handle of MDIClient window of current application
  29.     hWnd = FindWindowEx(hWndAccessApp, 0, "MDIClient", vbNullString)
  30.     'Within that, find child window matching class Odb (database window)
  31.     hWnd = FindWindowEx(hWnd, 0, "Odb", vbNullString)
  32.     'Default result to False in case handle wasn't found
  33.     DBWindowVisible = False
  34.     If (hWnd) Then
  35.         'Having found window, check the visibility flag of its style value
  36.         lngStyle = GetWindowLong(hWnd, conGWLStyle)
  37.         DBWindowVisible = ((lngStyle And conWinVis) = conWinVis)
  38.     End If
  39. End Function
I suggest you get this setup while I dig up the other related stuff.
Aug 12 '10 #13
AccessIdiot
493 256MB
Dumb question #1: where does this go?
Aug 12 '10 #14
NeoPa
32,181 Expert Mod 16PB
With reference to post #12 :
  1. No need for that form. Go straight to Switchboard instead.
  2. We'll cover what's required in more detail shortly.

    For now, we can ignore F11 for the users as most won't know about it or want to use it if they do (from your earlier answers about security levels). If you're worried, these settings are in the same place as the others.
Aug 12 '10 #15
NeoPa
32,181 Expert Mod 16PB
AccessIdiot: Dumb question #1: where does this go?
Not at all.

From Access press Alt-F11 to open/switch to VBA IDE.
Press Ctrl-R to open/switch cursor to Project Explorer pane.
Right-click in there and select Insert / Module.
Press F4 to open/switch cursor to Properties pane.
Change name to "modOS".
Press F7 to open/switch cursor to Code pane.
Paste in the code.
Aug 12 '10 #16
AccessIdiot
493 256MB
Rockin' it and done, thanks!
Aug 12 '10 #17
NeoPa
32,181 Expert Mod 16PB
The form's Open event procedure needs the following code :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.     Call DoCmd.Restore
  3.     If DBWindowVisible() Then
  4.         Call DoCmd.SelectObject(ObjectType:=acForm, InDatabaseWindow:=True)
  5.         Call DoCmd.RunCommand(Command:=acCmdWindowHide)
  6.     End If
  7. End Sub
The last bit is a little different from what I use for myself, so get this ready for now.
Aug 12 '10 #18
AccessIdiot
493 256MB
Are we talking the switchboard form here? Or some kind of log in form? The switchboard is not my own - its the built in Access one so has an embedded macro on the On Open property right now. Though I could probably whip up my own custom one pretty easily if that is a better way to go.
Aug 12 '10 #19
NeoPa
32,181 Expert Mod 16PB
Now, for the closing of the Switchboard form (this should only ever close when you're done, and not when calling other forms or anything else within the execution of the project).

You will need available at the top of the form's code a definition of the account(s) that should allow access to the database window :
Expand|Select|Wrap|Line Numbers
  1. Private Const conClear As String = "Administrator;MKM"
Clearly these are just guesses and you'd need to fill the data in yourself. Separate accounts with a semi-colon (;) as illustrated.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Close()
  2.     Dim strUser As String
  3.  
  4.     strUser = ";" & Environ("UserName") & ";"
  5.     If InStr(1, ";" & conClear & ";", strUser) > 0 Then
  6.  
  7.         If Not DBWindowVisible() Then _
  8.             Call DoCmd.SelectObject(ObjectType:=acForm, _
  9.                                     InDatabaseWindow:=True)
  10.     Else
  11.         Call DoCmd.RunCommand(acCmdClose)
  12.     End If
  13. End Sub
That should do it for you. Give it a go and let me know how it works for you :)
Aug 12 '10 #20
NeoPa
32,181 Expert Mod 16PB
AccessIdiot: Are we talking the switchboard form here? Or some kind of log in form? The switchboard is not my own - its the built in Access one so has an embedded macro on the On Open property right now. Though I could probably whip up my own custom one pretty easily if that is a better way to go.
If you're planning on using the Switchboard as your main form (I would guess that makes sense) then yes. Put it in there. You can add my code to what's already there I expect. If you have any problems post what you have and I'll look at it for you and show you how to integrate my code into what's already there.
Aug 12 '10 #21
AccessIdiot
493 256MB
Well it's an Access gizmo - go through the wizard to create a switchboard. I did it because I was lazy and it was one of those "get something up so the client can check out a first draft of the db" kind of things. I can make my own though - it would be easier I think. Give me a few minutes to set it up and then I can try your code out.

Does it make a difference that I won't ever be using this db? I'm making this for someone in another state and will just be shipping it off to them to put on their own computer. Would I put their initials on this line instead?

Expand|Select|Wrap|Line Numbers
  1. Private Const conClear As String = "Administrator;MKM"
Aug 12 '10 #22
NeoPa
32,181 Expert Mod 16PB
Sort of. You'd need to find out from them what they log on as. There may only be one of them, but if there are more then separate them with semi-colons (;).

Alternatively, if you think it worth the extra effort, I could look at implementing the list as records in a table for you. That way the Admin could maintain the list themself after you've passed it on to them.
Aug 12 '10 #23
AccessIdiot
493 256MB
I think that would be best. Maybe have a table of username/password? That way I could set up a temporary one and he could go in and change it to whatever he wanted once I gave it to him. We're not super worried about security (who would want to hack/jeopardize a bunch of goose nesting habitat data?), more just people thinking they know what they are doing and then editing/deleting in the tables directly instead of through the forms. Only one guy should be in there and if he doesn't know what he's doing well, that's his fault and no one else's. Does that make sense?

Or does having "Administrator" take care of that?
Aug 12 '10 #24
AccessIdiot
493 256MB
Dumb Question #2: The code for the form close - should that also go on the "exit" button I have on there as well? Or just on the form close event?
Aug 12 '10 #25
AccessIdiot
493 256MB
I am getting this error message:
"Run-time error '2046':
The command or action 'Close' isnt' available now."

Also when I launch the file I can still see the navigation pane?
Aug 12 '10 #26
NeoPa
32,181 Expert Mod 16PB
AccessIdiot: Dumb Question #2: The code for the form close - should that also go on the "exit" button I have on there as well? Or just on the form close event?
#2? You missed some surely :p

I'm just kidding of course. This is a question that dumb people don't ask, and hence have crappy designs.

No. The Exit button, the X in the top-right corner and even Ctrl-F4 call the form to close. This procedure handles that. Whichever route is used.
Aug 12 '10 #27
AccessIdiot
493 256MB
Well now that's kind of a tricky thing isn't it? Some people might just hit the x in the top right corner and close the switchboard form without actually exiting the program. Some might hit the exit button on the switchboard which had a docmd.quit on it. I don't know too many keyboard control folks that are going to be volunteering to do data entry at a wildlife refuge so I'm not too worried about Ctrl F4 :-P But the other two are definitely a possibility.
Aug 12 '10 #28
NeoPa
32,181 Expert Mod 16PB
AccessIdiot: I think that would be best. Maybe have a table of username/password?
Let me try to get this clear. There is no need for a password if you are getting the username from the Environment Variable. If they have already logged on, then they have provided a more secure password already. Just the name (Account ID) is required to recognise who they are, so this is all that would need to be stored in any table.

If you want a table approach, may I suggest that you first create a table to hold the data. I doubt you'll need help with that part.
Aug 12 '10 #29
NeoPa
32,181 Expert Mod 16PB
AccessIdiot: Well now that's kind of a tricky thing isn't it?
As I stated earlier, this code handles all those anyway. That's the beauty of it.
Aug 12 '10 #30
AccessIdiot
493 256MB
Right. My understanding is that this db is going to be sitting on a guest computer. So anyone can launch it without needing a log in (or there is a guest log in on the computer). The guy that will be owner/administrator of the db won't necessarily need to log in either, so it might help to create a log in to differentiate between him and the folks doing the data entry.

Sorry, I'm just realizing how this is going to work over there.

Oh and see post #26 - I'm getting an error message on the close form.
Aug 12 '10 #31
NeoPa
32,181 Expert Mod 16PB
AccessIdiot: I am getting this error message:
...
I'm going to need a clearer, more precise, description of exactly what you're doing and what goes wrong where with error message(s) etc if I'm to help debug this at a distance. We may get to attaching a copy of the database for me to look at if we need to, but we're probably not there yet.
Aug 12 '10 #32
AccessIdiot
493 256MB
Right, sorry, you probably can't see through the computer screen can you? :-)

When I hit the "exit" button on my switchboard (which is simply docmd.exit on click) I get the error message posted above.

When I simply click in the X in the upper right hand corner I get "Run-time error '2501': The RunCommand action was canceled."

*edit* when I am smart enough to remember to change "MKM" to my actual username on my machine I get a Compile error: variable not defined on conClear in your code.
Aug 12 '10 #33
NeoPa
32,181 Expert Mod 16PB
AccessIdiot: Right. My understanding is that this db is going to be sitting on a guest computer. So anyone can launch it without needing a log in (or there is a guest log in on the computer).
In that case I'd think seriously about bothering with this at all. My experience tells me that 99 times out of a hundred one person will come along and leave it running, then another will come along and use the same session. If they all think they'll be using this on the same computer in any sort of controlled way then I think they're naive, frankly.

That goes for giving a password just as much as it does for the account ID.
Aug 12 '10 #34
AccessIdiot
493 256MB
Well, I have to have something to give them where he has access to the tables and everyone else has access only to the forms. I would love to be able to hide the navigation pane on launch and the first thing that pops up asks if you want to log on as administrator (navigation pane becomes visible if you log in correctly) or go right to the main switchboard and the navigation pane stays hidden.
Aug 12 '10 #35
NeoPa
32,181 Expert Mod 16PB
AccessIdiot: When I hit the "exit" button on my switchboard (which is simply docmd.exit on click) I get the error message posted above.
That's tricky. It's part of the new code I added for your situation (most of the rest I use myself in most projects). Maybe it doesn't like trying to close the database while within the form's Close event. I may need to look at this again.
Aug 12 '10 #36
NeoPa
32,181 Expert Mod 16PB
AccessIdiot: Well, I have to have something to give them where he has access to the tables and everyone else has access only to the forms. I would love to be able to hide the navigation pane on launch and the first thing that pops up asks if you want to log on as administrator (navigation pane becomes visible if you log in correctly) or go right to the main switchboard and the navigation pane stays hidden.
I think you missed the point here a little. Even with such a feature it still wouldn't work. Extra work. Extra complexity, all for people who don't appreciate that it is their own ideas and approach that will mean it won't work.

I suggest you go back to them and explain how impractical is their suggestion for a working environment and see what they come back with.

It's easily enough managed, whichever way they want it, but you can tell them from me that it will all be pointless due to their approach.
Aug 12 '10 #37
AccessIdiot
493 256MB
I wish that were an option. If it is too impractical then I can just tell them to tell people not to open any of the tables and use the switchboard only, but it sure would be cool not to show the navigation pane. I guess I can tell the guy who wants access about F11.

By the way I don't know if you saw the edit I added in post #33?
Aug 12 '10 #38
NeoPa
32,181 Expert Mod 16PB
AccessIdiot: *edit* when I am smart enough to remember to change "MKM" to my actual username on my machine I get a Compile error: variable not defined on conClear in your code.
I so nearly missed this with all the posts going backwards and forwards.

I think I may need to have a closer link to go forward with this. Do you have Skype installed yet?
Aug 12 '10 #39
AccessIdiot
493 256MB
Not allowed at work. :-( Shall I send it the 1990's way? :-)
Aug 12 '10 #40
NeoPa
32,181 Expert Mod 16PB
Attach it to the thread. I was hoping to chat and iron out some of the complications as so much has been covered but over a forum page which is not the easiest or most reliable medium in the world.

Anyway, see below for the attachment instructions I usually post :

When attaching your work please follow the following steps first :
  1. Remove anything not relevant to the problem. This is not necessary in all circumstances but some databases can be very bulky and some things do not effect the actual problem at all.
  2. Likewise, not entirely necessary in all cases, but consider saving your database in a version not later than 2003 as many of our experts don't use Access 2007. Largely they don't want to, but some also don't have access to it. Personally I will wait until I'm forced to before using it.
  3. If the process depends on any linked tables then make local copies in your database to replace the linked tables.
  4. If you've done anything in steps 1 to 3 then make sure that the problem you're experiencing is still evident in the updated version.
  5. Compile the database (From the Visual Basic Editor select Debug / Compile {Project Name}).
  6. Compact the database (Tools / Database Utilities / Compact and Repair Database...).
  7. Compress the database into a ZIP file.
  8. When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.
It's also a good idea to include some instructions that enable us to find the issue you'd like help with. Maybe some instructions of what to select, click on, enter etc that ensures we'll see what you see and have the same problems.
Aug 12 '10 #41
AccessIdiot
493 256MB
Sigh. Well, I'm not sure what happened but I no longer get an error message. So I'll hold off on attaching it right now since that seems to be working fine.

However, I had my coworker log in to a machine and launch it and the navigation pane shows up? How do I hide that from anyone other than:

Expand|Select|Wrap|Line Numbers
  1. Private Const conClear As String = "Administrator;MKM"
or is that what you were saying is difficult to do?
Aug 12 '10 #42
NeoPa
32,181 Expert Mod 16PB
Much water has passed below this bridge. I'll recap some of it if I can.

AccessIdiot: However, I had my coworker log in to a machine and launch it and the navigation pane shows up?
This indicates that something isn't working as it should. I may have to look at the database after all. The Form_Open() event procedure should handle hiding the database window for all users. The only difference for the Admin person(s) is in the Form_Close() one.
AccessIdiot:
Expand|Select|Wrap|Line Numbers
  1. Private Const conClear As String = "Administrator;MKM"
or is that what you were saying is difficult to do?
No. I think you said you'd prefer to manage this with a table. The next point deals with that directly, but for now we can do a test that this part is working if you let me know what the UserName is that is being used. To get this information go to the VBA IDE (Alt-F11) and then the Immediate pane (Ctrl-G) then execute :
Expand|Select|Wrap|Line Numbers
  1. ?Environ("UserName")
Post the result back in here if you would. That will also be added to the conClear declaration as "Administrator;MKM;New Value".
NeoPa: If you want a table approach, may I suggest that you first create a table to hold the data. I doubt you'll need help with that part.
If you prepare this now then it will exist by the time you attach your database.

If you are still noticing problems then I suggest this point would be a good one to attach your database to the thread. Let me know if ever you want an attachment removed after I've downloaded it myself. Otherwise I'm happy to give you my email address (if you don't have it already) and I'm happy for you to use that if you'd prefer.
Aug 13 '10 #43
AccessIdiot
493 256MB
I'm afraid I'm gone for the next few days. I'll address when I return, thanks.
Aug 13 '10 #44
NeoPa
32,181 Expert Mod 16PB
No worries M.

It's a shame to miss the opportunity of the weekend, but that's how it is. I'll respond when next you post.

Cheers.
Aug 13 '10 #45
AccessIdiot
493 256MB
Okay I'm back and really confused. Maybe I just needed to be logged off and away for four days, I don't know.

When I launched the db this morning the navigation pane did not show. I tried your ?Environ("UserName") and it returned the value I have in the conClear As String variable. Same thing happens when one of my coworkers tries it from their login.

Anyway, here is the db attached with a simple login table. Hope it's right.
Attached Files
File Type: zip DeerFlat_2010-08-17.zip (2.02 MB, 168 views)
Aug 17 '10 #46
NeoPa
32,181 Expert Mod 16PB
I'll get to look at it tomorrow I expect.
Aug 17 '10 #47
AccessIdiot
493 256MB
Any ideas would be greatly appreciated, as always!
Aug 18 '10 #48
NeoPa
32,181 Expert Mod 16PB
I got no time to look at it today I'm afraid M.

I took a day's holiday with a few chores to keep it interesting. I finished the chores with just enough time to get ready to go out at 19:00 :( Now I need a holiday to recover from my holiday.

I'll try to look into this tomorrow after work, but I've downloaded the database at least :)
Aug 18 '10 #49
NeoPa
32,181 Expert Mod 16PB
I don't know how you're situation over the weekend M. I didn't get around to doing anything on this last night (obviously) and I'm out till about 20:00 tonight.

I may get a chance to overlap with you tonight for a short while, otherwise are you around at the weekend?
Aug 20 '10 #50

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by dan glenn | last post: by
reply views Thread by Ray Lavelle | last post: by
1 post views Thread by Ed B | last post: by
2 posts views Thread by ashish | last post: by
15 posts views Thread by =?Utf-8?B?QmVuamFtaW4gSmFuZWNrZQ==?= | last post: by
2 posts views Thread by Chris Zopers | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Purva khokhar | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.