[AccessD] Multiple Table Search

Mark,

    Sorry for the mis-information on Find and Replace.  I see Drew's already
come up with a more through solution, but for those who are interested,
below is a down and dirty DAO based sub to do what you want.  You could
easily extend to save the results in a table, search multiple databases,
etc.

Jim Dettman
(315) 699-3443
jimdettman at earthlink.net

Option Compare Database
Option Explicit

Public Sub FindItem(strSearchFor As String)

  Dim curDB As DAO.Database
  Dim rs As DAO.Recordset
  Dim intK As Integer
  Dim intI As Integer
  Dim lngCount As Long

  Set curDB = CurrentDb()

  ' Table loop
  For intK = 0 To curDB.TableDefs.Count - 1
    Set rs = curDB.OpenRecordset(curDB.TableDefs(intK).Name,
dbOpenForwardOnly)
    lngCount = 1

    Do While rs.EOF = False
      ' Loop for fields
      For intI = 0 To rs.Fields.Count - 1
         If InStr(1, rs(intI), strSearchFor) > 0 Then
            Debug.Print "Table: "; curDB.TableDefs(intK).Name; " Record: ";
lngCount; "  Field: "; rs(intI).Name
         End If
      Next intI
      rs.MoveNext
      lngCount = lngCount + 1
    Loop
  Next intK

  Debug.Print "Complete"
End Sub



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Mitsules, Mark
S. (Newport News)
Sent: Monday, July 26, 2004 2:38 PM
To: [AccessD]
Subject: [AccessD] Multiple Table Search


Group,

Strange request, but I've been handed this db and am not familiar enough
with the data to do an educated search.   What is the simplest method to
find an instance of data amongst 70+ tables.

Mark
--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com