Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / General 1 / February 2006

Tip: Looking for answers? Try searching our database.

Global Search over Multiple Databases

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
RoadRunner - 09 Feb 2006 17:11 GMT
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
Benny Andersen - 09 Feb 2006 20:39 GMT
> 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

David W. Fenton - 10 Feb 2006 00:15 GMT
> 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/

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.