Avatar of stephenlecomptejr
stephenlecomptejr
Flag 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
Microsoft Access

Avatar of undefined
Last Comment
stephenlecomptejr

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Rey Obrero (Capricorn1)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
stephenlecomptejr

ASKER
Always gracious for the help capricorn!
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck