I am trying to make a form which searches for the value inside all of the tables in the database (there are more than 1 table). The result will be displayed as the name of the table which this appears in. If someone can help me that will be nice.
In short, I have a form with a textbox and button. I enter the search string (for example 183939) and click on the button. It searches the value (183939) inside all the fields in the tables in the database, and if the value is found, then it displays the name of the table that it appears in. Thanks for the help.
I think this is a bad idea because it could take a very long time, and provide confusing results due to also searching system tables... but the following function will return an array of all table names containing the search term or nothing if it wasn't found. Calling example is such:
theTables = containingTable("hello") where theTables is a variant. A limitation is that this will fail for multi-valued fields.
Function containingTables(term As String) Dim db As Database Dim tds As TableDefs Dim td As TableDef Set db = CurrentDb Set tds = db.TableDefs For Each td In tds For Each f In td.Fields On Error Resume Next If DCount("[" & f.Name & "]", "[" & td.Name & "]", "[" & f.Name & "] LIKE '*" & term & "*'") Then If Err.Number <> 0 Then Debug.Print Err.Number, Err.Description Err.Clear On Error GoTo 0 Else containingTables = containingTables & td.Name & "," Exit For End If End If Next Next Set tds = Nothing Set db = Nothing 'Alternate Version if Len(containgingTables) then containingTables = Left(containingTables, Len(containingTables) - 1) 'Original Version 'if Len(containgingTables) then containingTables = Split(Left(containingTables, Len(containingTables) - 1), ",") End Function
To display the results with the alternate version, just use:
searchTerm is whatever you are searching.
Me as well i don't know why you would want to do something like that...
I think the solution posted by Daniel Cook is correct, i just took a slightly different approach. Do you need to match the exact value like I do? Anyway, here's my code:
Function searchTables(term as String) Dim T As TableDef Dim Rs As Recordset Dim Result() As String Dim Counter Counter = 0 For Each T In CurrentDb.TableDefs If (Left(T.Name, 4) <> "USys") And (T.Attributes = 0) Then Set Rs = T.OpenRecordset While Not Rs.EOF For Each Field In Rs.Fields If Rs(Field.Name) = term Then Counter = Counter + 1 ReDim Preserve Result(Counter) Result(Counter) = T.Name & "," & Field.Name End If Next Rs.MoveNext Wend Rs.Close End If Next If Counter = 0 Then searchTables = Null Else searchTables = Result End If End Function
You should filter out duplicated values, in case the function matches multiple times the same filed in the same table.