Want a Form to Search all Tables for text

  • Thread starter Thread starter TheBrenda
  • Start date Start date
T

TheBrenda

I am not looking for something to release to the client. This is a
programmers' tool. Looking for a form that will search for the selected text
across all tables in my Access database (or selected tables). I am tired of
looking for for something in multiple tables. Does anyone have such a tool
that they can share?
 
TheBrenda said:
I am not looking for something to release to the client. This is a
programmers' tool. Looking for a form that will search for the selected
text
across all tables in my Access database (or selected tables). I am tired
of
looking for for something in multiple tables. Does anyone have such a tool
that they can share?
Click to expand...


I find this in my "test and debug" module:

'------ start of code -------
Sub SearchForData(ValueSought As Variant)

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim intValueType As Integer
Dim intFld As Integer
Dim blnTestField As Boolean
Dim aintFieldsToCheck(255) As Integer
Dim intNFields As Integer
Dim I As Integer

intValueType = VarType(ValueSought)
Select Case intValueType
Case vbNull, vbEmpty, vbArray, vbError, vbObject, _
vbDataObject, vbUserDefinedType
Err.Raise 5, , "Invalid data type for search"
End Select

Debug.Print "*** Searching database for "; ValueSought; " ***"
Set db = CurrentDb

For Each tdf In db.TableDefs
If Not (tdf.Name Like "MSys*") Then

Set rs = tdf.OpenRecordset(dbOpenSnapshot)

If rs.EOF = False Then

' Before processing the actual records, make a list of
' all the fields in this recordset of the appropriate type.

intNFields = 0
For I = 0 To rs.Fields.Count - 1

blnTestField = False
Select Case intValueType
Case vbString
Select Case rs.Fields(I).Type
Case dbText, dbMemo, dbChar
blnTestField = True
End Select
Case vbDate
Select Case rs.Fields(I).Type
Case dbDate, dbTime
blnTestField = True
End Select
Case vbBoolean
Select Case rs.Fields(I).Type
Case dbBoolean
End Select
Case Else
Select Case rs.Fields(I).Type
Case dbLong, dbInteger, dbDouble, dbSingle,
dbCurrency, _
dbByte, dbFloat, dbDecimal, dbNumeric
blnTestField = True
End Select
End Select

If blnTestField Then
aintFieldsToCheck(intNFields) = I
intNFields = intNFields + 1
End If

Next I

' Were there any qualifying fields?
If intNFields > 0 Then

With rs
Do Until .EOF
For I = 0 To (intNFields - 1)

intFld = aintFieldsToCheck(I)
If .Fields(intFld).Value = ValueSought Then
Debug.Print tdf.Name; " : ";
For Each fld In rs.Fields
Debug.Print , fld.Name; "=";
fld.Value
Next fld
Debug.Print
End If
Next I
.MoveNext
Loop
End With

End If

End If

rs.Close
Set rs = Nothing

End If
Next tdf

Debug.Print "*** Search Complete ***"
Set db = Nothing

End Sub
'------ end of code -------

I don't even recall how well it works, or if it has been thoroughly tested,
but you're welcome to use it if it helps.
 
Sorry to be a dunce, but I am not an Access programmer. I created a simple
form with a button and a textbox. Then added this code to the button click. I
tried to build but got "user-defined type not defined" on Dim db As
DAO.Database.

Obviously I am not an Access programmer. This code solution may be more than
I can bite off.

Does anyone have another Search solution or know of one that I can download
as freeware, or even reasonable shareware?
 
TheBrenda said:
Sorry to be a dunce, but I am not an Access programmer. I created a simple
form with a button and a textbox. Then added this code to the button
click. I
tried to build but got "user-defined type not defined" on Dim db As
DAO.Database.
Click to expand...

You'll need to set a reference to DAO. If you're using Access 2007, go into
the VB Editor, click Tools -> References..., locate "Microsoft Office 12.0
Access database engine Object Library" (I think) in the list, and put a
check mark in the box next to it. If you're using Access 2000-2003, the
reference to check is "Microsoft DAO 3.6 Object Library".

That said, the code I gave you is not set up to display its results on a
form. It's just there to be called from the Immediate Window, and displays
its results there. If you want to use it without extensive modifications,
put the code into a new standard module, not a form module. Then, to search
for something, you would enter something like this in the Immediate Window:

SearchForData "The String You're Looking For"

and see the results there.
Does anyone have another Search solution or know of one that I can
download
as freeware, or even reasonable shareware?
Click to expand...

There are commercial products like SpeedFerret and FindAndReplace. They do
a *lot* more than you're asking, and I don't think they are all that
expensive.
 
OK, got it running. thanks. But would like to look for a partial match. This
is the line that looks for match.

If .Fields(intFld).Value = ValueSought Then

I have already done a UCase(ValueSought) if it was a string. Is there a
problem doing a UCase on non-string types? I changed the above line to the
following.

If InStr(UCase(.Fields(intFld).Value), ValueSought) > 0 Then

Thanks for your help. It works great. When I get some energy I will put it
to a form.
 
Back
Top