ACC: How to Use the Seek Method on Linked Tables

ID: Q131829

The information in this article applies to:

Microsoft Access versions 2.0, 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

Although you cannot use the Seek method directly on a linked (attached) Microsoft Access table, by using Visual Basic for Applications, you can create a workaround. This article demonstrates a sample user-defined Sub procedure that you can use so that you can link a Microsoft Access table and use the Seek method to locate specified records.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.

MORE INFORMATION

To use the Seek method on a linked table from the sample database Northwind.mdb (or NWIND.MDB in version 2.0), follow these steps:

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.

REFERENCES

For more information about Linking Tables, search the Help Index for "Linking Tables," or ask the Microsoft Access 97 Office Assistant.

Additional query words: how to

Keywords          : kbprg MdlDao 
Version           : 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto

Last Reviewed: November 21, 1998