Global Search 2

[aw23]

I need to create a global search where the user can type in a word or phrase and it searches all my records in all my tables. What is the easiest way to do this?

Thanks

 

[Frink]

Hallo,

You need to do it in code. Write a loop to loop through all the tables in the database.
Inside that loop through all the records in each table.
Inside that loop through all the fields in each record.

Simple eh?

Exactly how you do this depends on your version of MS Access, but it's all in the help. Lookup help on Tabledefs, recordset and fields, if you're not sure.

- Frink

 

[aw23]

Thanks, I was hoping there'd be an easier way! I have lots of tables with lots of records and lots of fields!

 

[Frink]

Hallo,

It's less than 10 lines of code. Have you looked in the help?

- Frink

 

[aw23]

I'm sorry, I'm a little confused. What is a tabledef?

Thanks

 

[Frink]

Hallo,

It depends on which version of Access you are using, but in Access there are Objects, and Collections of Objects.
A TableDef is a table in your database, and is a member of the CurrentDB.TableDefs collection.
Lookup Collections in the help for some enlightenment!

Have a read as I don't want to reproduce those facts here.

public Sub SearchAll(ByVal pstrSearch As String)
  Dim dbCurrent As Database
  Dim tdfTable As TableDef
  Dim rstRecords As dao.Recordset
  Dim fldField As Field
  Dim lngRecordNumber As Long
  Set dbCurrent = CurrentDb
  Debug.Print "Searching for " & pstrSearch & " in all Tables and Fields"
  Debug.Print "TableName!FieldName(RecordNumber)=FieldContents"
  For Each tdfTable In CurrentDb.TableDefs
    Set rstRecords = dbCurrent.OpenRecordset(tdfTable.Name, dbOpenForwardOnly)
    lngRecordNumber = 0
    Do While Not rstRecords.EOF
      For Each fldField In tdfTable.Fields
        If "" & rstRecords(fldField.Name) Like pstrSearch Then
          Debug.Print tdfTable.Name & "!" & fldField.Name & "(" & lngRecordNumber & ")=" & rstRecords(fldField.Name)
        End If
      Next fldField
      lngRecordNumber = lngRecordNumber + 1
      rstRecords.MoveNext
    Loop
  Next tdfTable
End Sub

Should output to the immediate window any matching records.
I don't know how you wanted the matches displayed, but this should give you the idea of how it's done.

- Frink

 

[aw23]

Thanks. Sorry I have not had a chance to work on this yet, have been busy with other things.
I will try it now but please tell me what you think of this, if it makes sense. I have three textboxes for the search where the user can enter up to 3 criteria. So when I use the global search, it can have other criteria as well. For example search for the word 'The' anywhere in the database but only in those clients located in Germany. I was thinking like this. Maybe do the other search first, find all clients in Germany and then do the golbal search with tabledefs on that rs. I will try this now, please let me know what you think.
Thanks!

 

[aw23]

Ok, I just tried running the code and I got an error:
'Type Mismatch'
on the following line:
For Each fldField In tdfTable.Fields
How can that be a type mismatch?
Thanks

 

[PHV]

Replace this:
Dim fldField As Field
By this:
Dim fldField As DAO.Field

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[aw23]

Thanks that worked. I am getting an error though for the following table which is that the table cannot be read, no reading permissions.
MSysACEs
This must be some sort of an internal table as I do not see it on the list of tables and I did not create it. Can I exclude it from the search? I don't need it. Is there any way I can say

if tdftable.name then
skip this one and continue in search

 

[PHV]

For Each tdfTable In CurrentDb.TableDefs
If UCase(Left(tdfTable.Name, 4)) <> "MSYS" Then
Set rstRecords = dbCurrent.OpenRecordset(tdfTable.Name, dbOpenForwardOnly)
...
End If
Next tdfTable

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Frink]

Hallo,

Happy New Year, and all that.

Cheers PHV for your tweaks. I still stuck using DAO, I'm afraid (ADO seems to be quite awkward, IMHO)

I'd prefer:
If Not (tblTableDef.Attributes And dbSystemObject) Then
rather than
If UCase(Left(tdfTable.Name, 4)) <> "MSYS" Then

but your way will work fine (until MS change their system table naming)

- Frink

 

[aw23]

Thanks
Now, I want to search for any field containing a string, not necessarily is the field the whole string.
I tried the following
pstrSearch = "%" & pstrSearch & "%"
which did not work, and I tried
If "" & rstRecords(fldField.name) Like "%" & pstrSearch & "%" Then


which also did not work. How can I do that?
Thanks

 

[Frink]

Hallo,

I really think that it would be better for your understanding of VB to look up this sort of thing in the help files rather than getting spoon-fed answers to each problem you find.

Try help on 'Like' or 'Wildcards' to get more information.

But because I don't mean to be harsh:
If Nz(rstRecords(fldField.name),"") Like "*" & pstrSearch & "*" Then

You'll also have to not allow pstrSearch to be an empty string.

- Frink

 

[JohnCHolmes]

Frink,

I like your posts, communication clarity and attitude. I think you might like to look at this one too.

http://www.mrexcel.com/board2/viewtopic.php?t=109378&highlight=

However it relies on Microsoft's find (essentially a Ctrl-F, I believe) and is consequently slower, just the way Mikeysoft loves to do things. You might see a couple of ideas there though (option to skip huge tables, e.g.)

As to your own, a line like
If 10000 * (lngRecordNumber \ 10000) = lngRecordNumber Then Debug.Print lngRecordNumber
might be likable, or
Debug.Print "beginning " & tdfTable.Name
though they'd break up the symmetry of your Immediate Window, as you've currently rigged it.

Good job on your own effort here.