Search utility

[Arsene Lupin]

Hello, everyone,

I am trying to develop a custom search utility, which will search for
a string in all text and memo fields of one table, and record all
instances where it found it in a second table. A little more detail
follows:

Have one table (let's call it table # 1), with an unknown number of
fields, of which several may be text and/or memo fields, I would like
to loop through every record, from BOF to EOF, and, in each record,
search for the occurrence of a user-provided search string in all text
and memo fields. The search string could be found as the sole content
of one of the fields or as part of the contents of a field.

For every record in table # 1 where the search string is found, I
would like to create a record in a second table (let's call it table #
2, which starts out empty), this new record appended to table # 2 will
only contain the record number on table # 1 where the string was
found. By the time table # 1 reaches EOF, table # 2 should have one
record for each time the string was found in table # 1.

Any ideas or even better, code, would be greatly appreciated,

Thanks

Arsene

 

[John W. Vinson]

Hello, everyone,

I am trying to develop a custom search utility, which will search for
a string in all text and memo fields of one table, and record all
instances where it found it in a second table. A little more detail
follows:

Have one table (let's call it table # 1), with an unknown number of
fields, of which several may be text and/or memo fields, I would like
to loop through every record, from BOF to EOF, and, in each record,
search for the occurrence of a user-provided search string in all text
and memo fields. The search string could be found as the sole content
of one of the fields or as part of the contents of a field.

For every record in table # 1 where the search string is found, I
would like to create a record in a second table (let's call it table #
2, which starts out empty), this new record appended to table # 2 will
only contain the record number on table # 1 where the string was
found. By the time table # 1 reaches EOF, table # 2 should have one
record for each time the string was found in table # 1.

Any ideas or even better, code, would be greatly appreciated,

Thanks

Arsene

One big red flag here is that Access tables don't HAVE (usable or meaningful)
"record numbers". A Table is an unordered "bucket" of data. It's not even
required that a table have a Primary Key (it'd be foolish to include such
tables in a database except in unusual circumstances, but there are certainly
plenty of foolish programmmers...). It's bad design, bad programming, and
probably not going to be useful, but I'll assume you want the AbsolutePosition
in the recordset; do be aware that this value is volatile, unreproducible, and
will change if you (say) add or delete a record anywhere in the table, or
compact the database. I'd assume you also want to know which FIELD in the
table had the string, if not omit that part of the code.

Some untested "air code":

Dim db As DAO.Database
Dim fld AS DAO.Field
Dim rsIn As DAO.Recordset
Dim rsOut As DAO.Recordset
Set db = CurrentDb ' or use a reference to some other database
Set rsOut = db.OpenRecordset("Table2", dbOpenDynaset)
Set rsIn = db.OpenRecordset("Table1", dbOpenSnapshot) ' read only and faster
Do Until rsIn.EOF
For Each fld IN rsIn.Fields
If fld.Type = dbText OR fld.Type = dbMemo Then ' text, memo only
If InStr(fld.Value,strFindThis)>0 Then
rsOut.AddNew
rsOut!Fieldname = fld.Name
rsOut!RecordNumber = rsIn!AbsolutePosition
rsOut.Update
Next fld
rsIn.MoveNext
Loop
rsIn.Close
rsOut.Close
Set rsIn = Nothing
Set rsOut = Nothing

If you want to store the value of the Primary Key, you'll need to extract that
(with some difficulty) from the table's field and index definitions; be aware
that a primary key is not the same as an Autonumber, can be of any datatype,
and can consist of one, two, or even ten fields.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Arsene Lupin]

John,

Cannot thank you enough for the code, this is exactly what I was
looking for. Will be implementing it into my app.

Thanks !!!