Modules: Specify UserName & Password for new Access instance

MDW Security

    Automation does not allow you to specify a different MDW file along with a new username and password when opening a secured database from code.  One way to get around this limitation is to Shell out to the secured mdb whiile specifying all necessary information through command line parameters.

'************* Code Start *****************
Sub sOpenDBWithPwd()
Dim strDB As String
Dim strCmd As String
Dim objSecuredDB As Access.Application
  strDB = "J:\NewCode97.mdb"
  strCmd = SysCmd(acSysCmdAccessDir) & "\MSAccess.exe " _
      & strDB & " /wrkgrp " & DBEngine.SystemDB _
      & " /user Admin"  '/pwd ''"
  Call Shell(strCmd, vbNormalFocus)
  DoEvents: DoEvents: DoEvents
  Set objSecuredDB = GetObject(strDB)
  Stop
End Sub
'************* Code End *****************

Database Password

To open a database that's secured via the database password through Automation, open it in code  first, specifying the optional password in the OpenDatabase method's arguments.  A subsequent call to OpenCurrentDatabase for the same database will force Access to reuse Jet's setting for the open database.

Note that this technique will also work with TransferDatabase and CopyObject in allowing you to specify the database password.

'************* Code Start *****************
Function foo()
    Dim db As Database
    Dim oAcc As Access.Application

    Const TMP = "fooz.mdb"

    Set db = DBEngine.CreateDatabase(TMP, _
                        dbLangGeneral)
    db.NewPassword "", "doooo"
    db.Close
    Set db = Nothing

    Set oAcc = New Access.Application
    Set db = oAcc.DBEngine.OpenDatabase(TMP, _
                            False, False, ";PWD=doooo")
    oAcc.OpenCurrentDatabase TMP
    db.Close
    Set db = Nothing
End Function
'************* Code End *****************