By pass startup form / macros 2

[MajP]

I have checked past threads on this, and googled it but could not find an answer. I thought I remember seeing a post with a solution. I am Looking for a way to open some external databases and bypass the startup form or autoexec. Most of my dbs open with a dialog startup form.
I found this older code

http://access.mvps.org/access/api/api0068.htm

Uses the api to simulate the shift key, but I could not get it to work and not very knowledgeable on API. The information I need requires the database to load and can not be retrieved by ADO/DAO or the application object. These dbs have no security or shift bypass disabled set. Any help is appreciated.

 

[dhookom]

MajP,
Are you suggesting holding down the shift key when opening doesn't work and the Allow Bypass Key is set to True?

Is the database being opened as run-time?

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

No. Manually opening works fine. I am trying to automate from an external db. I want to open an external db, get some information, and then close it. However, the external dbs have dialog startup forms and possibly code. So they either they flicker or stay open or run code.

 

[dhookom]

Not sure if this will help or not but Bill Mosca had a property setter utility that might help.

Duane
Hook'D on Access
MS Access MVP

 

[randy700]

If you only want to extract some data, can't you simply
link to the tables in the other db?


Randy

 

[Gammachaser]

If it is a startup form try this:

Dim strOriginalForm as String
Dim db as Database

Sub RemoveStartup()
Set db = OpenDatabase([i]yourdatabase[/i])
strOriginalForm = db.Properties("StartUpForm") 
db.Properties("StartUpForm") = "(none)"
db.Close
set db = Nothing
End Sub

Sub ResetStartup()
Set db = OpenDatabase([i]yourdatabase[/i])
db.Properties("StartUpForm") = strOriginalForm
db.Close
Set db = Nothing
End Sub

Remove the startup form, do your thing with the database and then reset it.

You might be able to do the same for an autoexec by renaming it (I have not used autoexecs but there should be an easy way to do it using the same logic.)

 

[MazeWorX]

MajP i have a properties setting utility "work in progress" but it can set these properties remotely so you can do what you want so far.

M

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[MajP]

Thanks everyone.
I was actually trying to help on another thread, but could not figure a solution. The OP is trying to get the custom command bars / shortcut bars from external databases. They do not want the external database show or forms/macros execute. Here is my understanding, and this is why I was stumped. I may be wrong on this, so correct me if my understanding is incorrect.
Information on Custom command bars is maintained in the database, but I think the only way you can reference them are through the application object. If you create a new Access.application you can reference the default command bars collection, but custom bars are only added to the collection once opening a database in the application. If you use application.opencurrentdatabase then the database opens and startup options are triggered. If you use dao and run opendatabase you can get a reference to the database, but you can not get a reference to the command bars.
So my guess to do this would be to first do what GammaChaser suggests and use the dao.opendatabase to get to the database properties. Remove the startup form value from the startupform property, open the datbase using application.opencurrentdatabase (thus loading the custom commandbars), read the commandbars, replace the startup form value, close the database.
Duane and Mazeworx that application that Duane refereces works well. I gets the startup properties which is really is not much of a challenge because you can get and set that through DAO without opening the application. However it also allows you to select a startup form. AFAIK the forms can only be referenced through the application object through the currentdb or currentproject.

 

[MazeWorX]

and for those who are curious about it
To get this property

Set db = DBEngine.OpenDatabase(Me.txtConnect)

Me.txtAppTitle.Value = db.Properties("AppTitle")

and to set it

db.Properties("AppTitle") = Me.txtAppTitle.Value

also if the property doesnt exist getting it will cause an error so we create it usually by trapping the error. Note spelling does count when it comes to the db properties

   Const ConPropNotFound = 3270 
If Err.Number = ConPropNotFound Then
        Set prp = db.CreateProperty("AllowByPassKey", dbBoolean, True)
        Set prp = db.CreateProperty("Track Name AutoCorrect Info", dbBoolean, False)
        Set prp = db.CreateProperty("Perform Name AutoCorrect", dbBoolean, False)
        Set prp = db.CreateProperty("Auto Compact", dbBoolean, False)
        db.Properties.Append prp
    End If

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[MajP]

MazeWorx,
I misspoke and also I have a question. For most properties I notice that the process is not to remove the value, but remove the property. When I add a startup form it actually adds the property with the value of the start up form's name. I said in my post to remove the value, which I think is incorrect. If I want to remove the start up form, I need to remove the startupform property.

Here are my questions, I have not tested yet. Maybe you know
For something like you show
Set prp = db.CreateProperty("Auto Compact", dbBoolean, False)
Since this value is false, can you remove the property and get the same results or do you get an error? I noticed most properties have positive results and are removed if negative or 0. (But not all)
Does setting the startupform to "" work the same as removing the property.

 

[MazeWorX]

