How to Update Template References for LIMS 3.x

Summary

The information in this article applies to LIMS 2.x and newer.

If any of your LIMS workstations either now have or in the past had multiple versions of Microsoft Access and you have Excel export templates that query the LIMS for additional data such as those used with the Sample Summary report, you may need to update the template’s references to work correctly with LIMS version 3.x. This article provides step-by-step instructions to update the Excel template so it correctly queries the Access 2002 database used with LIMS version 3.x.

More Information

Typically, Excel export templates used with LIMS’ Sample Summary report require special consideration when LIMS is upgraded to a newer version of Microsoft Access. These templates normally query the LIMS for additional data since only data behind the Sample Summary report’s main report is exported automatically. Analytical results, which are part of a subreport, are not automatically exported. If your template includes an AnalysisData, LookupTable, or Customer worksheet it probably queries the LIMS to populate the worksheet.

To query the LIMS, an Excel template must include a reference to Microsoft Access and to Microsoft’s Data Access Objects (DAO). Adding a reference to another application or component allows an Excel workbook’s Visual Basic for Applications (VBA) macros to access the application’s objects. To query the data successfully in environments where multiple versions of Access are used, a template may have been configured to work specifically with Access 97 for LIMS version 2.x. Follow the steps below to update the template’s references and to set the Access version so it will work correctly with LIMS version 3.x.

Step One: Verify that the template queries the LIMS.

Step Two: Update the references.

Step Three: Set the Access version number.

Step One: Verify that the template queries the LIMS.

Only templates that query LIMS for additional data must be updated. To verify whether a template queries the LIMS, simply check for an existing reference to the Microsoft Access Object Library. Open the template in Excel, use Tools | Macro | Visual Basic Editor or Alt+F11 to open the Visual Basic Editor, then use Tools | References to view the current references for the template’s VBA project. If any version of the Microsoft Access Object Library is currently selected or marked “Missing”, you should proceed to step 2 below to update the references. If the Microsoft Access Object Library is not selected, the template does not query the LIMS for data so no updates are required.

Step Two: Update the references.

To update the references in your template, simply remove the references to the earlier versions of the Microsoft Access and Microsoft DAO object libraries then select the new versions. Open the template in Excel, use Tools | Macro | Visual Basic Editor or Alt+F11 to open the Visual Basic Editor, then use Tools | References to open the references dialog for the template’s VBA project. Uncheck (i.e. turn off) the references for Microsoft Access 8.0 Object Library and Microsoft DAO 3.51 Object Library. Now scroll down and select the Microsoft Access 10.0 Object Library and Microsoft DAO 3.6 Object Library. Note that 10.0 is the version number for Access 2002, the version used with LIMS 3.x.

The screen above shows an example of the references for one template. In addition to the Microsoft Access 10.0 and Microsoft DAO 3.6 object library references, your template may include additional references not shown in this example. Click the [OK] button to close the references dialog, use File | Close or Alt+Q to close the VBA Editor and return to Excel then save your changes.

Step Three: Set the Access version number.

When your template queries the LIMS for data, it includes VBA code that creates a connection to the running instance of LIMS. Whether the VBA code currently looks for any running version of Access or a specific Access version, it should be updated to look for Access version 10.0 (i.e. Access 2002).

To set the Access version number, open your template in Excel. Use the Name box (i.e. the pick list at the intersection of row and column labels) to see if your template has a “LIMSAccessVersion” named range. If the LIMSAccessVersion name exists, select it to move to that cell, change the value in the cell to 10 then close and save your template and you are finished. The screen below shows the LIMSAccessVersion cell in the LIMS Data Query workbook.

If your template does not include a LIMSAccessVersion named range, you must set the Access version in the template’s VBA code. Use Tools | Macro | Visual Basic Editor or Alt+F11 to open the Visual Basic Editor. If it is not already displayed use View | Project Explorer or Ctrl+R to display the Project Explorer. In the Project Explorer pane, double-click LIMSData under Microsoft Excel Objects to view the LIMSData sheet’s VBA code. Use Edit | Find or Ctrl+F to open the Find dialog, enter “Get Object” in the Find What field then click the [Find Next] button to locate either of the following lines:

    Set objAccess = GetObject(, "Access.Application")
    Set objAccess = GetObject(, "Access.Application.8")

Change or append the Access version number in the applicable line above so it now reads:

    Set objAccess = GetObject(, "Access.Application.10")

The screen below shows an example in the VBA Editor with the Access version number properly set to 10.

Use Debug | Compile VBAProject to check for errors. If an error is detected the offending line of VBA code will be highlighted with an error message. When there are no errors use File | Close or Alt+Q to close the VBA Editor and return to Excel then close the template saving your changes.