Hi, I have a question. I am asked to produce a global search of a
given corporate name, in more that one database. Each database has
different table names and different field names in the tables. Does
anyone know if this can be done? Thanks
> Hi, I have a question. I am asked to produce a global search of a
> given corporate name, in more that one database. Each database has
> different table names and different field names in the tables. Does
> anyone know if this can be done? Thanks
programmatically it is possible to search every relevant field of any
table, but i have no knowledge of any easy way to do that.

Signature
Regards
Benny Andersen
> I have a question. I am asked to produce a global search of a
> given corporate name, in more that one database. Each database
> has different table names and different field names in the tables.
> Does anyone know if this can be done?
Is the built-in Windows search for files containing a certain text
string not sufficient? You can limit to a particular file type, and
search subfolders. So, if you needed to find all the MDBs with that
company name in them on a particular server, you'd start at the
highest possible share level (if it's set up right, there ought to
be one top-level data share).
If that's not sufficient, you'd have to somehow get a list of all
the MDBs and then you could easily program searching all fields in
all tables in those MDBs.
Just sketching out the structure of this:
Loop 1 Start
fetches an MDB name/path from the list and opens it
Loop 2 Start
fetches the first table in the MDB from the TableDefs
collection
If NOT linked table then
Loop 3 Start
loop the fields collection of each TableDef
and construct a SQL string for each field that
tests WHERE field Like "*searchtext*" and open a
recordset with that SQL
If match found Then
record this MDB's name and table/field name AND
exit Loop 3 OR
record MDB name and table/field name AND
go to next field
Loop 3 End
Else Next TableDef
Loop 2 End
Loop 1 End
There are many questions that would need to be answered, but that's
the basic structure of what you'd need to do:
1. get the list of MDBs (either in Access or from outside Access)
2. open each MDB in turn
3. walk the TableDefs collection
4. walk the Fields collectin of each TableDef
5. if there's a match record as much information as you need:
a. MDB name only
b. MDB name and table
c. MDB name, table and field
d. MDB name, table, field and count of matches
e. MDB name, table, field, count of matches and PKs of matching
records
That pretty much covers it so far as I can see.

Signature
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
RoadRunner - 14 Feb 2006 14:28 GMT
I need to put the results into a access database. Also they need an
access menu front end.
David W. Fenton - 14 Feb 2006 21:04 GMT
> I need to put the results into a access database. Also they need
> an access menu front end.
That's nice. What's your question?

Signature
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/