Searching entire database for string

[ischwartz88]

Hi, I am not a computer programmer. I have been learning VBA along the way of making this script. I know Access and SQL.

I have a very large database with many tables and queries. My goal is to write a script or function that will search the entire database for a certain string (actually two strings).

I have tried using a series of queries and unioning them together but this is a very scrappy way of doing this. Perhaps there is a method of automatically searching every field in every table for a string? I'm sure this has been done before but I have been searching the internet for about a week and have come up with nothing, so here I am, asking for help.

Any input is GREATLY appreciated; I am getting flustered due to my lack of knowledge with VBA!

Thanks!
Ian

[ADezii]

Just subscribing, when I get a chance I'll see what I can come up with.

[Delerna]

Hi, this is an extract from some code I had in an Access form
It should get you started

Private Sub cmdSearchTheTables_Click()
    Dim DB As dao.Database
    Dim tbl As dao.TableDef
    Dim fld As dao.Field
 
    Set DB = CurrentDb
    For Each tbl In DB.TableDefs
        For Each fld In tbl.Fields
            If fld.Value = "Search string" Then MsgBox "Found it" 
        Next
    Next
    Set tbl = Nothing
    Set fld = Nothing
    Set DB = Nothing
End Sub
 

you will of course need a reference to "Microsoft DAO 3.6 object library"

[ischwartz88]

Ah! The code makes sense to me, and I have DAO 3.6 installed and enabled.

I have attached the code to a button called cmdSearchTheTables.

However, it says the IF statement

If fld.Value Like "*MK 5*" Then

is an invalid operation.

I tried a few different syntax forms for the line, but it still returned the same error.

Am I missing something? Do I need to assign a text field to output the result into?

[ischwartz88]

OK. I have added much to the Sub. My code now reads as:

 
Public Sub GenerateFieldSizeInfo()
    Dim cdb As database
    Dim oTable As TableDef
    Dim oField As field
    Dim rsTable As Recordset
    Dim strSQLTable As String
    Dim iMaxSize As Integer
    Dim iHeadroom As Integer
    Dim iFileNumber As Integer
    Dim strOutputFile As String
 
    Dim ddb As database
    Dim dtbl As TableDef
    'Dim dfld As field
 
    'Create the header file. (If the file already exists, its contents are overwritten.)
    iFileNumber = 1
    strOutputFile = "C:\etc,etc."
 
    Open strOutputFile For Output As #iFileNumber
    'Open the NWSS database.
    Set cdb = CurrentDb
 
    Print #iFileNumber, " Table"; Tab(40); "Field"; Tab(70); "Relevant Text"; Tab(101); "Size"; Tab(111); "Max"; Tab(117); "Headroom"
    Print #iFileNumber, String(125, "-")
    For Each oTable In cdb.TableDefs
      'If (StrComp("tbl", Left$(oTable.Name, 3)) = 0) Then
        For Each oField In oTable.Fields
          If (oField.Type = 10) Then
            Print #iFileNumber, " "; oTable.Name;
            Print #iFileNumber, Tab(40); oField.Name; Tab(104 - Len(Str(oField.Size))); oField.Size;
            strSQLTable = "SELECT " + "[" + oField.Name + "]"
            strSQLTable = strSQLTable + " FROM " + "[" + oTable.Name + "]"
            strSQLTable = strSQLTable + " ORDER BY " + "[" + oField.Name + "]"
            Set rsTable = cdb.OpenRecordset(strSQLTable, dbOpenDynaset)
            If Not rsTable.EOF Then
              rsTable.MoveFirst
            End If
            Do Until rsTable.EOF
              If oField.Value Like "*Search text*" Then
                Print #iFileNumber, Tab(70 - Len(Str(oField))); oField
              End If
              rsTable.MoveNext
            Loop
            iMaxSize = 0
            Do Until rsTable.EOF
              If (Len(rsTable.Fields(0).Value) > iMaxSize) Then
                iMaxSize = Len(rsTable.Fields(0).Value)
              End If
              rsTable.MoveNext
            Loop
            iHeadroom = oField.Size - iMaxSize
            Print #iFileNumber, Tab(114 - Len(Str(iMaxSize))); iMaxSize;
            Print #iFileNumber, Tab(124 - Len(Str(iHeadroom))); iHeadroom
            rsTable.Close
          End If
        Next
          Print #iFileNumber, ""
      'End If
    Next
    Close #iFileNumber
End Sub
 

I am still getting the "run-time error '3219', invalid operation" message when I try to run the script. It pertains to the IF statement in line 40. Anyone have any suggestions? I am puzzled because this should work!

[missinglinq]

