469,281 Members | 2,486 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,281 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
58 9141
AccessIdiot
493 256MB
Unfortunately not around the database on the weekend and my 4 yr old keeps me away from the computer mostly (she's asleep right now). I'll be back at it Monday morning PNW time. Have a great weekend!
Aug 21 '10 #51
NeoPa
32,173 Expert Mod 16PB
M, All your clever code is completely bypassed if you click on Exit (Your Command Button). Instead of closing serenely using :
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.Close
it quits Access immediately and irreversibly, using :
Expand|Select|Wrap|Line Numbers
  1. DoCmd.Quit
Also, assuming you want the database to close (rather than just the form or even the whole application), you should simply use :
Expand|Select|Wrap|Line Numbers
  1. Call CloseCurrentDatabase
In short, your Form_Close() subroutine should read :
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 CloseCurrentDatabase
  12.     End If
  13. End Sub
Everything else I could see worked exactly as expected (I even added in my ID and it switched me back to the Database Window on close, instead of dropping out of the database completely).

PS. Your cmdViewReport_Click() subroutine should say :
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdViewReport_Click()
  2.  DoCmd.OpenReport "rpt_Report", acViewPreview
  3. End Sub
acViewreport is not a valid parameter value (It's undefined). With that fixed the project compiles (which is always very important).
Aug 23 '10 #52
AccessIdiot
493 256MB
Thanks A, that cleans it up nicely. My code is very much hacked together as you can tell.

Still having an issue however. When I am logged in to my machine and I launch the db I don't get the navigation pane. If I right click on the switchboard form and select "close" then the form closes but I get the navigation pane.

This doesn't happen when my coworker tries it from his machine (his login), just mine. Any ideas?
Aug 23 '10 #53
NeoPa
32,173 Expert Mod 16PB
M:
Any ideas?
Only that this is exactly what I would expect to see. This is the whole idea we've been looking to implement, to have separate results for those in the list, from those not in it. The Database Window (Navigation Pane if you prefer) is designed (in this database) not to show until someone in the list closes out the database. At this point M (or A when I was running it) will get the Database Window left open and accessible, whereas all others will find that the database has been closed entirely, leaving only an open Access window.

What were you expecting that is different from this?
Aug 23 '10 #54
AccessIdiot
493 256MB
Got it. I was expecting that the nav panel would display as soon as the db was launched if the appropriate person was logged in. It is fine the way it is now, I just need to explain to the client what they should expect to see and how it works. Thanks A!
Aug 23 '10 #55
AccessIdiot
493 256MB
Another question: is there a way to remove the right click option to design forms (layout option too) for those that aren't allowed?
Aug 23 '10 #56
NeoPa
32,173 Expert Mod 16PB
M:
Got it. I was expecting that the nav panel would display as soon as the db was launched if the appropriate person was logged in.
That can easily be done that way too, if that would help. I figured it would simply clutter up the screen if the database is running. The current way, it shows either the running database, or the Database Window. Never both at the same time. Let me know if you'd prefer the Database Window to be open at all times for the selected users though.
Aug 24 '10 #57
NeoPa
32,173 Expert Mod 16PB
M:
Another question: is there a way to remove the right click option to design forms (layout option too) for those that aren't allowed?
Not that I'm familiar with. Similar code wouldn't do it I don't think. This is taking the solution well beyond the original stated requirement (which was fundamentally to make fiddling unobvious rather than blocked). If stronger protection is required then a whole different approach would be needed. We'd have to throw away and start again from scratch if this feature were important.
Aug 24 '10 #58
AccessIdiot
493 256MB
Nope, just thought I'd ask. I think making the tables not apparent is more important at this stage than protecting the design. Thanks again!
Aug 24 '10 #59

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
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.