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

[AccessIdiot]

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?

[NeoPa]

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?

[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?

How do I hide the database window? Or password protect it? I'm a little unclear on this part.

[AccessIdiot]

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?

[NeoPa]

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.

[NeoPa]

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.

[Jerry Maiapu]

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.

[AccessIdiot]

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!

[NeoPa]

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.

[AccessIdiot]

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.

[NeoPa]

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?

[AccessIdiot]

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:

Private Sub cmdSwitchboard_Click()
  DoCmd.Close acForm, "frm_Welcome"
 DoCmd.OpenForm "Switchboard"
End Sub

On the OpenForm event of the form I have

DoCmd.NavigateTo "acNavigationCategoryObjectType"
 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?

[NeoPa]

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.

Option Compare Database
Option Explicit

'Windows API Variable Prefixes
'cb = Count of Bytes (32-bit)
'w  = Word (16-bit)
'dw = Double Word (32-bit)
'lp = Long Pointer (32-bit)
'b  = Boolean (32-bit)
'h  = Handle (32-bit)
'ul = Unsigned Long (32-bit)

Private Const conWinVis As Long = &H10000000
Private Const conGWLStyle As Long = -16

Private Declare Function FindWindowEx Lib "user32" _
    Alias "FindWindowExA" (ByVal hwndParent As Long, _
                           ByVal hwndChildAfter As Long, _
                           ByVal lpszClass As String, _
                           ByVal lpszWindow As String) As Long
Private Declare Function GetWindowLong Lib "user32" _
    Alias "GetWindowLongA" (ByVal hwndID As Long, _
                            ByVal nIndex As Long) As Long

Public Function DBWindowVisible() As Boolean
    Dim hWnd As Long, lngStyle As Long

    'Get handle of MDIClient window of current application
    hWnd = FindWindowEx(hWndAccessApp, 0, "MDIClient", vbNullString)
    'Within that, find child window matching class Odb (database window)
    hWnd = FindWindowEx(hWnd, 0, "Odb", vbNullString)
    'Default result to False in case handle wasn't found
    DBWindowVisible = False
    If (hWnd) Then
        'Having found window, check the visibility flag of its style value
        lngStyle = GetWindowLong(hWnd, conGWLStyle)
        DBWindowVisible = ((lngStyle And conWinVis) = conWinVis)
    End If
End Function

I suggest you get this setup while I dig up the other related stuff.

[AccessIdiot]

Dumb question #1: where does this go?

[NeoPa]

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.