Access Cookbook 2nd Edition-Access Cookbook 2nd Edition

Recipe 6.5 Quickly Find a Record in a Linked Table

6.5.1 Problem

You like to use the ultra-fast Seek method to search for data in indexed fields in your table-type recordsets, but the Seek method won't work with linked tables because you can only open dynaset-type DAO recordsets against linked tables. You can use the Find methods to search for data in these types of recordsets, but Find is much slower at finding data than Seek. Is there any way to use the Seek method on linked tables?

6.5.2 Solution

The Seek method works only on table-type recordsets, so you can't perform seeks on linked tables. However, there's no reason why you can't open the source database that contains the linked table and perform the seek operation there. This solution shows you how to do this.

To use the Seek method on external tables, follow these steps:

Use the OpenDatabase method to open the source database that contains the linked table. For example, in the event procedure attached to the cmdSeek command button on the sample form, frmSeekExternal, you'll find the following code:

Set wrk = DBEngine.Workspaces(0)

' Directly open the external database. It will be opened
' nonexclusively, read-write, and with type = Access.
Set dbExternal = _
  wrk.OpenDatabase(acbGetLinkPath("tblCustomer"),, False, False, "")

Create a table-type recordset based on the source table. If you renamed the table when you linked to it, make sure you use the name used in the source database. The sample form uses this code:
```
' Create a table-type recordset based on the external table.
Set rstCustomer = dbExternal.OpenRecordset("tblCustomer", dbOpenTable)
```

Set an index and perform the seek operation, as in this code behind the sample form:

' This index consists of last and first names.
rstCustomer.Index = "FullName"

' Perform the seek and then check if the record was found.
rstCustomer.Seek "=", ctlLName.Value, ctlFName.Value

Any time you perform a seek or a find, you must next check to see if the operation was successful. You do this using the NoMatch property of the recordset. For example, on the sample form, you'll find the following code:

strMsg = "The record for " & ctlFName & ctlLName & " was"
If Not rstCustomer.NoMatch Then
    strMsg = strMsg & " found!" & vbCrLf & vbCrLf
    strMsg = strMsg & "Customer# = " & rstCustomer![Customer#]
    MsgBox strMsg, vbOKOnly + vbInformation, "External Seek"
Else
    strMsg = strMsg & " not found!"
    MsgBox strMsg, vbOKOnly + vbCritical, "External Seek"
End If

Close the recordset and the external database. The sample form uses this code:
```
rstCustomer.Close
dbExternal.Close
```

To see an example, copy the 06-05.MDB and 06-05Ext.MDB databases to a folder on your hard drive. The 06-05.MDB database is linked to the tblCustomers table in 06-05Ext.MDB. Code in frmRelink, the startup form in 06-05.MDB, takes care of relinking to the tblCustomer table in 06-05Ext.MDB (we explain this technique later in this chapter). Open the frmSeekExternal form from 06-05.MDB. Enter a first and last name for which to search (you may find it helpful to browse through tblCustomer first) and press the Use Seek command button (see Figure 6-8). Even though this table does not exist in the 06-05.MDB database, the row will be retrieved using the fast Seek method.

Figure 6-8. The frmSeekExternal form

6.5.3 Discussion

The key to this technique is using the OpenDatabase method on the workspace object to open the external database directly where the linked table physically resides. The OpenDatabase method takes four parameters, which are detailed in Table 6-4.

Table 6-4. The OpenDatabase method's parameters

Parameter

Description

frmSeekExternal example

dbname

The name of the database, including the path

acbGetLinkPath("tblCustomer")

exclusive

True to open the database exclusively

False

read-only

True to open the database in read-only mode

False

source

The Connect string for opening the database

"" indicates an Access database

Here's the code that opens the database in the sample form:

Set dbExternal = _
  wrk.OpenDatabase(acbGetLinkPath("tblCustomer"), False, False, "")

The function call, acbGetLinkPath("tblCustomer"), retrieves the path and filename of the linked database containing tblCustomer by parsing the Connect property of the linked table.

The code for acbGetLinkPath is shown here:

 Function acbGetLinkPath(strTableName As String) As String
    On Error GoTo HandleErr
    Dim strConnect As String
    strConnect = CurrentDb.TableDefs(strTableName).Connect
    ' The path and filename are after ";DATABASE=".
    acbGetLinkPath = _
        Mid$(strConnect, InStr(strConnect, ";") + 10)
        
ExitHere:
    Exit Function
HandleErr:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & ": " & Err.Description, , "acbGetLinkPath"
    End Select
    Resume ExitHere
        
End Function

The logic behind this function is simple. The Connect property of a linked Access table always begins with ;DATABASE= and then contains the path to the linked database file. The Mid$ function allows you to start in the middle of a string and retrieve the remaining characters (or, optionally, just a specified number of those characters). We used the Instr function to find the semicolon, rather than assuming it is the first character of the Connect string, because other kinds of linked tables will identify the type of link before the semicolon. For example, the Connect property of a table linked to an Excel spreadsheet will begin with Excel;DATABASE=.

You won't notice much difference between the Seek and FindFirst or FindNext methods with small tables, but with tables containing many thousands of records, the difference in speed can be significant. Because there is overhead involved with attaching to an external database, the FindFirst method will sometimes even be faster on very small tables. Another option for large amounts of data that offers better performance than FindFirst or Seek is a parameterized query or a custom SQL statement to retrieve just the single record that you need. Seeks are most useful when you need to jump around in a table, finding many different records that don't share any criteria.

Our example uses an API call to time how long it takes to perform seeks and finds, but you won't notice a significant difference on the small sample data. This method of timing database activity is explained in Chapter 7.

You are not limited to using the Seek method on Access databases. It works with indexed, nonnative ISAM databases also, and the tables needn't be linked to the current database.

You can't perform a seek on text, spreadsheet, or ODBC data sources.

[ Team LiB ]