DOCUMENT:Q192919  21-SEP-1998  [vbwin]
TITLE   :HOWTO: Automate a Secured Access Database Using Visual Basic
PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:WINDOWS:5.0,6.0,97
OPER/SYS:WINDOWS
KEYWORDS:

======================================================================
---------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual Basic Learning, Professional, and Enterprise Editions
   for Windows, versions 5.0, 6.0
 - Microsoft Access 97
---------------------------------------------------------------------

SUMMARY
=======

There is no Automation method in the object model of Access that allows
Visual Basic to open a secured Access database without getting a prompt
requesting a username and password. However, it is possible to accomplish
this using the Shell command. This article demonstrates how to open a
secured Access database without getting a prompt.

MORE INFORMATION
================

There are two ways to secure a Microsoft Access database. One is to furnish
individual MDBs with passwords. Although in DAO, you can use the
OpenDatabase method to open such a database without getting a password
prompt, there is no method to do so in Access. The second method is to
provide a series of usernames and passwords to secure Access itself. The
username and password prompt can be avoided in this case by using the Shell
command and the GetObject method.

The main problem with using the Shell command to open a secured Access
database is that Access does not register itself in the running object
table until it has lost focus once. This means that until Access loses
focus, it cannot be found with a call to GetObject and automated. The
following Visual Basic code demonstrates how to launch a secured Access
database and get the running instance of Access so it can be automated.

Step by Step Example
--------------------

1. Open a new Standard EXE project in Visual Basic. Form1 is created by
   default.

2. Choose References from the Project menu, check "Microsoft Access 8.0
   Object Library," and then click OK.

3. Add a CommandButton to Form1 and put the following code into Form1's
   code window:

      Private Declare Sub Sleep Lib "Kernel32" (ByVal dwMS As Long)

      Private Sub Command1_Click()
      Dim accObj As Access.application, Msg As String
      Dim application As String, dbs As String, workgroup As String
      Dim user As String, password As String, cTries As Integer
      Dim x

      ' This is the default location of Access
      application = "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE"
      ' Use the path and name of a secured MDB on your system
      dbs = "C:\TestDatabase.mdb"
      ' This is the default workgroup
      workgroup = "C:\Windows\System\System.mdw "
      user = "Admin"           ' Use a valid username
      password = "Mypassword"  ' and correct password

      x = Shell(application & " " & dbs & " /nostartup /user " & user & _
         " /pwd " & password & " /wrkgrp " & workgroup, vbMinimizedFocus)

      On Error GoTo WAITFORACCESS
      Set accObj = GetObject(, "Access.Application")

      ' Turn off error handling
      On Error GoTo 0

      ' You can now use the accObj reference to automate Access

      Msg = "Access is now open. You can click on Microsoft Access "
      Msg = Msg & "in the Taskbar to see that your database is open."
      Msg = Msg & vbCrLf & vbCrLf & "When ready, click OK to close."
      MsgBox Msg, , "Success!"

      accObj.CloseCurrentDatabase
      accObj.Quit

      Set accObj = Nothing
      MsgBox "All Done!", vbMsgBoxSetForeground

      Exit Sub

      WAITFORACCESS:               ' <--- This line must be left-aligned.
      ' Access isn't registered in the Running Object Table yet, so call
      ' SetFocus to take focus from Access, wait half a second, and try
      ' again. If you try five times and fail, then something has probably
      ' gone wrong, so warn the user and exit.
      SetFocus
      If cTries < 5 Then
         cTries = cTries + 1
         Sleep 500 ' wait 1/2 seconds
         Resume
      Else
         MsgBox "Access is taking too long. Process ended.", _
            vbMsgBoxSetForeground
      End If
      End Sub

4. Run the project and click on Command1. Your secured MDB will open
   without prompting you, and a message box will pause the code so that you
   can verify that your database is actually open. You can then click OK to
   dismiss the message box and close Access.

REFERENCES
==========

For additional information, please see the following articles in the
Microsoft Knowledge Base:

   ARTICLE-ID: Q132143 147816
   TITLE     : ACC: Overview of How to Secure a Microsoft Access Database

   ARTICLE-ID: Q147816
   TITLE     : ACC: Using Microsoft Access as an Automation Server

Additional query words: kbDSupport kbDSD kbVBp kbVBp500 kbVBp600 kbVBA
kbAccess kbAccess97 kbNoKeyWord
======================================================================
Version           : WINDOWS:5.0,6.0,97
Platform          : WINDOWS
Issue type        : kbhowto

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1998.