Setting .adp properties outside access (AllowBypassKey)

[Nando]

Hi everyone! I need to open an ADP project through automation (I know how to
do that). However, when I do it all the scripting stuff starts executing
(startup forms, autoexec macro..). I would like to bypass these methods and
open the adp file to do my stuff. What I would like is a way I can set the
"AllowBypassKey" property, so I later can quietly open the file through
automation without all that scripting executing . I was successful doing it
for a standard MDB file. I used the code below to set the property to False,
and then use the automation to open the file again and do what I actually
want to do. However, when I run this code with an ADP project it comes up
with a runtime error 3343 "Unrecognized database format." The ADP format
seems to be incompatible with DAO. So how do I set the "AllowBypassKey"
property of an ADP file without having to open the database directly or
automated? I need to avoid the auto-scripting when opening the file through
automation.

Call SetDAOProperty("C:\mydatabase.adp", "AllowByPassKey", dbBoolean, False)

Public Function SetDAOProperty(strDBFilename As String, strPropertyName As
String, _
PropType As DAO.DataTypeEnum, vPropVal As
Variant )
Dim db As DAO.Database
Dim prop As DAO.Property

Set db = DAO.OpenDatabase(strDBFilename)

On Error Resume Next
db.Properties.Delete strPropertyName
Set prop = db.CreateProperty(strPropertyName, PropType, vPropVal, True)
db.Properties.Append prop

Set prop = Nothing
db.Close
Set db = Nothing
End Function

 

[Sylvain Lafontaine]

The control of properties are different for ADP and some properties - like
the Custom properties that we can set from the menu File | Database
Properties - cannot be accessed from VBA. For AllowBypassKey, I don't know
but here the code that I'm using for setting some user's properties with
ADP:


Private Const cerrPropertyNotFound As Integer = 2455 ' For ADP, not MDB.

Public Sub Debug_DisplayProperties()

Dim db As CurrentProject
Set db = Application.CurrentProject

Dim i
For i = 0 To db.Properties.Count - 1
Debug.Print db.Properties(i).name & ": " & db.Properties(i)
Next

End Sub


Public Sub SetProperty(ByVal strPropName As String, _
ByVal varPropType_Bidon As Integer, _
ByVal varPropValue As Variant)

Const cProcedureName As String = "SetProperty"
On Error GoTo Err_Handler

Dim db As CurrentProject
Set db = Application.CurrentProject

If (IsNull(varPropValue)) Then varPropValue = ""

Dim i
For i = 0 To db.Properties.Count - 1
If (db.Properties(i).name = strPropName) Then
db.Properties(strPropName).Value = varPropValue
GoTo Exit_Sub
End If
Next

db.Properties.Add strPropName, varPropValue

Exit_Sub:
On Error GoTo 0
Set db = Nothing
Exit Sub

Err_Handler:
' Err_Handler: utilisée dans l'ancienne version.

Select Case err
Case cerrPropertyNotFound
db.Properties.Add strPropName, varPropValue

Case Else
' Call LogError(Err.Number, Err.Description, cModuleName &
cProcedureName)
End Select

Resume Exit_Sub

End Sub


' GetProperty() : return True if the property exists in the collection.

Public Function GetProperty(ByVal strPropName As String, _
ByRef strPropValue As Variant) As Boolean

Const cProcedureName As String = "GetProperty"
On Error GoTo Err_Handler

Dim db As CurrentProject
Set db = Application.CurrentProject

Dim i
For i = 0 To db.Properties.Count - 1
If (db.Properties(i).name = strPropName) Then
strPropValue = db.Properties(strPropName)
GetProperty = True
GoTo Exit_Function
End If
Next

GetProperty = False

Exit_Function:
On Error GoTo 0
Set db = Nothing
Exit Function

Err_Handler:
GetProperty = False

Select Case err
Case cerrPropertyNotFound
Case Else
' Call LogError(Err.Number, Err.Description, cModuleName &
cProcedureName)
End Select

Resume Exit_Function

End Function

 

[Nando]

Hi Sylvain. The problem is that the code relies on a current session of
Access that has been called from automation. No problem with that, however
at that point the project has been already opened and the scripts (startup
form and/or autoxec macros) have already ran. I need to avoid this
situation. That's why I wanted to edit the "AllowBypassKey" property. I only
need to open the adp project without triggering all the self executing
scripts. The original code I submitted works perfectly because it does not
rely on an Access session (does not run the autoscripts). However it does
not work on adp files

 

[Robert Morley]

