|[ Team LiB ]|
Recipe 6.5 Quickly Find a Record in a Linked Table
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?
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:
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.
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.
Here's the code that opens the database in the sample form:
Set dbExternal = _ wrk.OpenDatabase(acbGetLinkPath("tblCustomer"), False, False, "")
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.
|[ Team LiB ]|