Programatically opening an Access database (any format).
The file has been password protected.
To be clear, this IS NOT a startup form prompting for login credentials.
This is a password protected file prompting for the password when you open it.
Question:
Is there some way to programatically send a cancel to the dialog?
I've tried a few things, but it seems to be stopped at that point requiring a user click (OK or CANCEL).
Programatically opening an Access database (any format).
The file has been password protected.
To be clear, this IS NOT a startup form prompting for login credentials.
This is a password protected file prompting for the password when you open it.
Question:
Is there some way to programatically send a cancel to the dialog?
I've tried a few things, but it seems to be stopped at that point requiring a user click (OK or CANCEL).
How are you trying to open the database? The reason I ask is that I believe we need to modify that because hitting Cancel will not open the database anyway so why try if you can't open it? So what are you currently using to open it?
HI BOB!!!
Long time no see!
I hope all is well for you!
The database is bing opened from an Access Object running within an Excel Add-in.
The purpose of the Add-in is merely to gather certain attributes of the file in advance of processing through an automated tool which will convert the file to the newer format (.accdb).
It turns out there are some limitations and "challenges" with the automated tool from this thrid party vendor and we need to gather some information before processing.
If the file is password locked
If the file is encoded
If the VBA project is locked
The access version format of the file
The name of the startup form, if any.
All of these goals are achieved, albeit perhaps not in my preferred methods, but it does work.
The challenge is though, in order to detect if the file has a password I open the file programatically and the operator sees the password box.
Currently the user has to phycically click cancel whch fires an error my code detects and concludes yes in fact the file is password protected.
Naturally, none of the other attributes can be obtained in this scenario, which is ok.
The problem is that I'm trying to avoid the need for any operator interaction at all.
As it is now, it will sit and wait fo the user to click cancel.
However, if I can somehow detect the dialog is there and feed it a "{esc}" then it will be completely hand free.
I've tried progrmatically putting in an incorrect password and that actually worked better for password locked files (I could manage the error) but the problem is the code also tries to use the same bogus password to ALL files, even those that do not have passwords. Thusly we get the same erorr on all files instead of just the ones that actually do have a password.
I've tried some incarnation of SendKeys, but I must be doing something wrong becuase it never seems to hit the sendkeys code. it just sits tehre on the dialog box.
There are two ways.
First, I attempt to programmatically convert a file.
If the file is encoded this process will result in an error I process in the On Error scenario.
Technically, in order to convert the file, Access wants to open it if there is a password.
So, in this one process I can conclude both the encoding status as well as the password status for 95% of our scenarios.
So, here is the first process
This private sub is called from the main sub
The variables are all already declared and defined.
Code:
Private Sub CheckEncoding()
On Error GoTo CheckEncoding_Err:
Set AccessObj = CreateObject("Access.Application")
With AccessObj
.Visible = False
.Application.ConvertAccessProject AccessPathIn, AccessPathOut, 12
.Application.DoCmd.Quit
End With
Kill AccessPathOut
FileLocked = False
EncodingStatus = False
GoTo EndTheSub:
CheckEncoding_Err:
If Err.Number = 32544 Then ‘this is the encoding error
FileLocked = False
EncodingStatus = True
Else
If Err.Number = 3059 Then ‘this is the error AFTER cancel is clicked in the dialog box
AccessObj.Application.DoCmd.Quit
FileLocked = True
EncodingStatus = False
Else
MsgBox "undefined error encountered"
MsgBox Err.Number & " " & Err.Description
End If
End If
EndTheSub:
Set AccessObj = Nothing
End Sub
The second way is slightly different.
Turns out, in some scenarios, the Access Object created for the conversion doesn’t play nicely with the code for checking the other attributes.
So, as you can see above, I close that object so I can open it again below in order to check the other attributes.
Naturally, if I have already detected a password state I completely skip this next process.
HOWEVER, it also turns out we do get some submissions that are already in the ACCDB format.
Even though the format is current, they are submitting the file to have the code evaluated for deprecated constants etc.
Since these files are already in the ACCDB format, and cannot by definition be encoded, they skip the above process entirely.
So, the below code is hit by:
Earlier formatted file which we already know are not password locked, and,
Current format files whose password state is unknown.
This sub’s only purpose is to open the file and once open the flow returns to the original sub from whence other privates gather the attributes whilst the file is open.
Code:
Private Sub OpenAccessFile()
On Error GoTo OpenAccessFile_Err:
Set AccessObj = CreateObject("Access.Application")
With AccessObj
.Visible = False
.VBE.MainWindow.Visible = False
.OpenCurrentDatabase AccessPathIn, True
.Application.DoCmd.Minimize
End With
Exit Sub
OpenAccessFile_Err:
If Err.Number = 3059 Then ‘this is the error AFTER cancel is clicked in the dialog box
FileLocked = True
Else
MsgBox "undefined error encountered"
MsgBox Err.Number & " " & Err.Description
End If
End Sub
So, if I'm gathering correctly, the ghist here is to attempt a connection to the file.
If it has a password, the connection will fail with an error code instead of opening the password dialog box?
Thanks for the tip and the source.
I was indeed able to make something like the referenced code work like a charm.
Since some the DBs are ACCDB, I had to use ADO instead of DAO.
(I was getting unrecognized database format errors when checking ACCDB with DAO)
But, ADO seems to work a dream for all the tested formats and file versions (even '97)
Interestingly, the error code changed though (probably ADO vs. DAO).
Code:
Public adoConnection As ADODB.Connection
Private Sub PasswordCheck()
On Error GoTo PasswordCheck_Err:
Set adoConnection = New ADODB.Connection
adoConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source= " & AccessPathIn & ";"
adoConnection.Open
FileLocked = "False"
adoConnection.Close
Exit Sub
PasswordCheck_Err:
'If Err.Number = 3031 Then 'DAO Err.Number
If Err.Number = "-2147217843" Then 'ADO Err.Number
FileLocked = True
Else
MsgBox "Undefined error encountered in 'PasswordCheck'" & vbCrLf & _
Err.Number & " " & Err.Description
End If
End Sub
Re: the Salutation.
Perhaps I date myself, but every single time I say "Hi Bob", I get a pavlov response harkening back to the very old drinking game connected to the original Bob Newhart Show in which everytime anybody on screen said "Hi Bob" you had to chug a beer.
For anybody familiar with the show, that could happen so frequently, one or more of the contestants might well be passed out before the end of the show.
