Bind an Access Form's Record Source from an XML File at Runtime

  • Article

 

Tim Getsch
Frank Rice
Microsoft Corporation

May 2004

Applies to:
    Microsoft® Office Access 2003

Summary: Learn how to bind a form dynamically to a recordset created from an XML file. This simple application can be the starting point for a powerful solution for your customers and should be considered a part of your arsenal when developing Access application. (8 printed pages)

Contents

Introduction
Overview
Import XML and Create an Access Table
Function to Determine the Table Name
Routine to Bind a Recordset to an Unbound Form
Conclusion

Introduction

With the prominence of Extensible Markup Language (XML), it is likely that at one time or another while developing solutions in Microsoft® Office Access 2003, you will need to import and use data from an XML file. Likewise, there may be instances where you need to bind the record source of a form at runtime. Fortunately, both of these operations are relatively easy to create and are equally easy to combine into one. Among the benefits of combining the operations into one smooth function are that you remove a level of complexity from your users. This also makes your solutions appear more professional as these operations are completed behind the scenes and away from your customers.

This article demonstrates programmatically importing an XML file to an Access table. A recordset is then created from the table and dynamically bound to a form.

Note   The examples in this article are written using the Data Access Objects (DAO) library. For similar examples using ActiveX Data Objects (ADO), see the book Programming Microsoft Office Access 2003 from Microsoft Press written by Rick Dobson.

Overview

This application works by first prompting the user for the path and name of the XML file to be imported. The code then sets a variable to the current date and time. This variable is used later to help find the table created during the import operation. Next, the procedure imports the XML file and creates the table. Then, the RetrieveNewestTableName function is called with the current data and time variable created earlier. This function retrieves the name of the table created during the import. The returned value is then checked to make sure it isn't empty and thus, generate an error. Next, the user is prompted for the name of the unbound form. The recordset will be bound to this form. Finally, the subroutine calls the BindRstToUnboundForm function that binds the table's recordset to the form's Recordset property.

Next we'll look at the function that determines the name of the newly created table. To understand why this procedure is necessary, consider that the table created during import is based on the name of the XML file. If the name of the input file matches the name of an existing table in the database, the new table uses the name of the existing table concatenated with a number incremented by 1. So, for example, if the name of the XML file is Employees.xml and the Employees table already exists in the database, the new table is created as Employees1. If you import the file again and the Employees and Employees1 tables exist, the new table is designated Employees2. So to make sure that the recordset is created from the correct table, it is important to determine which table is the correct one.

To determine the correct table, the code loops through the list of tables and first checks for ImportErrors table and, if found, ignores the table. The ImportErrors table is created if there are errors during the import of data from, for example, the XML file. Because this could be listed as the latest table, it is eliminate in the code. Next, the variable containing the current date and time is compared to the DateCreated property of the each table and the name of the latest table is stored in a variable. The name of the table is then returned to the calling procedure.

Once the code determines the table name, the name of the table and form are passed to another function that creates a recordset based on the table. The procedure then assigns the recordset to the Recordset property of the form. The form is displayed and the code displays a test record in the Immediate window of the Microsoft Visual Basic® Editor to verify that the routine is working correctly.

Import XML and Create an Access Table