Just out of curiosity's sake, in what real-world situation would you need to search every field of every table for a given search string?

Welcome to Bytes!

Linq ;0)>

[ischwartz88]

Just out of curiosity's sake, in what real-world situation would you need to search every field of every table for a given search string?

Welcome to Bytes!

Linq ;0)>

This is a task given to me for my job. It is to find data sensitive to a certain location. The script will be able to be used on any database made in the future; henceforth, creating a script to search every field is the most efficient way to go about this.

After the script is fully functional on VBA, I plan to translate it into VB and have it read a given database which is inputted by the user.

[Delerna]

hmmmm
the function LIKE compares two strings.
Maybe its having a problem with one of them being a field object.
Try

Dim str as string
str=fld.value
if str like "*MK 5*" Then
 

[ADezii]

I've written an Algorithm for you that will search every Field in every Table for a Search String that you specify. System Tables, those prefaced with 'MSys' or 'USys', are ignored. If a match(es) are found, the Record Number, Table Name, Field Name, and the actual Field Value containing the String are written to the Immediate Window. The Function will return True if successful, otherwise False. The code is very flexible in that the results can easily be saved to a Table, written to a List Box, printed to a Text File, etc. with little modification. You can simply call the Function within an Expression passing it the required Search String, then test for success. I did my testing on all the Tables of the Northwind Database, looking for the String 'Seafood'. For testing purposes, I added this String to several Fields, in multiple Tables. The Function Call and subsequent results are posted below:

Public Function fSearchForString(strSearchString) As Boolean
On Error GoTo Err_fSearchForString
 
Dim tdf As DAO.TableDef
Dim MyDB As Database
Dim MyRS As Recordset
Dim intNumOfFields As Integer
Dim intCounter As Integer
 
'Let's not get ridiculous, need at least 4 Characters
If Len(strSearchString) <= 3 Then Exit Function
 
Set MyDB = CurrentDb
 
DoCmd.Hourglass True
 
Debug.Print "The String [" & strSearchString & "] has been found in the following locations:"
Debug.Print "******************************************************************************************"
Debug.Print
 
For Each tdf In CurrentDb.TableDefs
  If Not tdf.Name Like "MSys*" Or Not tdf.Name Like "MSys*" Then    'ignore System Tables
    Set MyRS = MyDB.OpenRecordset(tdf.Name, dbOpenDynaset)
      intNumOfFields = MyRS.Fields.Count
        For intCounter = 0 To intNumOfFields - 1
          Do While Not MyRS.EOF
            If InStr(MyRS.Fields(intCounter).Value, strSearchString) > 0 Then
              Debug.Print "Rec Num: " & MyRS.AbsolutePosition + 1 & " | " & _
                          "Table: " & tdf.Name & " | " & "Field: " & _
                           MyRS.Fields(intCounter).Name & " | Value: " & _
                           MyRS.Fields(intCounter).Value
            End If
            MyRS.MoveNext
          Loop
          MyRS.MoveFirst
        Next
  End If
Next
 
DoCmd.Hourglass False
 
MyRS.Close
Set MyRS = Nothing
 
fSearchForString = True
 
Exit_fSearchForString:
  Exit Function
 
Err_fSearchForString:
  fSearchForString = False
  MsgBox Err.Description, vbExclamation, "Error in fSearchForString()"
  DoCmd.Hourglass False
  If Not MyRS Is Nothing Then
    MyRS.Close
    Set MyRS = Nothing
  End If
    Resume Exit_fSearchForString
End Function

Call to Function:

If fSearchForString("Seafood") Then
  MsgBox "Search  for String a success!", vbExclamation, "Success"
Else
  MsgBox "What in the world have you done, ADezii!", vbCritical, "Failure"
End If

Sample OUTPUT:

The String [Seafood] has been found in the following locations:
******************************************************************************************
 
Rec Num: 8 | Table: Categories | Field: CategoryName | Value: Seafood
Rec Num: 12 | Table: Employees | Field: LastName | Value: Seafood
Rec Num: 5 | Table: Employees | Field: Title | Value: Sales Seafood Manager
Rec Num: 1 | Table: Products | Field: ProductName | Value: Chai Seafood
Rec Num: 23 | Table: Products | Field: ProductName | Value: Tunnbröd Seafood
Rec Num: 37 | Table: Products | Field: ProductName | Value: Seafood Gravad lax
Rec Num: 4 | Table: Shippers | Field: CompanyName | Value: Seafood Express
Rec Num: 19 | Table: Suppliers | Field: CompanyName | Value: New England Seafood Cannery

[ischwartz88]

Perfect! Thank you very much. I played around with the code for a bit and I ended up combining my script with yours, and it works perfectly!