ACC: How to Use the Seek Method on Linked Tables

MORE INFORMATION

1. Create a new database and name it DB1.MDB.
2. On the File menu, click Get External Data, and then click Link Tables.
   
   NOTE: In version 2.0, on the File menu, click Attach Table.
3. Select the Northwind.mdb file, and then click Link.
   
   NOTE: In version 2.0, in the Attach dialog box, select Microsoft Access, and then click OK. Then, in the Select Microsoft Access Database dialog box, select NWIND.MDB, and click OK.
4. In the Link Tables dialog box, select Orders, and click OK.
   
   NOTE: In version 2.0, in the Attach Tables dialog box, select Orders, and then click Attach.
5. Create a module and type the following line in the Declarations section if the line is not already there:

   Option Explicit

6. Type the following procedure.
   
   NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

         Sub Seek_Attached_Table (Tablename$, Indexname$, SearchValue)
         Dim db As Database
         Dim t As TableDef
         Dim rs As Recordset
         Dim dbpath$, SourceTable$
   
         On Error GoTo SA_Errors
   
         Set db = dbengine(0)(0)
         dbpath = Mid(db(Tablename$).connect, InStr(1, _
            db(Tablename$).connect, "=") + 1)
         If dbpath = "" Then MsgBox "You've chosen a table already in the _
            current database", 64, "": Exit Sub
         SourceTable = db(Tablename).sourcetablename
   
         Set db = dbengine(0).OpenDatabase(dbpath)
         Set rs = db.OpenRecordset(SourceTable, db_open_Table)
         rs.index = Indexname
         rs.Seek "=", SearchValue
   
         If Not rs.nomatch Then
            MsgBox "Found It!", 64
         Else
            MsgBox "Not Found", 64
         End If
   
         rs.Close
         db.Close
   
         Exit Sub
         SA_Errors:
            MsgBox Error, 16, CStr(Err)
            Exit Sub
   
         End Sub
   						

7. To run the Sub procedure, type the following line in the Debug window (or Immediate window in version 2.0), and then press ENTER:

   Seek_Attached_Table "Orders","PrimaryKey",11000

   Note that the message "Found It!" appears.