sort of as far as i know the DBEngine method cannot delete properties once they have been created. You can change them. With the app title to remove it you simple change it to "" however the startup form is not quite like that. You change it to nothing like this

db.Properties("StartUpForm") = "(none)"

which makes sense if when selecting the property you are selecting a database object then use this method otherwise an empty string or the appropriate numeric value for the property will do it. When resetting it to your form just pass the form name as a string minus the ()





HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[MajP]

Thanks. I think I got it. A built in property may not exist in the collection, but once it is added you cannot delete it or atleast not from DAO.
So now I can open a datbase without the startup form firing, by first checking if that property exists and if so set it to "(none)". Then resetting the value when done.


Any idea on the autoexec?
According to the link there is no way to do that except as shown

However, Access does not provide any built-in way to conditionally bypass the macro. If a database contains the macro, it will run! It can be only be bypassed at startup time (from the User Interface) with the shift key, provided the AllowBypassKey property has not been set.
If the AllowBypassKey has not been set, then the 'Shift' key can be programmtically pressed when the database is opened programmatically through Automation code. To get a reference to a hidden instance of Access with the database in which you wanted to bypass the Autoexec macro, you can use this function.

Unfortunately this code does not work for me. It is probably somewhat dated, and I have no API knowledge.

 

[MazeWorX]

I'm thinking you may want to consider copying the macro to your connecting db then export a macro that does nothing when your done replace it. The macro will run if it exists and i don't believe there is anything to suppress this action

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[MazeWorX]

so did a little testing you can Import the macro from the db saving a copy to replace later then export your own AutoExec macro that does nothing to work on the db. just need to code the process

M

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[Gammachaser]

A few years ago I developed a tool to create, set, and change the values in all of the 'Startup' Properties (I call it MasterKey because it orginally was designed to set/unset the 'AllowBypassKey' property.) When MajP started this thread I modified it to change the StartupForm property as well (had not thought of a reason to have that before.)

While I was developing it, I accidentally created the User Property for the startup form as a boolean type instead of text. I then deleted the property and recreated it with the proper type. So, yes, you can delete the properties using

[i]database[/i].properties.delete [i]propertyname[/i]

 

[MazeWorX]

however you cant stop the AutoExec macro from firing if it is present this is a function of access. Having said this you can Import and export db objects

Private Sub Command148_Click()
    On Error Resume Next
    DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\db3.mdb", acMacro, "AutoExec", "AutoExecBackup"
    If Err.Number = 3024 Then
        MsgBox "Auto Exec macro does not exist"
        Exit Sub
    Else
        MsgBox "Import complete"
        DoCmd.TransferDatabase acExport, "Microsoft Access", "C:\db3.mdb", acMacro, "TempAutoExec", "AutoExec"
        MsgBox "Export complete"
    End If
End Sub

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[MajP]

Thanks again. Have not tested yet but seems based on what everyone says
1) Import and save the autoexec if it exists
2) Export Temp autoexec into target
3) use the dao.opendatabase to get a reference to the datbase without openining in the application
4) See if a start up form property exists and save the value
5) Set startup form to "(none)"
6) load using access.application.opencurrentdatabase
(no start up form or autoexec)
7) read the custom commandbars from the application command bars collection
8) close the application
9) reset the start up form property value
10) re-export the saved autoexec

 

[MajP]

Thanks for the help and ideas. So here is my complete solution. Hopefully someone knows a simpler way. But there are some useful things I learned in doing this. Works pretty well but the transfer causes a flicker and if the autoexec is in a db not in a trusted location you get a pop up.

Public Sub GetCBs()
  Dim db As DAO.Database
  Dim strPath As String
  Dim startUpform As String
  Dim app As Access.Application
  Dim custBars As Collection
  Dim custShortCutBars As Collection
  Dim custNonShortCutBars As Collection
  Dim i As Integer
  Dim blnAutoexec As Boolean
  strPath = GetOpenFile()
  'Get the db without opening in application
  Set db = getDb(strPath)
  'Get startupform
  startUpform = getStartUp(db)
  'Turn off the start up form
  TurnOffStartUp db
  'Check for and auto exec. If exists import and replace
  If hasAutoexec(db) Then
   blnAutoexec = True
   ImportAutoExec (strPath)
  End If
  Set app = New Access.Application
  'Open safely
  app.OpenCurrentDatabase (strPath)
  'Read command bars
  Set custBars = getCustBars(app)
  Set custShortCutBars = getCustShortCutBars(app)
  Set custNonShortCutBars = getCustNonShortCutBars(app)
  app.CloseCurrentDatabase
  Set db = app.CurrentDb
  Set db = getDb(strPath)
  'Return start up form
  TurnOnStartUp db, startUpform
  db.Close
  'Return auto exec
  If blnAutoexec Then
    ReturnAutoExec (strPath)
  End If
  Debug.Print "all custom bars:"
  'All bars
  For i = 1 To custBars.Count
    Debug.Print custBars(i)
  Next i
  'Do something with the command bars
  Debug.Print "all shortcut bars:"
  'Short cut only
  For i = 1 To custShortCutBars.Count
    Debug.Print custShortCutBars(i)
  Next i
  'Not short cut
  Debug.Print "Non shortCut"
  For i = 1 To custNonShortCutBars.Count
    Debug.Print custNonShortCutBars(i)
  Next i
