Access Cookbook - Ken Getz [129]
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
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.
6.6. Get a Complete List of Field Properties from