Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

How To Automate a Secured Access Database Using Visual Basic


View products that this article applies to.

This article was previously published under Q192919

↑ Back to the top


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.

↑ Back to the top


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 prior to Access 2002. The Application.OpenCurrentDatabase method of Access2002 includes and optional parameter to specify the database password.

  • 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. For Access 2000, check Microsoft Access 9.0 Object Library. For Access 2002, check Microsoft Access 10.0 Object Library.
  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 & " " & Chr(34) & dbs & Chr(34) & " /nostartup /user " & user & _
          " /pwd " & password & " /wrkgrp " & Chr(34) & workgroup & Chr(34), 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.

↑ Back to the top


References

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

132143� ACC: Overview of How to Secure a Microsoft Access Database
235422� ACC2000: How to Open a Password-Protected DB Through Automation
147816� ACC: Using Microsoft Access as an Automation Server

↑ Back to the top


Keywords: kbhowto, KB192919

↑ Back to the top

Article Info
Article ID : 192919
Revision : 9
Created on : 1/23/2007
Published on : 1/23/2007
Exists online : False
Views : 743