Search for value in all tables 1

[RickBerem]

Hi,

I'm looking to build a little tool that would allow me to find a value in all the tables of any database.
I've done so using Tabledefs, Records and Fields. But it's slow. I'd like to know if there's a faster way to do it or if I have to endure the long process (Well it's quite long on big tables)

Thanks for any help! SG

 

[RickSpr]

I think it would be more practical to try to find ways to speed up the code you've already got. Is the database on the local PC, or can you copy it there, so there are no network delays? Did you open your tables with the dbDenyRead option so you won't have to lock each row or page? How about the dbForwardOnly option, with table-type recordsets?

Could your code be tightened up? Are you using With statements so you don't have to search through DAO object chains unnecessarily? Are you scanning text fields character by character, or using the faster Like operator? Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[MichaelRed]

While the question appears to request help in making your process somewhat more efficient, the rather brief statement of the actual procedure set does not provide any opportunity to review / test / improve the processes themselves. Posting the actual code would provide this opportunity.

You also not that you want to " ... find a value in all the tables of any database.
... , but this implies that you are also searching across multiple databases, or is the search to be selective within a specific (named) database at run time. Along these lines, is the db 'type' restricted to ".MDB" format using ONLY Jet dbengine, or do you expect to also review other object types (".XLS", ".CSV", "SQL", ... ) and - if so, are these selected at run time, or just 'whatever' or dependedent on other factors (e.g. 'named' databases)?

What "information" do you expect to be returned from a "match"?

Does the Search stop with the first occurance of a match, of is it supposed to find all occurances?

Perhaps this BRIEF list of issues will illustrate the need for many additional details of your inquiry -many/most of which could be implicit in the existing procedures.


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[RickBerem]

Hi,

Maybe my post was a little short I'll try to fix that.
What I meant by ANY database is that my user will select the MDB he wants to search from(Local or Network). The search only look for the exact match in the fields and does not stop after the first occurance. What I expect to be returned from a match is table name and field name.(Written in a table in the current db)

Here's my code:

Dim iTable As Integer 'Table counter
Dim iField As Integer 'Field counter
Dim lRecord As Long 'Record counter
Dim rsSearch As Recordset 'Recordset object
Dim rsWrite As Recordset 'Recordset object
Dim sTable As String 'Table Name
Dim vTemp As Variant
   
'Sets recordset object to be able to write results
Set rsWrite = CurrentDb.OpenRecordset("SELECT * FROM tblResults")
'Sets the progress bar
vTemp = SysCmd(acSysCmdInitMeter, "Fetching...", db.TableDefs.Count)
'For each tables
For iTable = 0 To db.TableDefs.Count - 1
   'If table is not a System table
   If Not db.TableDefs(iTable).Name Like "MSys*" Then
      'Copies table name
      sTable = db.TableDefs(iTable).Name
      'Fills recordset object with all records of the table
      Set rsSearch = db.OpenRecordset("SELECT * FROM [" & sTable & "];")
      'If there's records in the table
      If rsSearch.RecordCount <> 0 Then
         rsSearch.MoveLast 'Cycle throught records
         rsSearch.MoveFirst
         'for each record
         For lRecord = 0 To rsSearch.RecordCount - 1
               'For each field in the record
               For iField = 0 To rsSearch.Fields.Count - 1
               'Shows progress of work
               Me.lblWorkInProgress.Caption = sTable & &quot;.&quot; & rsSearch.Fields(iField).Name
               Me.Repaint 'Repaint form
               'If current value equals the Fetch value then
               If rsSearch(iField).Value = Me.txtFetchValue Then
                  rsWrite.AddNew 'Creates new record
                  rsWrite(&quot;Table&quot;) = sTable 
                  rsWrite(&quot;Field&quot;) = rsSearch.Fields(iField).Name
                  rsWrite.Update 'Saves record
               End If
            Next iField
            rsSearch.MoveNext 'goes to next record
         Next lRecord
      End If
   End If
   'Increment progress bar
   vTemp = SysCmd(acSysCmdUpdateMeter, iTable + 1)
   Me.lstResults.Requery 'Refreshes data in data listbox
   DoEvents 'Let processor do other stuff
Next iTable
vTemp = SysCmd(acSysCmdRemoveMeter) 'Kills progress bar
rsSearch.Close 'Close recordset object
rsWrite.Close 'Close recordset object

Thanks for your help
SG

 

[MichaelRed]

see thread705-503186 There are two routines posted therein. I believe that the one I posted (with some enhancement) will do the finding. You will note the critique. I believe that some of it is presented without really looking at the 'routine', but I will respond to parts of it.

1.[tab]I fully intended to do the complete search. I did miss checkout's mention of the specific record. For your purpose, I believe that the procedure I posted is more appropiate.

2.[tab]I do not think this is relevant. Wheather the 'original' recordset is closed or not will have little effect on the overall process given the procedure's outline.

3.[tab]The actual construction of the clause in all but one of the 'cases' is commented out. They were not intended to be completed (at least not by me), but to show the type of operation which MIGHT be useful to the (other) users. Different processes MAY want to explicitly handle various field types in different manners. This is wht the comment reference to "DataTypeEnum" is included with the Select Case Statement). I totally miss his point re the "equal sign" at the bottom of this item.

4.[tab]Yes. This will open a new / seperate RECORDSET for each field. Restricted to the individual Record, it is un-necessary. For searching an Entire table, it is a way to find the appropiate records (those which DO include the value).

5.[tab]A small sloppiness=. The entire set of local variables will go out-of-scope at the conclusion of the procedure, so the array will not exist when the process is complete.

In other areas, your requirement is somewhat different, so additional items need to be considered.

A.[tab]The procedure relies on and expects to search a specific table in the CURRENT db. You would need to expand the procedure to Include the db path\name and open THAT db as an additional db to Search. An alternative could be to have a calling routine to open the db and pass the reference to the procedure.

B.[tab]The procedure ecpects to receieve a table name to search. This, like the db (see Item "A&quot needs to be revised in some manner. If (in Item "A", you choose to open the db seperatly and pass the reference to this procedure, then passing the table name can reasonably remain as it is currently). If the procedure is modified to accept the db name and expects to open the db itself, there needs to be a mechanisim to determine which 'tables' to open (more on this later)

C.[tab]The procedure returns a string which is simply the field name. This is possibly inadequate, but depends on how you would resolve "A" & "B". In general, I would suggest that an entirely seperate procedure be generated to do the actual "posting" of the record to your results table. The (modified) procedure would pass the elements to the posting procedure. Presumably, this would include hte db (path & Name), the table and the field. In some manner, you will generally want to also be able to include the 'value'. You will want to assure that the recordset which holds the reclts of your search is either cleared of previous results or the records include an explicit indiciator of the search (value searched for and date/time of search?).

D.[tab]In the process of finding the tables within the db being searched, the taabledefs collection is not the approach I would recommend. Look into the MSysObjects (system) table. It includes ALL objects in the database and may easily be referenced in a query to select the tables and exclude the MSys* objects. This would provide a convenient "recordset" to loop through (replacing your itable = 0 to ... with a While Not rst.Eof type). The loop construct is not that important, but the process of looping through the collection as opposed to the simply query / recordset may be.

E.[tab]the porcedure I posted (ref Thread). Completly exits after a first match is found. From your commentary, you would want it to simply continue processing (skip to the next field / table).

I am sure that additional issues will intrude, but the referenced thrad and these comments should get you several steps closer.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[RickBerem]

Hi,

thanks MichaelRed!

Using ideas from your code it's now lightning fast!

Thanks a lot!


SG