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