The code in this section demonstrates how to use the ImportXML method to import data from an XML file and create a table.

  1. Ensure that you have an XML and XSD file in the correct format such as those created by the ExportXML method. You create XML and XSD files from Access by using the ExportXML method in a standard module in the Visual Basic Editor or on the File menu, by clicking Export and then selecting XML in the Save as type drop-down box.

  2. To use the ExportXML method, the following code example exports the table named Customers in the current database to an XML file; the data and schema are exported as separate files.

    Application.ExportXML _
    ObjectType:=acExportTable, _
    DataSource:="Customers", _
    DataTarget:="Customers.xml", _
    SchemaTarget:="CustomersSchema.xsd"

  3. Next, ensure that you have an unbound form with fields similar to the imported table to bind to the recordset. For the purpose of this article, you can create one from the existing Customers form in the Northwind sample database. To do this:

    1. Start Access and open the Northwind sample database. By default, this database is located at C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb.
    2. Click the Customers form.
    3. On the File menu, click Save As, type a name for the new form (Customers1, for example), and then click OK.
    4. Make sure that the Record Source property is blank by opening the form in Design view.
    5. On the View menu, click Properties to open the Properties window for the form.
    6. Click the All tab, scroll to the top to the Record Source box and clear it, if necessary.

  4. Then, create another unbound form.

    Note   This form is different that the form created in step 3 and is used to run the procedures that make up the application.

  5. Add a command button by performing the following steps:

    1. In the Database window, click Forms under Objects.
    2. Click the New button on the Database window toolbar.
    3. In the New Form dialog box, click Design View. Leave the table or query source drop-down box blank. Click OK.

  6. Double-click a command button from the Toolbox to add it to the form. If the Toolbox isn't visible, click the View menu and then click Toolbox.

  7. Click Cancel on the Command Button Wizard dialog box.

  8. Position the command button in the center of the form.

  9. Add code to the command button with the following steps:

    1. With the form in Design view, double-click the command button. In the Property window, click the Event tab, and then click the OnClick property.
    2. Click Build button next to the property box to display the Choose Builder dialog box.
    3. Double-click Code Builder to display the event procedure Sub and End Sub statements in the form module.

  10. Next, set references to the Microsoft Office 11.0 Object Library library and the Microsoft DAO 3.6 Object Library library with the following steps:

    1. On the Tools menu, click References.
    2. In the Available References box, select the check box next to the Microsoft Office 11.0 Object Library library.
    3. Next, select the check box next to the Microsoft DAO 3.6 Object Library library reference, if necessary.

  11. Add the following code to the OnClick event procedure between Sub and End``Sub statements:

        Dim fd As Office.FileDialog
    Dim strTableName As String
    Dim strFormName As String
    Dim datBeforeImport As Date
    Const strFileExt = ".xml"

    On Error GoTo Path_Err

    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    fd.Filters.Add "XML", "*.XML"
    fd.Show

    'User didn't enter a file path.
    If fd.SelectedItems.Count = 0 Then
       MsgBox "You must select an " & _
       "XML file. Please try again."
       Exit Sub
    End If

    'Set variable later used to find table created
    'by ImportXML method.
    datBeforeImport = Now
    'Invoke the ImportXML method against the xml file.
    Application.ImportXML fd.SelectedItems(1), acStructureAndData

    'Check to make sure that RetrieveNewestTableName
    'function doesn't return an error code which would
    'generate a false error in this procedure.
    strTableName = RetrieveNewestTableName(datBeforeImport)
    If strTableName = "" Then
       Exit Sub
    End If

    'Prompt user for name of form to use.
    strFormName = InputBox("Type the name of the form you want to " & _
                 "use. It should have the same fields as the " & _
                 "recordset (table) the form will be based on.")

    BindRstToUnboundForm strTableName, strFormName

Exit_Sub:
       Exit Sub

Path_Err:
    If Err.Number = 31527 Then
       MsgBox "The XML file was not found. Check the spelling " & _
          "or that the file exists and try again."
       GoTo Exit_Sub
    Else
       MsgBox "Operation aborted for the following reason. " & _
          vbCrLf & "Error Number: " & Err.Number & " " & _
          vbCrLf & "Error Description: " & " " & Err.Description
       GoTo Exit_Sub
    End If

This subroutine essentially imports a XML file and creates an Access table. It also calls the other procedures in the application.

The procedure starts by displaying a file dialog box using the FileDialog method of the Application object, filtered for XML files in order to prompt the user for the name and path to the XML file. Then, the code sets the variable that compares against the creation date and time of the table created by the ImportXML method. Next, the subroutine calls the ImportXML method to read the XML file and create a table for the data. The subroutine then calls the function RetrieveNewestTableName and checks to see if that function has returned an empty String. Next, the procedure prompts the user for the name of the form that the recordset will be bound to. Then the BindRstToUnboundForm function is called.

