WildCard Quering of a Database using a RecordSet

[robboll]

I am working on a routine where if you put in any value, it looks
through all the tables of any database for that value. Currently, I am
at the point where a table is created with three columns.

Here is an example of the table that is generated:

Table Name: SearchData

TableName FieldName SearchValue
Table1 Field1 alpha
Table1 Field1 alpha
Table1 Field9 alpha
Table5 Field3 alpha
Table5 Field8 alpha

In this case the word "alpha" is the search value and it generates a
table with all the tables and fields where "alpha" is found.

The next step is to create a routine that uses the values of TableName,
FieldName, and SearchValue to return a wildcard search of each table in
the recordset.

I am sure there is a better approach to this, but the code that has
evolved so far skips through the entire datasource and generates only
the first query from the first record.

What I'd like it to do is after it creates TEMPxyz that it allows the
user to review the data, and after reviewing it, that it either
automatically creates a new record and displays it, or allows the user
to click a next command button to do the same.

This brings up a related question: Is it necessary to generate a new
table with each row of the source record set -- or can this done using
a different method being used. Seems that constant
creating/overwriting of TEMPxyz would cause a lot of fragmentation of
the database.

Thanks for any help with this,

RBollinger


Function queryResults()
On Error GoTo Err_Line

Dim db As DAO.Database
Dim rsXYZResults As DAO.Recordset
Dim mTable As String
Dim mField As String
Dim strSQL As String

Set db = CurrentDb
'Open the Table/Fields table
Set rsXYZResults = db.OpenRecordset("SearchData", dbOpenSnapshot)
With rsXYZResults
.MoveFirst
Do Until .EOF
mTable = Trim(.Fields("TableName"))
mField = Trim(.Fields("FieldName"))
mvalue = Trim(.Fields("SearchValue"))

strSQL = "SELECT " & mTable & ".*" & _
" INTO TEMPxyz" & _
" From " & mTable & _
" WHERE ((( " & mTable & "." & mField & ")" & _
" Like " & "'" & "*" & mvalue & "*" & "'" & "))"

db.Execute strSQL, dbFailOnError

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryTEMPxyz", acViewNormal, acEdit
DoCmd.SetWarnings True
.MoveNext
Loop
End With
rsXYZFields.Close
Set rsXYZFields = Nothing
db.Close
Set db = Nothing
Exit Function

Err_Line:
'MsgBox "Error occurred"
Resume Next
End Function

 

[John Nurick]

I seem to remember from previous threads that you are trying to identify
the *columns* in which SearchValue appears and not the individual
records.

In that case you don't need to go creating tables. Modify your
"SearchData" table so it has a record for each column that you want to
worth search (presumably these are text or memo type), plus a boolean
field:

TableName FieldName ValueFound
Table1 Field1
Table1 Field8
Table1 Field9
Table1 Field3
Table1 Field8
...

Then do something like this air code:
...
Dim strSearchValue As String
Dim strSQL As String
...
strSearchValue = "alpha"
Set rsXYZResults = db.OpenRecordset("SearchData", dbOpenSnapshot)

'Clear the ValueFound field in SearchData
strSQL = "UPDATE SearchData SET ValueFound = FALSE;"
db.Execute strSQL, dbFailOnError

With rsXYZResults
Do Until .EOF
mTable = Trim(.Fields("TableName"))
mField = Trim(.Fields("FieldName"))

If DCount(mField, mTable, _
mField & "='" & strSearchValue & "'") > 0 Then
'Search value exists in this column
'Mark the corresponding record in SearchData
strSQL = "UPDATE SearchData SET ValueFound = TRUE " _
& "WHERE (TableName = '" & mTable & "') AND " _
& "(FieldName = '" & mField & "');"
db.Execute strSQL, dbFailOnError
End If

.MoveNext
Loop
End With

...

Then use a query like
SELECT TableName, FieldName FROM SearchData
WHERE ValueFound
ORDER BY TableName, FieldName
to retrieve the list of tables and columns that contain the search
value.

 

[robboll]

Yes, initially I was only interested in identifying the tables as
associated columns where the search value was located. Ken Snell
helped me produce the code that did just that. I am trying to take it
a step further. The new code needs to execute a query based on the
results of the previous procedure (i.e., values for each TableName,
FieldName, SearchValue). And when you close the query for any given
record, it automatically runs the next row/query (or the user has the
opportunity to click a "next" command button to see the results).

Table Name: SearchData

TableName FieldName ValueFound
Table1 Field1 Widget
Table1 Field8 Widget
Table1 Field9 Widget
Table1 Field3 Widget
Table1 Field8 Widget

So . . . given the above record set.

The procedures should run an individual "wildcard" query for each row.
(i.e., Executing a query based on Table1, Field1 and "Widget" then
looking for
Table1, Field8 and "Widget", etc., etc.)

When the user clicks next, the routine should close the open query,
jump to the next record, and execute again -- until the end of the
file.

I hope explained it better this time.

RBollinger

 

[John Nurick]

Presumably you want the user to be able to examine the records found.
You don't need to use a make-table query for that; an ordinary select
query will do. But the tricky bit will be setting up a form that can
display records from virtually any table.

 

[robboll]

If I don't use a make-table query, what is the basic code to display
the query? When I use . . .

strSQL = "SELECT " & mTable & ".*" & _
" From " & mTable & _
" WHERE ((( " & mTable & "." & mField & ")" & _
" Like " & "'" & "*" & mvalue & "*" & "'" & "))"

db.Execute strSQL, dbFailOnError

.... the db.Execute strSQL doesn't execute the query.

 

[John Nurick]

db.Execute is for use with action queries.

If you just want to display the results of the SELECT statement in a
datasheet, create a query in the usual way (let's call it qryShowFound).
It doesn't matter what it does because we'll be assigning a new SQL
statement every time we use it, so make it a simple SELECT query on one
of your tables. Then do something like this:

strSQL = "SELECT " & mTable ....
db.QueryDefs("qryShowFound").SQL = strSQL
doCmd.OpenQuery "qryShowFound"

If I don't use a make-table query, what is the basic code to display
the query? When I use . . .

strSQL = "SELECT " & mTable & ".*" & _
" From " & mTable & _
" WHERE ((( " & mTable & "." & mField & ")" & _
" Like " & "'" & "*" & mvalue & "*" & "'" & "))"

db.Execute strSQL, dbFailOnError

... the db.Execute strSQL doesn't execute the query.