ACC97: DAO Property Retrieval Slow with Linked Table Objects

• Microsoft Access 97

SYMPTOMS

Advanced: Requires expert coding, interoperability, and multiuser skills.

When you use data access objects (DAO) to retrieve properties for linked TableDef objects, the process is much slower than when you use DAO to retrieve the properties for local Tabledef objects.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to the "Building Applications with Microsoft Access 97" manual.

CAUSE

The Microsoft Jet database engine must create a temporary query for each property retrieval of a linked TableDef object. The Jet database engine does not need to do this when retrieving properties for local TableDef objects.

RESOLUTION

Instead of retrieving the properties for the linked TableDef objects in the current database, you can use DAO to open the database that the TableDef objects actually reside in, and then retrieve the properties. To accomplish this, follow steps 1 - 17 in the "Steps to Reproduce Behavior" section later in this article and substitute the following procedure for the procedure documented in step 15:

   Sub EnumProperties(tDefName As String)
       Dim db As Database
       Dim tDef As TableDef
       Dim tDefSourceTableName As String
       Dim tDefProperty As Property
       Dim PropCount As Integer
       Dim dbPath As String
       Dim tDefField As Field
       Dim StartTime As Date, EndTime As Date

       StartTime = Now
       Set db = CurrentDb
       Set tDef = db.TableDefs(tDefName)

       If (tDef.Attributes And dbAttachedTable) <> 0 Then
           If InStr(tDef.Connect, ".mdb") > 0 Then
               tDefSourceTableName = tDef.SourceTableName
               dbPath = Right$(tDef.Connect, _
                        Len(tDef.Connect) - _
                        InStr(tDef.Connect, "="))
               Set db = DBEngine(0).OpenDatabase(dbPath)
               Set tDef = db.TableDefs(tDefSourceTableName)
           End If
       End If

       For Each tDefProperty In tDef.Properties
       Next

       For Each tDefField In tDef.Fields
           For Each tDefProperty In tDefField.Properties
               PropCount = PropCount + 1
           Next
       Next

       EndTime = Now
       Debug.Print
       Debug.Print "Table: " & tDef.Name
       Debug.Print "Number of Table and Field Properties: " & _
          tDef.Properties.Count + PropCount
       Debug.Print "Total Time: " & _
          DateDiff("s", StartTime, EndTime) & " second(s)"
       db.Close
   End Sub

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform these steps on a copy of the database.

1. Open the sample database Northwind.mdb.

2. Create a module and type the following line in the Declarations section if it is not already there:

   Option Explicit

3. Type the following procedure. This procedure adds 700 user-defined properties to the Employees table.

          Sub CreateProperties()
             Dim db As Database
             Dim tDef As TableDef
             Dim prop As Property
             Dim i As Integer
   
             Set db = CurrentDb
             Set tDef = db.TableDefs!Employees
             For i = 1 To 700
                Set prop = tDef.CreateProperty("Prop" & i, dbText, i)
                tDef.Properties.Append prop
             Next
             tDef.Properties.Refresh
             MsgBox "Number of Properties: " & tDef.Properties.Count
             db.Close
          End Sub
   
   

4. To run this procedure, type the following line in the Debug window, and then press ENTER.

   CreateProperties

   Note that the message box indicates the number of properties that were added to the Employees table. This number will include any existing properties as well.

5. Create a new, blank database named PropTest.mdb.

6. On the File menu, point to Get External Data, and then click Import.

7. In the Import dialog box, select Microsoft Access (*.mdb) from the Files Of Type list.

8. Locate your Office97\Office\Samples folder, select Northwind.mdb, and then click Import.

9. In the Import Objects dialog box, select the Employees table, and then click OK.

10. On the File menu, point to Get External Data, and then click Link Tables.

11. In the Link dialog box, select Microsoft Access (*.mdb) from the Files Of Type list.

12. Locate your Samples folder, select Northwind.mdb, and then click Link.

13. In the Link Tables dialog box, select the Employees table, and then click OK. Note that the linked table is named "Employees1."

14. Create a module and type the following line in the Declarations section if it is not already there:

    Option Explicit

15. Type the following procedure:

           Sub EnumProperties(tDefName As String)
               Dim db As Database
               Dim tDef As TableDef
               Dim tDefProperty As Property
               Dim PropCount As Integer
               Dim tDefField As Field
               Dim StartTime As Date, EndTime As Date
    
               StartTime = Now
               Set db = CurrentDb
               Set tDef = db.TableDefs(tDefName)
               For Each tDefProperty In tDef.Properties
               Next
    
               For Each tDefField In tDef.Fields
                  For Each tDefProperty In tDefField.Properties
                     PropCount = PropCount + 1
                  Next
               Next
    
               EndTime = Now
               Debug.Print
               Debug.Print "Table: " & tDef.Name
               Debug.Print "Number of Table and Field Properties: " & _
                   tDef.Properties.Count + PropCount
               Debug.Print "Total Time: " & _
                   DateDiff("s", StartTime, EndTime) & " second(s)"
               db.Close
           End Sub
    
    

16. Test this procedure with the local Employees table to determine the amount of time it takes to enumerate its properties. To run this procedure, type the following line in the Debug window, and then press ENTER.

    EnumProperties "Employees"

    Note the number of seconds it takes to enumerate through all properties of the Employees table.

17. Test this procedure with the linked Employees1 table to determine the amount of time it takes to enumerate its properties. To run this procedure, type the following line in the Debug window, and then press ENTER.

    EnumProperties "Employees1"

    Note that the number of seconds it takes to enumerate the properties of the linked table is much higher.

Keywords          : kbprg PgmHowTo MdlDao TblOthr
Version           : 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbprb
Solution Type     : kbcode