Function to Determine the Table Name

The following function cycles through a filtered list of tables in the database and determines the name of the table just created during the import of the XML file.

  1. Add the following function in the area outside of the previous subroutine.

    Function RetrieveNewestTableName(datStartDate As Date) As String
    Dim datDateComp As Date
    Dim strNewestTableName As String
    Dim tbl As DAO.TableDef
    datDateComp = datStartDate
    For Each tbl In CurrentDb.TableDefs
        If tbl.DateCreated >= datDateComp Then
            If Left(tbl.Name, 12) = "ImportErrors" Then
                ' Ignore ImportErrors tables
            Else
                strNewestTableName = tbl.Name
                datDateComp = tbl.DateCreated
            End If
        End If
    Next tbl
    RetrieveNewestTableName = strNewestTableName
    Debug.Print RetrieveNewestTableName
End Function

This procedure determines the name of the table created during the import operation. It does this by comparing the DateCreated property of the tables.

The procedure first sets a local variable to the date and time that later will be compared to the creation date and time of each table. Then, the procedure loops through each table in the TableDefs collection of the CurrentDB object and, first, determines if the current table is the ImportErrors table. If there were errors during the import process, the records that weren't imported are stored in this table. Since this table might be the latest created, it would erroneously be bound to the form if selected. Next, the For Each...Next loop compares the creation date to (initially) the current date. If the creation date and time of the table is larger, then the name and creation date of the table are stored in variables. The cycle repeats and the comparison is now between the last table that met the criteria and the current table. Either way, when all of the lists of tables are examined, the code passes the name of the latest table to the RetrieveNewestTableName procedure and the table name is then displayed to the user.

Routine to Bind a Recordset to an Unbound Form

The following subroutine illustrates dynamically assigning a recordset to an unbound form at runtime. Insert the procedure to the area outside the previous function:

Function BindRstToUnboundForm(strDataSource As String, strFormName As String) As DAO.Recordset
    On Error GoTo ErrorHandler
    
    Set BindRstToUnboundForm = CurrentDb.OpenRecordset(strDataSource, dbOpenDynaset)
    
    'Assign recordset to Recordset property of form.
    DoCmd.OpenForm strFormName
    Set Application.Forms(strFormName).Recordset = BindRstToUnboundForm
    
    'Print record to check that things went smoothly.
    Dim fld As DAO.Field
    For Each fld In BindRstToUnboundForm.Fields
        Debug.Print fld.Name & " = " & fld.Value
    Next

Exit_Sub:
   Exit Function

ErrorHandler:
    Select Case Err.Number
    Case 2102
       MsgBox "The form was not found. Check the spelling " & _
          "or that the form exists and try again."
       GoTo Exit_Sub
    Case 2494
       MsgBox "You must type a name for the form."
       GoTo Exit_Sub
    Case Else
       MsgBox "Operation aborted for the following reason. " & vbCrLf & _
          "Error Number: " & Err.Number & " " & vbCrLf & _
          "Error Description: " & " " & Err.Description
       GoTo Exit_Sub
    End Select
End Function

This subroutine starts out creating a recordset from the table created with XML data. It does by using the OpenRecordset method of the CurrentDB object. Next, the unbound form is displayed using the OpenForm method of the DoCmd object. Then the table's recordset is assigned to the Recordset property of the form. And finally, a test record is displayed to verify that the application executed as expected.

Conclusion

Dynamically setting the record source of a form is a handy way to make your forms more versatile. Likewise, programmatically importing an XML file to a table is a quick way to put that data to use. Combining the two operations hides a level of complexity from your users. This simple application is the starting point for an even more powerful solution for your customers and should be considered a part of your arsenal when developing Access application.