Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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:

1. 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, ")

2. 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)

3. 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

4. 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

5. 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.

 dbname

 exclusive

 read-only

 source

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.