Link to home
Start Free TrialLog in
Avatar of stephenlecomptejr
stephenlecomptejrFlag for United States of America

asked on

How to loop through columns in DAO Recordset as each table is looped through in Access?

Per the following code, I'm trying to loop through a long listing of tables linked to SQL Server and search out a value in every row and column... if it finds the value its looking for it puts in a temp table the table name and field name so that a person can go there to look at more information.  The problem is using DAO I don't know how to determine the number of columns which will be different per each table.  How may I rearrange below to determine number of columns so I can reiterate through the rs.Fields(x) to look up the values?

Again, I know what's wrong with this code already - I just don't know the syntax to loop through the columns as each table comes through....   A rearrangement of the code below would be ideal versus a mere explanation as to what is wrong.

Thank you sincerely!

Public Sub Find_This(sValue As String)

  Dim db As DAO.Database
  Dim docdef As DAO.Document
 
  Dim sSQL As String
  Dim rs As DAO.Recordset
 
  Dim sTable As String
  Dim sField As String
 
  sSQL = "DELETE * FROM [tblFound]"
  CurrentDb.Execute sSQL
  DoEvents
 
  Set db = CurrentProject.Application.CurrentDb
    With db.Containers!Tables
    For Each docdef In .Documents
   
      sTable = docdef.Name
     
      If "tblSQLTableNames" <> sTable And "tblFound" <> sTable Then
       
        sSQL = "SELECT * FROM [" & sTable & "]"
        Set rs = CurrentDb.OpenRecordset(sSQL)
        Do Until rs.EOF
       
          'instead of
          sField = rs.Fields(0)
       
          If sValue = sField Then
         
            sSQL = "INSERT INTO [tblFound] (FieldFound, TableFound) VALUES ('" & sField & "', '" & sTable & "')"
            CurrentDb.Execute sSQL
            DoEvents
           
          End If
         
          rs.MoveNext
        Loop
        rs.Close
        Set rs = Nothing
       
      End If
    Next docdef
    End With
    db.Close
  Set db = Nothing
 
End Sub
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of stephenlecomptejr

ASKER

Always gracious for the help capricorn!