First Published 1 Nov 2023 Last Updated 3 Nov 2023
An important feature of all VBA enabled Office applications is that they can be controlled externally using automation.
For example, an Access database can be opened, used in the normal way then closed from another Access database or Excel spreadsheet.
This is a fundamental part of many apps including my Database Analyzer Pro application where selected databases are opened, hidden, in the background so that the analysis can be completed.
However, if the external database has a startup form or code requiring interaction, the process would 'hang' without further intervention.
For example, the new Northwind template databases have a startup Welcome form where the user must click Continue to proceed
This opens a second Login form where the user must select a user name from a combo then click Login
As the external database is being opened hidden in the background, that user intervention cannot occur.
To prevent that being an issue, the analyzer always uses the shift bypass to open the external app so the analysis runs smoothly.
The code I use to do this is very simple. It should be placed in a standard module
CODE:
Option Compare Database
Option Explicit
'====== API DECLARATIONS ========
'Pause code using the Sleep API
#If VBA7 Then 'A2010 or later (32/64-bit)
Declare PtrSafe Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
#Else 'A2007 or earlier
Declare Sub Sleep Lib "kernel32.dll" (ByVal dwMilliseconds As Long)
#End If
'Simulate a keystroke
#If VBA7 Then
Declare PtrSafe Sub keybd_event Lib "user32" (ByVal bVk As Byte, ByVal bScan As Byte, _
ByVal dwFlags As Long, ByVal dwExtraInfo As LongPtr)
#Else
Declare Sub keybd_event Lib "user32.dll" (ByVal bVk As Byte, ByVal bScan As Byte, _
ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
#End If
'====== END OF API DECLARATIONS ========
Sub OpenWithShiftBypass(strPath As String)
Dim appAccess As Access.Application
' Create a new instance of Access
Set appAccess = New Access.Application
' Simulate pressing the Shift key to bypass startup code
keybd_event vbKeyShift, 0, 0, 0
'Open the target database
appAccess.OpenCurrentDatabase strPath
' OPTIONAL - Make the target database visible
appAccess.Visible = True
' Simulate releasing the Shift key
keybd_event vbKeyShift, 0, 2, 0
' OPTIONAL - Wait 5 seconds (5000 milliseconds) so external app can be viewed before closing it
' NOT NEEDED if app is hidden
Sleep 5000
' OPTIONAL - Close the target database
appAccess.CloseCurrentDatabase
' Quit the new instance of Access
appAccess.Quit
' Release the object
Set appAccess = Nothing
End Sub
Example usage:
OpenWithShiftBypass "G:\MyFiles\ExampleDatabases\Northwind\2023\Northwind22Dev.accdb"
NOTE:
For a completely different approach, see the OpenBypass code by fellow Access MVP, Adrian Bell AKA @NeoPa at Bytes.com
Further Information
1. The above code won't correctly handle opening external databases that are encrypted with a password.
The next article in this series will provided modified code to handle password protected databases and (optionally) open the database exclusively
2. The above code will also not work where the shift bypass property has been disabled
This is often done to protect databases and ensure required startup code is run.
However, it is also possible to re-enable the shift bypass from another Office application.
The third article in this series will provide code to manage the shift bypass and help protect your databases.
Feedback
Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.
Please also consider making a donation towards the costs of maintaining this website. Thank you
Colin Riddington Mendip Data Systems Last Updated 3 Nov 2023
Return to Code Samples Page
Page 1 of 3
1
2
3
Return To Top
|
|
|