End Sub
Public Function getDb(strPath As String) As DAO.Database
  Set getDb = DBEngine(0).OpenDatabase(strPath)
End Function

Public Function getCustBars(app As Access.Application) As Collection
 ' all bars
  Dim col As New Collection
  Dim cb As Object
  For Each cb In app.CommandBars
    If cb.BuiltIn = False Then
        col.Add (cb.Name)
    End If
  Next cb
  Set getCustBars = col
End Function

Public Function getCustShortCutBars(app As Access.Application) As Collection
 ' only short cut bars
  Dim col As New Collection
  Dim cb As commandbar
  For Each cb In app.CommandBars
    If cb.BuiltIn = False Then
       If cb.Type = msoBarTypePopup Then
         col.Add (cb.Name)
       End If
    End If
  Next cb
  Set getCustShortCutBars = col
End Function

Public Function getCustNonShortCutBars(app As Access.Application) As Collection
 ' Menu bars that are not shortcut bars
  Dim col As New Collection
  Dim cb As commandbar
  For Each cb In app.CommandBars
    If cb.BuiltIn = False Then
       If cb.Type <> msoBarTypePopup Then
         col.Add (cb.Name)
       End If
    End If
  Next cb
  Set getCustNonShortCutBars = col
End Function

Public Function getStartUp(db As DAO.Database) As String
  Dim prp As DAO.Property
  For Each prp In db.Properties
    If prp.Name = "startupform" Then
      getStartUp = prp.Value
      Exit For
    End If
  Next
End Function
Public Sub TurnOffStartUp(db As DAO.Database)
   Dim prp As DAO.Property
   For Each prp In db.Properties
    If prp.Name = "startupform" Then
         prp.Value = "(None)"
      Exit For
    End If
  Next
End Sub
Public Sub TurnOnStartUp(db As DAO.Database, strFrm As String)
   Dim prp As DAO.Property
   For Each prp In db.Properties
    If prp.Name = "startupform" Then
       prp.Value = strFrm
       Exit For
    End If
  Next
End Sub
Public Sub ImportAutoExec(strPath As String)
    On Error GoTo errLbl
    DoCmd.TransferDatabase acImport, "Microsoft Access", strPath, acMacro, "AutoExec", "AutoExecBackup"
    DoCmd.TransferDatabase acExport, "Microsoft Access", strPath, acMacro, "TempAutoExec", "AutoExec"
    Exit Sub
errLbl:
   If Err.Number = 7874 Then
        Debug.Print "Auto Exec macro does not exist"
    Else
      MsgBox Err.Number & " " & Err.Description
    End If
End Sub
Public Sub ReturnAutoExec(strPath As String)
On Error GoTo errLbl
    DoCmd.TransferDatabase acExport, "Microsoft Access", strPath, acMacro, "AutoExecBackup", "AutoExec"
    DoCmd.DeleteObject acMacro, "AutoExecBackup"
    Exit Sub
errLbl:
   If Err.Number = 7874 Then
        Debug.Print "Auto Exec macro does not exist"
    Else
      MsgBox Err.Number & " " & Err.Description
    End If
End Sub
Public Function hasAutoexec(db As DAO.Database) As Boolean
  Dim rs As DAO.Recordset
  Dim strSql As String
  strSql = "SELECT MSysObjects.Name FROM MSysObjects WHERE MSysObjects.Name = 'AutoExec' AND MSysObjects.Type = -32766"
  Set rs = db.OpenRecordset(strSql)
  If Not (rs.EOF And rs.BOF) Then
    hasAutoexec = True
  End If
End Function

 

[Gammachaser]

That looks like it would do the trick, but I have a question (I am always trying to learn some better practices.)

Why do

Public Sub TurnOffStartUp(db As DAO.Database)
   Dim prp As DAO.Property
   For Each prp In db.Properties
    If prp.Name = "startupform" Then
         prp.Value = "(None)"
      Exit For
    End If
  Next
End Sub

when

Public Sub TurnOffStartup(db As DAO.Database)
db.Properties("StartUpForm") = "(none)"
End Sub

works fine?

 

[MazeWorX]

MajP you might find this helpful

http://bytes.com/topic/access/answers/866696-check-trusted-path-registry

Why do

because if the property doesn't exist and we try to change it it, it will throw an error.

Now having said this we could test for properties then gather the required information and only change what was required. I'm not sure this would require less code that Mp has posted already

thoughts?



HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>