What about simply setting something that your code can recognize to skip the
startup sequence? If you're using straight automation (i.e., Set myApp =
New Access.Application), this would have to be something like an entry
within the database (if that's feasible in your scenario), a file on disk, a
registry key, an environment variable, or something else of that nature; if
you're launching it via a Shell command and then later attaching to the
process, you can also use the /cmd startup parameter (which can later be
examined from code using the Command() function).

It's not quite the same as the AllowBypassKey property, but it should get
you where you want to go, at least.



Rob

 

[Nando]

Thanks Robert, but using "Set myApp = New Access.Application" and then
OpenCurrentProject will cause to open the project running all the scripting
(autoexec and/or startup form). I do not want that (in fact, I beleive that
everybody opening an Access file through automation wouldn't want to trigger
those scripts either). I just want to open the project bypasing all its
autoexecute scripts.

I would say everything goes down to: "Opening an ADP file without running
the AutoExec macro or the Startup form"

I do not see any methods or properties to instruct the Access.Application
object to bypass the autorun macros.

However the code at http://www.mvps.org/access/api/api0068.htm emulates the
Shift key while calling the Access.Application/OpenCurrentProject. Great!
But first the project would have to be set with its "AllowBypassKey" set to
True. As it seems I cannot set that property without calling automation.
However, the code I submitted in my first post works great, without
automation, opening the file quietly using DAO, avoiding the scripts, and
setting the "AllowBypassKey" property to True. Then I can use the MVPS
function above to bypass the autoexecuting scripts and use its extended
object architecture to do everything else. I cannot do the same for adp
files as my function only works on MDB files, and not ADPs.

 

[Robert Morley]

Okay, so add code to bypass each of the options under whatever conditions
you specify. For example, if you want to always bypass them when started by
automation, add the following function to a standard module:

Public Function MyUserControl() As Boolean
MyUserControl = Application.UserControl 'This could, of course, be any
other condition you want to use, but UserControl will probably do what you
want.
End Function

Then in your AutoExec macro (and I believe this would work in an AutoKeys
macro, but not 100% sure), in the Conditions column (enable under Tools,
Options, View), you'd have "=MyUserControl()" as the condition for anything
you wanted to run. In other words, the various instructions would run only
if the app was under user control. Or with slight changes to the code, you
could abort the macro if it wasn't under user control.

Similarly, in your startup form, you would put...

Private Sub Form_Open(Cancel As Integer)
If MyUserControl() Then
DoMyStartupStuff
Else
Cancel = True
End If
End Sub

That ought to take care of just about everything.

I'll grant it's not quite the same as AllowBypassKey, but it should get you
where you want to go.



Rob

 

[Nando]

Thanks Robert, Unfortunately that would not be realistic for me. At work
I'll be inventory many adp files for my department. What I need to do is
just opening these files without executing all these autoscripts.

Microsoft Access (AFAIK) does not provide any built-in way to conditionally
bypass the macro, except for the Shift key. I am able to set focus and send
a fictitious Shift key to the Access instance by using some API functions.
However, I will need to first set the property "AllowBypassKey" to True on
the adp file, so Access complies with bypassing the autoexec macros. For MDB
files, the DAO code (first post) does the job by setting the property to
True (without executing scripts), but this code does not work for ADP files.

I believe that everybody opening an Access file through automation wouldn't
want to trigger those scripts either. Because if a user just wanted to open
the file (and its scripts) he would just do it through the
command-line/shell. Even there Microsoft provides a way with a /X switch to
specify which macro is to run. My point (on this last paragraph) is that I
cannot believe the Microsoft Access development team left something like
this hanging this way, unless I am wrong

 

[Sylvain Lafontaine]

MDB files are standard database files and can be easily accessed/manipulated
from the standard DAO database object. However, this is not the same thing
with ADP because ADP files are not databases and have no local tables.
Because these files are not databases and don't store anything, it's easy to
see why they have not been made to be manipulated easily with Automation.
(Excerpt maybe for manipulating the file's properties, I don't see any
reason why I would want to manipulate an ADP file via Automation.) I don't
know their structure (maybe they are modified mdb file) but it seems obvious
that these properties are stored using a proprietary format and that MS
doesn't seem to have bothered itself to much about giving us a programmatic
access to these properties.

Here a suggestion: try setting the property AllowByPassKey (to either true
or false) for all ADP files and then use an hexadecimal editor to directly
manipulate the stored value (search for the string AllowByPassKey in the
file and look at what's after). However, I don't know if this will work.

BTW, if this was the only thing that the MS team has left hanging for ADP, I
would be pretty happy.

 

[Robert Morley]

Ah, okay, I get it now! Unfortunately, AFAIK, you're right...there's no
way to bypass the startup in the manner you need to. I was thinking of
something along the same lines as Sylvain was, but that was the only thing I
could come up with.



Rob

 

[Nando]

Thank you Sylvain, and Robert!

So...Microsoft did leave it hanging :-(

Hmmm...I would say they must have something undocumented of hidden somewhere
(as usual) ;-)

I appreciate all your assistance in my case. I will research into the file
format option. If I'm successful (and the tests are reliable) I will come
back here with the results. Thank you guys!