Hiding the Access 2013 Application during database use...

[sooli]

how in the world do i hide the database window? The thing takes up the whole screen, all my users need are the forms I created... used to be a check box that would let you not display the application... that feature seems to be gone in 2013 =/

[twinnyfo]

Here is what I use:

I created a separate module called "modFormUtilities". In it are several functions.

Whenever you use these functions, your forms should all be set to Modal and Pop-up. This allows your Forms to have control of the environment, rather than the DB window.

There are some comments in the RestoreForm Function that explains how to use it. Let me know if you run across any snags.

Option Compare Database
Option Explicit
 
'==============================
'  CONSTANTS FOR DBASE WINDOW
'==============================
Public Const SW_HIDE = 0
Public Const SW_SHOWNORMAL = 1
Public Const SW_SHOWMINIMIZED = 2
Public Const SW_SHOWMAXIMIZED = 3
 
'==========================
'  DBASE WINDOW FUNCTIONS
'==========================
Public Declare Function IsWindowVisible Lib "user32" _
        (ByVal hwnd As Long) _
    As Long
Public Declare Function ShowWindow Lib "user32" _
        (ByVal hwnd As Long, _
        ByVal nCmdShow As Long) _
    As Long
 
'============================
' FORM MANAGEMENT FUNCTIONS
'============================
 
Public Function fAccessWindow(Optional Procedure As String, _
    Optional SwitchStatus As Boolean, Optional StatusCheck As Boolean) As Boolean
'****************************************************************************************
'* Purpose:  Controls the behavior and appearance of the Daatabase window               *
'*           residing on the form, and hence, we reduce the overall size of the FE DB.  *
'* Accepts:  Procedure -    describes the behavior requested                            *
'*           SwitchStatus - (optional) instructs the procedure to switch the current    *
'*                          status of the DB Window                                     *
'*           StatusCheck -  (optional) allows the procedure to check the current status *
'*                          of the DB window to determine whether or not to initiate a  *
'*                          change                                                      *
'****************************************************************************************
On Error GoTo EH
    Dim dwReturn As Long
    If Procedure = "Hide" Then
        dwReturn = ShowWindow(Application.hWndAccessApp, SW_HIDE)
    End If
    If Procedure = "Minimize" Then
        dwReturn = ShowWindow(Application.hWndAccessApp, SW_SHOWMINIMIZED)
    End If
    If Procedure = "Normal" Then
        dwReturn = ShowWindow(Application.hWndAccessApp, SW_SHOWNORMAL)
    End If
    If Procedure = "Show" Then
        dwReturn = ShowWindow(Application.hWndAccessApp, SW_SHOWMAXIMIZED)
    End If
    If SwitchStatus = True Then
        If IsWindowVisible(hWndAccessApp) = 1 Then
            dwReturn = ShowWindow(Application.hWndAccessApp, SW_HIDE)
        Else
            dwReturn = ShowWindow(Application.hWndAccessApp, SW_SHOWMAXIMIZED)
        End If
    End If
    If StatusCheck = True Then
        If IsWindowVisible(hWndAccessApp) = 0 Then
            fAccessWindow = False
        End If
        If IsWindowVisible(hWndAccessApp) = 1 Then
            fAccessWindow = True
        End If
    End If
    Exit Function
EH:
    MsgBox "There was an error resetting the Database Window!  " & _
        "Please contact your Database Administrator.", vbCritical, "Error!"
    Exit Function
End Function
 
Public Function RestoreForm(FormName As String)
'**********************************************************************************
'* Purpose:  Manages the database window, keep the current form on top of others. *
'* Accepts:  FormName is the name of the Form which you want to restore           *
'* Returns:  N/A                                                                  *
'*   Usage:  Add "RestoreForm Me.Form.Name" to the OnOpen event of a form         *
'**********************************************************************************
On Error GoTo EH
    fAccessWindow "Minimize", False, False
    DoCmd.SelectObject acForm, FormName
    DoCmd.Restore
    Exit Function
EH:
    MsgBox "There was an error restoring the Form.  " & _
        "Please contact your Database Administrator.", vbOKOnly, "Error!"
    Exit Function
End Function

Hope this hepps!

[sooli]

got error from click event... tried changing to modal..and it didn't help (one form will have to be non-modal so the boss man can copy and paste from the form opened by a click event.)

Error I am getting:

The expression On Click you entered as the event property setting produced the following error: constants, Fixed-length strings, arrays, user-defined types and Declare statements not allowed as Public members of object modules
*The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]
*There may have been an error evaluating the function, event or macro


