How to start Access from VB when .mdb file password protected???

[Henry Norman]

If an Access 2002 .mdb file is password protected, how can I start an
instance of Access 2002 from VB6, passing the password from within the
program (prompting for .mdb password does not work, as the app user does not
know the password)?

Four years ago, I developed a database application based on VB6 and Access
97. The Access .mdb file is password protected (court case data, very
sensitive), with the Access password stored in scrambled format in the
registry. From the VB6 application, users can select various reports stored
in the Access .mdb file for preview/print, with no need to know the Access
password.

The system worked great for four years. Then we "upgraded" to Office XP
(Access 2002), and with this move the application can no longer access the
Access .mdb file using the same methods. I'm hoping that someone can guide
me in the right direction!

For development, I use Visual Studio 6 with SP4.

The VB6 application opens the Access .mdb database using the following code:

' Get current Access password (scrambled):
gCurrAccessPW = GetSetting(App.Title, "Settings", "KeyValue", "y")
unscramble ' Make password useable
Dim pw$
pw = ";PWD=" & gCurrAccessPW
' Create Microsoft Jet Workspace object.
Set gJet = CreateWorkspace("", "admin", "", dbUseJet)
' Open Database object
Set dbsCRS = gJet.OpenDatabase(gDataBaseName, False, False, pw)
gbAccessOpen = True

After this, numerous data controls reference dbsCRS and access the Access
tables.

With Access 97, the following VB code started Access 97 with the selected
report in preview:

Private Sub menuFilePrintCase_Click()
' Set up error handler:
On Error GoTo errorHandler
Dim appAcc As New Access.Application
' Open database (use SendKeys() to pass the Access97 password):
SendKeys gCurrAccessPW & "~"
appAcc.OpenCurrentDatabase gDataBaseName
' Open case report (print preview):
appAcc.DoCmd.OpenReport _
"Case Report (From CRS)", _
acViewPreview, _
"Case Query (From CRS)", _
"tCases.caseId = '" & gCurrCaseid & "'"
' Display "report formatted" message:
sb.Panels(1) = "Switch to Access window to review/print case report"
' We're done here...
Exit Sub
errorHandler:
MsgBox "Report error " & Err.Number & ": " & Err.Description
End Sub

After "upgrading" to Office XP (Access 2002), the application no longer
works. The above breaks on bogus errors (unrecognizable file format), even
though there is nothing wrong with the Access 2002 .mdb file.

After some gyrations, I have managed to get this code to work:

Private Sub menuFilePrintCase_Click()
' Set up error handler:
On Error GoTo errorHandler
' If no suitable Access instance runs, create new:
Dim appObj As Object
Set appObj = GetObject(gDataBaseName) ' <= How do I pass the password???
With appObj
' Open the case report (print preview):
.DoCmd.OpenReport _
"Case Report (From CRS)", _
acViewPreview, _
"Case Query (From CRS)", _
"tCases.caseId = '" & gCurrCaseid & "'"
' Maximize the report preview window:
.DoCmd.Maximize
' Make report preview visible:
.Visible = True
End With
' Display "report formatted" message:
sb.Panels(1) = "Switch to Access window to review/print case report"
' We're done here...
Exit Sub
errorHandler:
MsgBox "Report error " & Err.Number & ": " & Err.Description
End Sub

However, this introduces a problem: because it contains very sensitive data,
the Access .mdb file must be password protected. This password is only known
to admins, the regular user does not know what it is. As a result, regular
users can no longer use the "print case data" function. This sux.

As it appears to be a perfectly valid thing to do (starting an instance of
Access and passing it the proper password, at run time), I suspect that it
is only my lack of MS experience that prevents me from finding the
solution...

Can anybody out there guide me in the right direction? Please? Rephrased,
the question is:

If an Access 2002 .mdb file is password protected, how doI start an instance
of Access 2002 from VB6, passing the password from within the program
(prompting for .mdb password does not work, as the app user does not know
the password)?

Many thanks in advance for any guidance,

Henry Norman
(e-mail address removed)

 

[Scott McDaniel]

Did you convert the database to Access 2002? It sounds as if you did, but
it's not clear. If so, can you open it directly in Access 2002? Any
problems/errors/etc?

Check Dev's website for info on this:
http://www.mvps.org/access/modules/mdl0036.htm

 

[Jack MacDonald]

This *may* be related -- had this experience recently.

An existing Access 97 database created and owned by the Admin user was
modified by a user who existed only in a secured workgroup. New tables
were created (owned by the "secure user"). When the table was opened
in a non-secure environment, the table owners appeared as "<unknown
user>". So far, so good... that's the expected behaviour.

When the table was attempted to be opened in Access 2002 (non-secure
workgroup), it was unable to do so. Access reported an error -- no
permissions on table "MSys????" (Can't remember the table name, but it
was one of the system tables). Access could NOT display the "unknown
owner" message.

Solution was to change the ownership of the objects to Admin. I notice
that your code does not use a named workgroup file, so I thought there
may be a connection. Hope it's not a red herring...

BTW - database passwords are generally regarded as quite flawed. You
are better off to use User Level Security.