CreateTableDef Method, FillCache Method, and CacheSize, CacheStart and SourceTableName Properties Example (Client/Server)

This example uses the CreateTableDef and FillCache methods and the CacheSize, CacheStart and SourceTableName properties to enumerate the records in a linked table twice. Then it enumerates the records twice with a 50-record cache. The example then displays the performance statistics for the uncached and cached runs through the linked table.

Sub ClientServerX3()

   Dim dbsCurrent As Database
   Dim tdfRoyalties As TableDef
   Dim rstRemote As Recordset
   Dim sngStart As Single
   Dim sngEnd As Single
   Dim sngNoCache As Single
   Dim sngCache As Single
   Dim intLoop As Integer
   Dim strTemp As String
   Dim intRecords As Integer

   ' Open a database to which a linked table can be 
   ' appended.
   Set dbsCurrent = OpenDatabase("DB1.mdb")

   ' Create a linked table that connects to a Microsoft SQL
   ' Server database.
   Set tdfRoyalties = _
      dbsCurrent.CreateTableDef("Royalties")
   tdfRoyalties.Connect = _
      "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers"
   tdfRoyalties.SourceTableName = "roysched"
   dbsCurrent.TableDefs.Append tdfRoyalties
   Set rstRemote = _
      dbsCurrent.OpenRecordset("Royalties")

   With rstRemote
      ' Enumerate the Recordset object twice and record
      ' the elapsed time.
      sngStart = Timer

      For intLoop = 1 To 2
         .MoveFirst
         Do While Not .EOF
            ' Execute a simple operation for the
            ' performance test.
            strTemp = !title_id
            .MoveNext
         Loop
      Next intLoop

      sngEnd = Timer
      sngNoCache = sngEnd - sngStart

      ' Cache the first 50 records.
      .MoveFirst
      .CacheSize = 50
      .FillCache
      sngStart = Timer

      ' Enumerate the Recordset object twice and record
      ' the elapsed time.
      For intLoop = 1 To 2
         intRecords = 0
         .MoveFirst
         Do While Not .EOF
            ' Execute a simple operation for the
            ' performance test.
            strTemp = !title_id
            ' Count the records. If the end of the
            ' cache is reached, reset the cache to the
            ' next 50 records.
            intRecords = intRecords + 1
            .MoveNext
            If intRecords Mod 50 = 0 Then
               .CacheStart = .Bookmark
               .FillCache
            End If
         Loop
      Next intLoop

      sngEnd = Timer
      sngCache = sngEnd - sngStart

      ' Display performance results.
      MsgBox "Caching Performance Results:" & vbCr & _
         "  No cache: " & Format(sngNoCache, _
         "##0.000") & " seconds" & vbCr & _
         "  50-record cache: " & Format(sngCache, _
         "##0.000") & " seconds"
      .Close
   End With

   ' Delete linked table because this is a demonstration.
   dbsCurrent.TableDefs.Delete tdfRoyalties.Name
   dbsCurrent.Close

End Sub