I made sure none of your sub names were the same as mine... so * 1 is not the issue...

[twinnyfo]

This code should not run on any OnClick events. The error described does not appear to be associated with any of the code provided, as the error trapping included would not generate any such error. How are you calling the code? It should be in the OnOpen Event of the form.

You can still use Modal for all forms (as the most recently opened form will open on top. You could either close that form after you copy and then paste into the original form or have other ways to transfer data.

[sooli]

I put the code into the OnOpen event. Error didn't occur till I entered the code, when I removed the code the error went away. When I debugged, it sent me straight to your code where the constants were defined. I'll play with it a bit and see if I can resolve - maybe pinpoint the exact spot that is causing the error... for now everyone is happy with the database as is... so this will be something to provide in a version update later. Thanks for you assistance on this and my other issue.. still trying to work that one out as well...

[twinnyfo]

Is the code I posted in a separate module--all by itself and NOT in a Form. Your Form should only have the following line in the OnOpen event:

RestoreForm Me.Form.Name

You also did not tell us what the error was when you debugged.

[sooli]

I did not make a separate module... i put it at the head of the module for the main form. I will try it as a separate module =)

When I debugged, it just put me on the 1st line of code where the constants were declared. I didn't get an explanation =( Only error I got was the one I posted earlier.

[jimatqsi]

Twinnyfo, this looks to be very useful. I'm having some trouble getting Access 2010 Runtime to behave. People are able to see the database window and even change queries!

Are there any version restrictions with this code?

Perhaps this could be the basis of an article. :)

Jim

[twinnyfo]

sooli,

Have you made any progress on this?

[sooli]

Twinnyfo!!!! You are AWESOME!!!! I got it to work and didn't have to tweak any of your code! I just had to call it properly!

Sorry took so long to get to this point! Plate is overflowing!

Question... is there anyway to keep the user from bringing the window back? You cannot see it while actively using the database... which is great, however if you hover over the areo peak, you can bring it right back up... and if I try to go back to design view it closes the database...

I did find that I can open the database using shift... and get to the design view which is fine... and I have the working copy of the database set so that navigation and menu's are disabled... so they can't do anything with it... just want it to not be retrievable at all by the user.

[Brilstern]

sooli,

Assuming you login with a unique username you can set form properties on load.

Example: (pseudo code)

If Me.Username = "Admin" Then
Me.Control.Visible = true
Else
Me.Control.Visible = False
End If

This button could allow for a close option for you.

To answer your other question:
Set the On Close Event to quit the database. This would force the database to close if a user uses the close in the navigation.

Another option would be to disable the close in the navigation altogether in the form properties. This would force the user to log out.

[twinnyfo]

@sooli,

When I create my DBs, I always do this:

1. There is always a Front-End/Back-End structure, so the DATA is always separated from the CODE. If your Front-End (the Code) crashes, then you don't lose your data. The data side very seldom (if ever) crashes.
2. When the Front end if opened, it checks to see how long it has been since the DB has been updated. My schedule is every six hours. If it has been six hours, the DATA is backed up again (just in case the data does crash).
3. My shortcut does not point to the DB itself, but to a MS Scripting file, which copies the DB to the local user's machine. If the local version crashes, it does not corrupt the master copy of the front-end.
4. My Front-End is ALWAYS compiled (.accde or .mde file), so no one can access the code or modify any of the objects.
5. All navigation controls are disabled in the DB (including the Nav pane, shortcut menus, etc.). I have not found an effective method for bypassing the "Shift-Key" method. Any experts out there know a simple way to do this?
6. My DBs log who has accessed the DB and assigns permissions to the user that I establish. Each user can only see what I let them see.
7. All forms are locked down (no built in controls, like Minimize, maximize and close). My users can only do what I want them to do with the forms. Navigation ultimately leads them to either do their job or exit the DB.

I use these principles because people are inherently inquisitive and like to snoop around DBs. I do everything I can to prevent this.

Here are some links to a couple articles that may assist in these tasks:

Working with Front-Ends and Back-Ends

MS Access User Permissions

[Brilstern]

Nice business rules twinnyfo. Going to have to put those in the toolbox for future projects.

[twinnyfo]

@Stevan,

I have been DBasing for about 20 years, and, unfortunately, I learned just about everything I know via bad experience. Up until maybe 5-6 years ago, I did not know what DB normalization was. I practiced those principles almost exclusively, but it was not because I was taught the principles. I learned--by creating poorly designed DBs--that there were certain principles that made for a better design. Now I am able to talk intelligently about those principles.

