Running code before any forms open in Access

Question

So I have an Access database with a front and a back end. I will be distributing it to users soon, but I have no control over where exactly they will put the files on their computers. However, I think I can count on them putting front and back ends in the same folder.

As such, when the front end opens, I want it to check that the linked tables are corretly connected to the backend database. I have working code for this; however I don't know where to put it. When the front end opens, a menu form is automatically opened (configured through the startup dialogue box). I have put the code in the OnOpen event, which I thought occurred before any data is loaded, but when I test this out, I get a message telling me that the backend cannot be found (it's looking in its old location).

Basically, is there an event I can use that runs before any forms have opened?

Answer 1

Create a Macro and name it "autoexec". For the macro action, select "RunCode" and then set the function name to the name of the function you use to check your linked tables.

Answer 2

As Matt said, create a macro, call it "autoexec", and select "RunCode" as the macro action. The Function Name argument should be the name of the function you wish to run (and not a sub), and if the function has no arguments, you should still put () at the end, or it won't work.

Answer 3

Before putting your front end and back end in the same folder, think about it. Isn't it worth having 2 folders? What about multiple users on the same computer accessing the same back-end database? What about multiple users accessing the same databse through a network? What is the necessity of having a front end-back end typology if your app is basically a single-user app?

Why don't you add a dialog box to your app, in case your connectivity is lost? You could create a fileDialog object in your code, allowing the user to browse for a *mdb file anywhere on his computer/network. It is then possible to control that the selected mdb file contains all requested tables and open the corresponding links (I guess you are using the transferDatabase command).

And what about additional tools/references you'll need for your app to run when you'll distribute it to your final users? By default, MS Access records the 3 basic ones:

• Visual Basic For Application
• Microsoft Access Library
• Microsoft DAO Library

If your app needs anything else, such as ADO or Office objects (ADODB.recordset or Office commandbars for example), you will have to add the references manually for each installation, as the final user won't be able to open the VBA window and access the tools/references menu.

So, if you need to deploy your app on multiple computers, I strongly advise you to use a deployment tool such as this free one. You'll need a few hours to be able to use it properly, but the result is worth it. You'll be able to give your clients a real installer module. It will create folders, add requested shortcuts, and manage references in the computer's registry. This will make your deployment definitely painless!

EDIT: the autoexec macro is definitely the right solution for calling code before any event.

EDIT: don't forget that your final users can make profit of the runtime version of Access, which is free!

Answer 4

I generally prefer to create a small form that runs a number of checks, such as finding the back-end and so forth, and set various options. The code for the open event of this form might be:

Me.Visible = False
'Determines if the database window is displayed
SetProp "StartupShowDBWindow", False, dbBoolean
'Hide hidden and system objects
SetOption "Show Hidden Objects", False
SetOption "Show System Objects", False

'Find back end
CheckLinkPath

I keep a table of tables to be linked in the front-end and if any are missing, this form can be used to report the error, or any other error for that matter.

Set RS = CurrentDb.OpenRecordset("Select TableName From sysTables " _
& "WHERE TableType = 'LINK'")

RS.MoveFirst
strConnect = db.TableDefs(RS!TableName).Connect
If Not FileExists(Mid(strConnect, InStr(strConnect, "DATABASE=") + 9)) Then
    'All is not well
    blnConnectError = True
Else
    Do Until RS.EOF()
        If db.TableDefs(RS!TableName).Connect <> strConnect Then
            blnConnectError = True
            Exit Do
        End If

        RS.MoveNext
    Loop
End If

If everything is ok, the small form calls the main menu or form and the user never sees the checking form. I would also use this form to open a password prompt, if required.

Answer 5

As others have suggested, I'd use the AutoExec macro in this case. If your code that checks the linked tables is currently a sub, change it to a function that returns TRUE if it succeeds. You can then use the "conditions" column in the AutoExec macro to exit the application with a user-friendly error dialog if the link table code fails. Your AutoExec macro could be something like:

1. Call your LinkTables() function, set condition to terminate startup if it fails.
2. Call your "Main" startup menu form.