ACC2000: DAO Property Retrieval Slow with Linked Table Objects

ID: Q200575

The information in this article applies to:

Microsoft Access 2000

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

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

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.

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

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

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 through 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

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.

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.

Open the sample database Northwind.mdb.

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


Option Explicit

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

To run this procedure, type the following line in the Immediate 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.

Create a new, blank database named PropTest.mdb.

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

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

Browse to the Microsoft Office\Office\Samples folder, select Northwind.mdb, and then click Import.

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

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

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

Browse to the Samples folder, select Northwind.mdb, and then click Link.

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

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


Option Explicit

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

Test this procedure with the local Employees table to see how long it takes to enumerate its properties. To run this procedure, type the following line in the Immediate window, and then press ENTER:
```
EnumProperties "Employees" 
```
Note the number of seconds it takes to enumerate through all properties of the Employees table.

Test this procedure with the linked Employees1 table to see how long it takes to enumerate its properties. To run this procedure, type the following line in the Immediate 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.

Additional query words:

Keywords : kbprg kbdta MdlDao TblOthr
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb