Open a pasworded runtime database

[Peter101793]

[This question has been posted on Utter Access https://www.utteraccess.com/topics/2.../posts/2831149



I am trying to replicate an application that worked with Access 2010 for use if Office 365 Access. This involves an encrypted passworded .accdr file where the password is hidden in the code and only known by the developer. When the .accdr opens it presents the user with a login screen where credentials can be maintained by a user administrator. As an aside, the application is split front-end and back-end and most of the tables are held in MySQL. I think this technique is important because the end user experience is like a bespoke c# application and provides a robust level of security preventing access to database passwords, tables and code.

The Access 2010 application code came from an MSDN Developer Article, no longer web viewable which acknowledge that, unlike passworded .accdb and .accde database, opening a runtime database with a password is not straightforward. The technique this article suggested was to open a non-passworded .accdb which opens, via Shell, a dummy .accdb database in runtime mode. The dummy database then uses GetObject to connect to the dummy database. The dummy database is closed and the 'real database' (the passworded runtime db ) is opened automatically with the password.

Here is the code, which fails on the line appRT.OpenCurrentDatabase strPathToDatabase, False, strPasswordx with the error message that the file does not exist, is opened by another user or is not an .adp file.

Function Hide1()
' removes toolbar
DoCmd.ShowToolbar "Ribbon", acToolbarNo  
' positions database window on RHS
Call ScreenRL(True)
'---Placeholder pause for code to backup and compact the main database
Call sapiSleep(3000)
'  Opens the "real database"
Call StartPasswordedDatabaseRuntime("C:\MouSe\NFM140.accdb", "xxxxxx", "C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE", True)
End Function



Function StartPasswordedDatabaseRuntime( _
        strPathToDatabase As String, _
        Optional strPasswordx As String, _
        Optional strPathToRuntime As String, _
        Optional blnQuit As Boolean)
Dim appRT As Access.Application
Dim strPathToDummy As String
Dim i As Long
    
Const Q As String = """"
        
'On Error GoTo Error1
Call ScreenRL(True)
   
If Len(strPathToRuntime) = 0 Then
   strPathToRuntime = SysCmd(acSysCmdAccessDir) & "msaccess.exe"
End If
   
strPathToDummy = "C:\scratchdir\dglink.accdb"
   
    
    
If Len(Dir(strPathToDummy)) = 0 Then
   Application.DBEngine.CreateDatabase strPathToDummy, dbLangGeneral, dbVersion40
End If
   
        
     
    
Shell _
   Q & strPathToRuntime & Q & " " & Q & strPathToDummy & Q & " /runtime", _
   windowstyle:=6
i = 1
    
    
Do While i > 300000
    i = i + 1
    
Loop
    
Set appRT = GetObject(strPathToDummy)

MsgBox strPathToDatabase & vbCrLf & strPasswordx  'This correctly displays db credentials which work when the .accdr is opened directly.   
With appRT
   .CloseCurrentDatabase
   

        
End With

DoEvents
DoEvents

appRT.OpenCurrentDatabase strPathToDatabase, False, strPasswordx  'This line fails with the message Microsoft Access cannot open the database because it is missing, opened exclusively by another user or is not an adp file.   
Call ScreenRL(True)
DoCmd.Maximize
    
   
If blnQuit Then
   Application.Quit
        
End If
    
    
Exit Function
Error1:
MsgBox "Sorry we have problems.. " & vbCrLf & Err.Number & vbCrLf & Err.Description
Application.Quit
    
End Function

Is there another way to open a passworded, encrypted runtime database in the current version of Access? Alternatively, can anyone help by pointing out how to correct the run-time error? Thanks.

[isladogs]

As I stated in the original thread at Utter Access, I believe the simplest solution to achieve the result you want using simulated runtime .accdr files is as follows:
1. Rename the target file as .accdb or .accde (as appropriate)
2. Lockdown the file to replicate the runtime functionality e.g. remove nav pane and status bar. Remove or significantly restrict the ribbon. Also disable right click shortcut menus, use of special keys etc


Then add this code to a standard module in the source database.

Option Compare Database
Option Explicit

Declare PtrSafe Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)


Sub OpenExternalApp(strPath As String, Optional blnExcl As Boolean = False, Optional strPwd As String)  
   
Dim appAccess As Access.Application

'code works for .accdb / .accde files but fails for .accdr files with err 7866 on line 40/60   
10    On Error GoTo Err_Handler                           

'Create a new instance of Access
20        Set appAccess = New Access.Application                           

'Open the target database
30        If strPwd <> "" Then
40            appAccess.OpenCurrentDatabase strPath, blnExcl, strPwd
50        Else
60            appAccess.OpenCurrentDatabase strPath, blnExcl
70        End If                  

'make external app visible
80       appAccess.Visible = True                       

'Wait 0.1 seconds so external app has fully loaded before closing the source database
90       Sleep 100 'adjust as necessary

'Release the object
100       Set appAccess = Nothing

'close this db
110        Application.Quit 
        
Exit_Handler:
120      Exit Sub  
             
Err_Handler:           
'err 2467 = The expression you entered refers to an object that is closed or doesn't exist.           
'occurs if user closed external database before line 130 runs
130        If Err = 2467 Then Resume Next            

140       MsgBox "Error " & Err.Number & " in line " & Erl & " of OpenExternalApp procedure : " & _
                         Err.Description, vbOKOnly + vbCritical, "Critical Error"

150        Resume Exit_Handler          

End Sub

The target password protected database will open and remain open after the source database closes.
It will work correctly without error 7866: can't open the database because it is missing, or opened exclusively by another user, or it is not an ADP file

[Peter101793]

As I stated in the original thread at Utter Access, I believe the simplest solution to achieve the result you want using simulated runtime .accdr files is as follows:
1. Rename the target file as .accdb or .accde (as appropriate)
2. Lockdown the file to replicate the runtime functionality e.g. remove nav pane and status bar. Remove or significantly restrict the ribbon. Also disable right click shortcut menus, use of special keys etc


Then add this code to a standard module in the source database.

Option Compare Database
Option Explicit

Declare PtrSafe Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)


Sub OpenExternalApp(strPath As String, Optional blnExcl As Boolean = False, Optional strPwd As String)  
   
Dim appAccess As Access.Application

'code works for .accdb / .accde files but fails for .accdr files with err 7866 on line 40/60   
10    On Error GoTo Err_Handler                           

'Create a new instance of Access
20        Set appAccess = New Access.Application                           

'Open the target database
30        If strPwd <> "" Then
40            appAccess.OpenCurrentDatabase strPath, blnExcl, strPwd
50        Else
60            appAccess.OpenCurrentDatabase strPath, blnExcl
70        End If                  

'make external app visible
80       appAccess.Visible = True                       

'Wait 0.1 seconds so external app has fully loaded before closing the source database
90       Sleep 100 'adjust as necessary

'Release the object
100       Set appAccess = Nothing

'close this db
110        Application.Quit 
        
Exit_Handler:
120      Exit Sub  
             
Err_Handler:           
'err 2467 = The expression you entered refers to an object that is closed or doesn't exist.           
'occurs if user closed external database before line 130 runs
130        If Err = 2467 Then Resume Next            

140       MsgBox "Error " & Err.Number & " in line " & Erl & " of OpenExternalApp procedure : " & _
                         Err.Description, vbOKOnly + vbCritical, "Critical Error"

150        Resume Exit_Handler          

End Sub

The target password protected database will open and remain open after the source database closes.
It will work correctly without error 7866: can't open the database because it is missing, or opened exclusively by another user, or it is not an ADP file

Your code as posted to this forum suffers from the same problem that I raised in the UA forum. The database being opened (the DB) closes at the same time of the starter database. Usually both databases remain minimised. If you increase the sleep time to say 4000, you can hover over the minimised icons and maximise the DB being opened before it closes. But both close together unless there is some user action, like clicking on a field. Then all works as you described, the DB remains open while the starter database closes. But this is useless for my purposes of displaying a user login form pop-up when the DB opens which must remain on screen until credentials are supplied or the the user exits the application.

I tried adding code appAccess.UserControl = True but this causes a runtime error. However when I insert a hidden field on the form which is displayed when the DB opens and set up code in the Form_Open event to insert a few characters of text, this solves the problem. The DB remains displayed while the launching program closes (sleep value 500). Progress!

As the starter database contains a password, this will need to be an .accde. And the DB must have code stripped (.accde) and be encrypted which should prevent users viewing forms and tables because they would need the password to decrypt the database. I need to do some more testing but I now have cautious optimism that this could end up as a workable solution.

I hope you now understand my frustration in the UA forum when you wrote "The target file opens and stays open after the source file is closed." but my testing provided a quite different experience!

I'll post again when I have done some further work.

[Peter101793]

Thank you for your continued interest in my problem.

The code you post above suffers from the same problem as I raised in the UA forum. Both DB's close at the same time, regardless of the Sleep value. That is, unless there is some user interaction, like clicking a field. Useless! However if you add a hidden field on the passworded DB and then code on the Form_Open event to add a few characters, things work as you described.

I need to do some more work here, but I am cautiously optimistic this may lead to a workable solution.

I've just realised that this solution requires a full version of Access whereas my earlier application worked with Access Runtime.

[pbaldy]

Post 3 was moderated, I'm posting to trigger email notifications.

[Welshgasman]

Why don't you post what ìs required and Colin, amend for you (if he is willing) instead of just crossposting in other forums WITHOUT ANY NOTIFICATION OF PREVIOUS CROSSPOSTS?

[isladogs]

I hope you now understand my frustration in the UA forum when you wrote "The target file opens and stays open after the source file is closed." but my testing provided a quite different experience!

Not really.
I made it clear that I had tested the code multiple times and that it worked for me exactly as described using A365 version 2507 Beta Channel.
OK so it clearly didn't work for you but that didn't justify your response at UA

Multiple postings are also generally not helpful

I have tested yet again and confirmed (again) that the target file remains open without any user intervention on my part.
In my case the target file opens to a startup form.

I repeated the test without a startup form in the target db - that did close along with the source app being closed.
Perhaps all that's needed is to have a startup form? e.g. your user login form?

Alternatively, adding the line appAccess.UserControl = True as line 85 also keeps the target app open for me with no startup form. No runtime error in my test

[jojowhite]

if you can test this db.
there is a sub (Test) on Module1.
on this sub, you define the path and name of the accdr and the password to it.
run the sub (F5).

[Peter101793]

Thanks. I'm looking at your code. Initial reaction is that it involves "SendKeys" which in the past I have found not always reliable. But thanks for doing work on this.

[Peter101793]

My last UA response to you wasn't prompted by the fact that your code didn't work for me. I wrote because I didn't believe comments like "Why waste everyone's time unnecessarily? " and "Help us to help you!" were in any way appropriate. In almost 1000 UA posts, which included a number of silly mistakes on my part, I have never been on the end of words like you used. In some forums a moderator would censure what you wrote.

I have carried out further tests on your code, modified by my hidden field. It works robustly. However there are two problems with your attempts to meet my requirement to open a passworded .accdr. The first is it will not work without full Access. The second, which I believe rules out your solution completely, is that the starter DB requires the password of the .accde to be included in viewable code because it is included in a non-encrypted database. The elegance of the original solution gets round this by including all passwords in the passworded .accdr from which all code is stripped. So I'm afraid it seems to me we're back to square one on this.

Correction made 22/06 14:59 BST

My last point is wrong. An .accde can have code stripped without a password. So Isladogs code, modified with a hidden field (or ppAccess.UserControl = True at line 85) may still provide a solution to this problem for an install on a PC with a full version of Access. Apologies for my mistake.

[jojowhite]

you can create your own Sendkeys:

Sub SendKeysWithWSH(ByVal strText As String, Optional ByVal Wait As Boolean = True)
    Dim wsh As Object
    Set wsh = CreateObject("WScript.Shell")
    ' Send keystrokes
    wsh.SendKeys strText, Wait
    Set wsh = Nothing
End Sub

[jojowhite]

My last UA response to you wasn't prompted by the fact that your code didn't work for me. I wrote because I didn't believe comments like "Why waste everyone's time unnecessarily? " and "Help us to help you!" were in any way appropriate. In almost 1000 UA posts, which included a number of silly mistakes on my part, I have never been on the end of words like you used. In some forums a moderator would censure what you wrote.

I have carried out further tests on your code, modified by my hidden field. It works robustly. However there are two problems with your attempts to meet my requirement to open a passworded .accdr. The first is it will not work without full Access. The second, which I believe rules out your solution completely, is that the starter DB requires the password of the .accde to be included in viewable code because it is included in a non-encrypted database. The elegance of the original solution gets round this by including all passwords in the passworded .accdr from which all code is stripped. So I'm afraid it seems to me we're back to square one on this.

that wasn't me.

[Peter101793]

that wasn't me.

I'm sorry - still not used to this forum!

[Peter101793]

This reply was intended for Isladogs. Sorry that my inexperience in using this forum meant that my reply was mis-posted.

My last UA response to you wasn't prompted by the fact that your code didn't work for me. I wrote because I didn't believe comments like "Why waste everyone's time unnecessarily? " and "Help us to help you!" were in any way appropriate. In almost 1000 UA posts, which included a number of silly mistakes on my part, I have never been on the end of words like you used. In some forums a moderator would censure what you wrote.

I have carried out further tests on your code, modified by my hidden field. It works robustly. However there are two problems with your attempts to meet my requirement to open a passworded .accdr. The first is it will not work without full Access. The second, which I believe rules out your solution completely, is that the starter DB requires the password of the .accde to be included in viewable code because it is included in a non-encrypted database. The elegance of the original solution gets round this by including all passwords in the passworded .accdr from which all code is stripped. So I'm afraid it seems to me we're back to square one on this.

[Peter101793]

I've tested your code with the db attached (including sample .accdr located in c:\scratchdir. Password 03LeIl1). Unfortunately my tests pop-up the password box when the .accdr opens. Am I doing something wrong?