Thanks to this site, primarily, I have learned much more about good design and programming concepts that have both re-inforced many practices and corrected some others. I want to spare others my bad experiences by guiding these young Jedis in the good ways of the Force.

I don't know what experience you have, but thanks for joining us!

[Brilstern]

@twinnyfo,

Now that we have successfully hijacked sooli's thread (hah just joking!) we appreciate the willingness of those that know to pass on the information.

I built my first database in 2011 and have found myself building one here and there ever since. My goal is always to make my next DB better than my last. Bytes has truly helped me do that!

[sooli]

@twinnyfo,

I would love to create the .accde! but it seems access 2013 doesn't afford that luxury. Or I need an addin that I don't know about. I guess when they took away the ability to hide the database with a check box in the database properties, they also removed that feature. The .accde is the one that actually keeps the users from being able to get to the database itself, or make changes to the forms.. i have all that locked down to an extent - but as you are aware... curiosity can lead to sneaky back door access... My boss didn't want the database to have a password to access it, so I was careful to create my own switch with nothing other than what the users are supposed to be able to do, and a password protected menu for administration which is reached thru a cmd button. The database is split so the user only has the front end, and so far I have only had one nosey nelly that has found the tables, and he actually went on the server and opened the database directly... he has been reprimanded for this, and everyone was made very aware that the database itself is not for their use EVER.

I tried to eliminate the upper right controls, but to my dismay, when I used a cmd button to minimize the form, the user was not able to bring it back, so I had to leave those on the screen because the user wants to be able to minimize the data base when not actively using it, and the general consensus was to leave the database open/available for the whole shift, rather than open it every time they needed to enter something.

I have a script on my system that runs a backup of the database every hour, since we have it residing on a san server, and have already experienced the "reason for having a san" fallacy... my database in its entirety just up and vanished one night, thankfully I was in it just as it happened and i was able to contact my server admin immediately and he was able to recover it, though we lost 6 hours worth of data, since his back up only runs every 8 hours. we are a 24x7 operation...

I too try to follow the same guidelines you posted, these came about, fortunately when i learned how to design in Access, eon's ago... in school. That was part of my Office training when Windows 95 was still around... over the years, as MS has made changes to the product itself, I was able to continue manipulating things to my personal whim, until 2013 came out... now I am finding the basic things that made Access a great "small" customizable database have abruptly become extinct.

Anyway... the code you posted previously works great, except that windows aero peek allows you to bring it back.. useless but back none the less... so I will just let my crew know, not to click the aero peek unless they want to restart the database.

I greatly appreciate your assistance!

[sooli]

@ Stevan Bias - I actually have something like your code for my admin functions. =) I am using it for subforms that only the admin can get to, and he loves it.

I do have the X on the switch set to close and compact the database, on all other forms the X takes the user back to the switch, as people are creatures of habit and love that red X, but hate to have to keep re-opening the database... lol

[twinnyfo]

sooli,

I use Access 2010 at work, and did some research and found nothing about 2013 that says you can't create .accde files. Are you perhaps looking in the wrong places?

In 2010, go to File, Save & Publish, Make ACCDE. 2013 should have a similar enough interface that it should be there, unless you have some form of limited version of Access 2013....

[sooli]

I tried to save as accde and ran into issues... i'll try to work them out, but in the meantime...

I still need a work around for using the icon in the task bar to bring the database back on the screen when it is minimized... it brings the application window back too. Boss is now wanting to eliminate the possibility of anyone trying to access the tables and making their own updates. I have the database itself password protected, so if a user attempts to open the mdb file, it prompts for password and if you get it wrong 3 times, the application just closes... I used a form inside the database to do this... was quick and simple way - without affecting the users ability to still update tables using the accdb file.

Trying to encrypt a sample now... to test that out... but fear that will require a password for the users to enter... and he doesn't want that. (we have too many passwords to remember as it is...)

Currently I have the properties set so navigation and menus are disabled, which for most of my users is sufficient. However... the shift button over-rides this...

I was thinking maybe I could Hide the list of tables? this way if someone were to gain access to the navigation field, they wouldn't be able to see them? but I wasn't sure what this would do to usability? Will the tables still be linked?

I tried it earlier today, bringing them back was a little tricky cause I didn't know how... I had done it in separate groups... so when i tried to bring them all back at once... I couldn't... there wasn't even an option... imagine my panic! lol.

If I hide them, will it stay hidden when the user copies the front end to the desktop - and will they stay linked? I am brain storming... so sorry if this seems to be something I should be able to test on my own... i cannot exactly use my users as guinea pig...

