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