You are currently offline, waiting for your internet to reconnect

How To Automate a Secured Access Database Using Visual Basic

This article was previously published under Q192919
SUMMARY
There is no Automation method in the object model of Access that allowsVisual Basic to open a secured Access database without getting a promptrequesting a username and password. However, it is possible to accomplishthis using the Shell command. This article demonstrates how to open asecured Access database without getting a prompt.
MORE INFORMATION
There are two ways to secure a Microsoft Access database:

  • One is to furnishindividual MDBs with passwords. Although in DAO, you can use theOpenDatabase method to open such a database without getting a passwordprompt, 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 toprovide a series of usernames and passwords to secure Access itself. Theusername and password prompt can be avoided in this case by using the Shellcommand and the GetObject method.
The main problem with using the Shell command to open a secured Accessdatabase is that Access does not register itself in the running objecttable until it has lost focus once. This means that until Access losesfocus, it cannot be found with a call to GetObject and automated. Thefollowing Visual Basic code demonstrates how to launch a secured Accessdatabase 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.
REFERENCES
For additional information, please see the following articles in theMicrosoft 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
Properties

Article ID: 192919 - Last Review: 01/23/2007 18:08:57 - Revision: 5.4

  • Microsoft Visual Basic 5.0 Learning Edition
  • Microsoft Visual Basic 6.0 Learning Edition
  • Microsoft Visual Basic 5.0 Professional Edition
  • Microsoft Visual Basic 6.0 Professional Edition
  • Microsoft Visual Basic 5.0 Enterprise Edition
  • Microsoft Visual Basic Enterprise Edition for Windows 6.0
  • Microsoft Office XP Developer Edition
  • Microsoft Office 2000 Developer Edition
  • Microsoft Access 2002 Standard Edition
  • Microsoft Access 2000 Standard Edition
  • Microsoft Access 97 Standard Edition
  • kbhowto KB192919
Feedback