[jforbes]

I think the root of the problem here has to do with storing sensitive information in a place where people that should not have permission can get to it. This really isn't so much an MS-Access problem that you are running into, but a Permission/Security issue. Access wasn't really created to address permissions... well it dabbled in it for a while early on and then backed off.

There are a few common ways to address permissions/security when it comes to data, there are more, but I'm only gonna touch on three:

• File System permissions is a very effective method by keeping sensitive information on a FileShare and then either allowing or taking away permissions based on the users Windows login. It is a much better way at securing data than attempting to lock down MS-Access because the permission are managed and enforced by a Server on the Network vs embedding security in a file.
• Encryption is handy and sometimes the best way depending on the applications needs, but I really wouldn't think this would be an option for you as it tends to take a considerable amount of development and overhead when running.
• Network Security can be a great way to lockdown data as that is one of its primary functions. Doing this would take either a custom application or use of an application that provides this for you. One application that is often used for this is MS-SQL Server.

If you can use a SQL Server, I would recommend it as there are a lot of benefits in addition to taking care of the problem you are running into for this post. You can setup the authentication based on the Logged in Windows User so that only people that can login to you network can access data as well as you can give and remove permissions on a user by user basis.

The other option, if you can't talk your Boss into a SQL Server, is to split your backend database based on group roles. You don't have to have only one backend database. You can split it by job function and place data specific for a certain job function into it's own FileShare on your network. Then control users permission on the FileShare. This way Jimmy from out in the shop can enter whatever he needs to enter, but can't see all the interesting comments Mary in HR is entering about him into the same system. SQL Sever would be better, but if your Boss has short arms and deep pockets, this approach may work for you.

[sooli]

well, the sql option is not really an option - I have the file on the server it resides locked down so no one on the outside can get in, but what I am having issues with now is the pesky navigation screen giving access to all the tables if a user happens to figure out how to bring it back. All the linked tables are accessible if the navigation pane is unlocked, which is easy to do, just hold shift while opening the front end. I only have 2 groups of folks, Admins and the users... and I have that separated by a password. I discovered I could hide the linked tables from view if the navigation pain happens to be unlocked... just wasn't sure if it would cause any issues. Being that I work 3rd shift, and the only other person I can use as a guinea pig is a "user" I didn't want to go that route to verify it would still provide access to input like it is supposed to. It looks like it works... so I guess I'll just give it a run for a little while and see how it goes.

In order for my users to be able to input data, they have to have read write access to the folder the database is in right? And even if I could modify that, I don't think it has any bearings on their ability to open the "linked" tables in the application.

Thanks everyone for your input and suggestions... IF anyone figures out a work around for the AERO PEEK issue... please let me know.

[twinnyfo]

Try this:

Public Sub DisableByPass()
    ChangeProperty "AllowBypassKey", dbBoolean, False
End Sub
Private Function ChangeProperty(strPropertyName As String, _
    varPropertyType As Variant, varPropertyValue As Variant) As Integer
On Error GoTo Err_ChangeProperty
    Dim dbACC As DAO.Database
    Dim prpProperty As Property
 
    Set dbACC = CurrentDb()
 
    'Property exists, so set its Value
    dbACC.Properties(strPropertyName) = varPropertyValue
    ChangeProperty = True
 
Exit_ChangeProperty:
    Set dbACC = Nothing
    Exit Function
 
Err_ChangeProperty:
    If Err.Number = 3270 Then       'Property not found
        'Since the Property isn't found, create it!
        Set prpProperty = _
            dbACC.CreateProperty(strPropertyName, varPropertyType, varPropertyValue)
        dbACC.Properties.Append prpProperty
        Resume Next
    Else
        'Unknown Error
        ChangeProperty = False
        Resume Exit_ChangeProperty
    End If
End Function

This will disable the Shift-Key workaround.

[Rabbit]

Because of the nature of Access, anything that you do can be undone by a user. Even disabling the shift key bypass.

Because of this drawback to Access, it is highly recommended not to use Access if there is a real need for security.

[NeoPa]

Making the Database Window hidden via code can be done by first selecting something in the window, then hiding that window. Here's some code for that :

Call Application.Echo(EchoOn:=False)
Call DoCmd.SelectObject(ObjectType:=acForm, InDatabaseWindow:=True)
Call DoCmd.RunCommand(Command:=acCmdWindowHide)
Call Application.Echo(EchoOn:=True)

This uses calls to Application.Echo() because when this code is run when the window is already hidden it can cause a flicker.