Late Binding in Microsoft Access

Late binding means you can do not have to concern yourself with what version of other software, such as Word, Excel or Outlook, being installed on the target system. Or if that software is not installed at all on the target system.. We found this problem when a client had Outlook 98 everywhere and the IT manager decided to load Outlook 2000 just to play with it. You will, of course, have to ensure you do not use any version specific code.

Once you've changed the code as per the following snippet you then remove the reference. You will only encounter an error when your app executes lines of code belonging to the automation app which isn't installed. This is quite preferable to having an error when starting up the app and not allowing the users in the app at all. Or when hitting a mid, left or trim function call.

While this might be slightly slower when resolving the reference I find there to be no perceptible performance difference. Also you would likely set the reference once at the start of some kind of "batch run" and close it when done so the performance hit would be negligible.

You'll want to install the reference if you are programming or debugging and to use the handy object IntelliSense code expansion while in the VBA Editor. Then once the code is running smoothly change the conditional compiler constant. The code below will automatically remove the reference once you run the code again. You can easily add the reference back in later and change back the conditional compiler constant code if required to add some functionality or for debugging.

The #Const, #If, #Else and #End If are conditional compiler constants and directives. They are designed for exactly this situation. The VBA compiler sees the these conditional compiler constants and directives and "preprocesses" the code. Thus the compiled code won't even see the statements that it was told to ignore and you won't have see any code errors. See the VBA help for more details.

Note that the code between the two <======= lines can be removed if you feel you will remember to remove the reference yourself. Also this may prove to be troublesome.

Dim ref As Reference

' 0 if Late Binding
' 1 if Reference to Excel set.
#Const ExcelRef = 0
#If ExcelRef = 0 Then ' Late binding
    Dim objXL As Object
    Dim objWkb As Object
    Dim objSht As Object
    Set objXL = CreateObject("Excel.Application")
    ' Remove the Excel reference if it is present   -   <=======
    On Error Resume Next
    Set ref = References!Excel
    If Err.Number = 0 Then
        References.Remove ref
    ElseIf Err.Number <> 9 Then 'Subscript out of range meaning not reference not found
        MsgBox Err.Description
        Exit Sub
    End If
' Use your own error handling label here
On Error GoTo tagError - <=======
#Else
    ' a reference to MS Excel <version number> Object Library must be specified
    Dim objXL As Excel.Application
    Dim objWkb As Excel.Workbook
    Dim objSht As Excel.Worksheet
    Set objXL = New Excel.Application
#End If

Don't forget to close the objects and/or set obj = nothing when finished with it.

I could've put the logic in the routine that would add the Excel reference. However this would require hard coding the path of the Excel.exe program. And it only takes a few seconds to add it. The following code will show you your current references.

Constants

If you are using Outlook, Excel, Word constants or other constants, which are much easier to use (and understand of course) than values. then copy/define the constants into Access. Just use Access syntax in Access to define the constants in a global module. You can determine the value of the constants by going to the debug/immediate window, Ctrl + G, and keying in a question mark and the constant name. While you could insert the value of the constant directly into your code I'd strongly urge you to create the constant in a dim statement so you have some idea later on what that value in the code really means. Or put the name of the constant as a comment on the line right after the code by using an apostrophe.

You can easily locate these constants by changing your code to late binding and removing the reference. Then compile your code. All these constants will then cause errors.

Auto FE Updater distribute new and updated Front End databases to your users with several mouse clicks.

Granite Fleet Manager - the best designed fleet maintenance tracking and management system available