How supply password in VBA during CompactOnClose for back-end password-protected db?
Microsoft Access, VBA, Access, Access 2000
Access 2000:Goal: to open and close a password-protected public access db (which compacts on close) and hide the "Password Required" prompt on open and on close.
Purpose: Provide public access to Main.mde and protect Main.mde from remote connections, i.e, import & link.
I have password protected "Main.mde". Main.mde compacts on close.
If I open Main.mde directly, I'm prompted for the password on open but not on close, so I created Open.mde to open Main.mde and supply the password on open.
When I open it from "Open.mde" and supply the password with vba, Main.mde opens without prompting for a password, so I'm half way there.
But when Main.mde is closed (having been opened from Open.mde) the password prompt displays.
If I remove the "compact on close" setting in Main.mde, the prompt on close does not display, but I really need this db to compact on close.
So, I can avoid the prompt on close with the direct method, or I can avoid the prompt on open with the remote method, but I can't avoid both the open & close prompts with a single method.
Question1: Is there a way to provide the password from Open.mde for both the open & close of Main.mde, or perhaps, another way to accomplish my goal? I believe the compact on close process includes exporting objects to a blank db, which may be why the "Password Required" pops up during the close.
I realize there is no getting around the password prompt. If there is no way to supply it with VBA on close, then...
Question2: Is there a way, with VBA, to recognize the "Password Required" pop-up as the active window, then do a "SendKeys" or copy the password in a string with a "Clipboard Copy" then do a "Clipboard Paste" (followed by a "SendKeys" {enter}, perhaps...) to supply the password?
Thank You.
Here is the code I'm using from Open.mde to open Main.mde.
Dim Errnum As Long
Dim ErrMsg As String
On Error GoTo ErrorHandler
Dim cRes As String, ourPath
Dim nPos As Long
cRes = CurrentDb.Name
nPos = Len(cRes)
'Find the current path
Do Until Right(cRes, 1) = "\"
nPos = nPos - 1
cRes = Left(cRes, nPos)
Loop
ourPath = cRes
'MsgBox ourPath
Dim stPath As String, stDBName As String
stDBName = "Main.mde"
stPath = ourPath & stDBName
'MsgBox stPath
Dim wrk As Workspace
Dim dbProtected As Database
'Define as Static so the instance of Access doesn't close when the procedure ends.
Static acc As Access.Application
Dim db As DAO.Database
Set acc = New Access.Application
acc.Visible = True
Set db = acc.DBEngine.OpenDatabase(stPath, False, False, ";PWD=PASSWORD")
acc.OpenCurrentDatabase stPath
db.Close
Set db = Nothing
Dim stFileName
stFileName = "Main.ldb"
stPath = ourPath & stFileName
Dim fso As Scripting.FileSystemObject, xExistingFile As String
Set fso = New Scripting.FileSystemObject
xExistingFile = stPath
'Loop here while Main.mde is open so I can Quit and keep
'the user from manually having to close the instance
While fso.FileExists(xExistingFile) = True
DoEvents
Wend
DoCmd.Quit
0
