Search trough IP table 1

[007700]

Hi guys,

I am looking for some good help. What a would like to do is create a search function that will look through one entire tabel filled with IP adresses. This table exisit out of multiple columns.
I would like the search to go trough the entire table and in the end list me all the values found.

I already tried it via a query, but I could that one only working for one column. I also tried the follwing module but could get it to do what I would like either.


Option Compare Database

Sub FullLIKESearch()
Dim rs As Recordset, tdf As TableDef, fld As Field, strSearch As String, iResp As Integer
'Search EVERY TABLE, EVERY FIELD in a database for a text string (substring, really)
strSearch = "0" 'this will be parameterized in the sub definition
For Each tdf In CurrentDb.TableDefs
If Left(tdf.Name, 4) = "MSys" Then GoTo lblNextTable
On Error GoTo errFullSearch1
Set rs = CurrentDb.OpenRecordset(tdf.Name, dbOpenDynaset)
On Error GoTo 0
rs.MoveLast
If rs.RecordCount * rs.Fields.Count > CLng(300000) Then
iResp = MsgBox("Okay to do " & rs.Fields.Count & " fields for " _
& rs.RecordCount & " records in " & tdf.Name & " ?", vbYesNo)
If iResp <> vbYes Then Debug.Print "skipped " & tdf.Name: GoTo lblNextTable
End If
'Debug.Print tdf.Name
For Each fld In rs.Fields
'Debug.Print "field " & fld.Name
rs.FindFirst "[" & fld.Name & "] LIKE " & "'*" & strSearch & "*'"
'rs.FindFirst "[" & fld.Name & "] = " & "'*" & strSearch & "*'"
If Not rs.NoMatch Then Debug.Print tdf.Name, fld.Name, strSearch: GoTo lblNextTable
Next fld
lblNextTable:
Next tdf
Debug.Print "DONE"
rs.Close: Set rs = Nothing
Exit Sub
errFullSearch1:
Debug.Print "problem opening " & tdf.Name
Resume lblNextTable
End Sub




Any help would be greatly appreciated...

Thanks in advance

Kindest regards

Henri van den Heuvel

 

[HitechUser]

Henri,

I am unclear as to why you could not do this with a query??? Just use the OR on each column in the table.

select * from tblIPaddress where clmOne LIKE .... or clmTwo Like ...

Help us here????

 

[007700]

I mean I only could get the search to go trough one column.
Iff it could be done via query much the better......

What I did was I put the following rule in the criteria field.

Like '*' & [What's the IP ?] & "*"

This enabled me to look trough one column for a peace of an ip number.
What you are saying is that with the LIKE or OR statement I could also let it look into more colums ??


How would this look like ?

I have got a table with the following collumn names:
Column 1 | Column 2 | Column 3
R01 Prim IP-Eth0 | R01 Prim IP-Eth0 | R01 Sec IP-Eth0

How would this line look like if I want it to go trough column 1,2 and 3 ?


Like '*' & [What's the IP ?] & "*" OR R01 Prim IP-Eth0 OR R01 Prim IP-Eth0 OR R01 Sec IP-Eth0 ???????? This didn't work..... I know it's me not seeing the solution, but please try and give me a hand.....

 

[VBslammer]

Something like this:

Sub FullLIKESearch(ByVal strSearch As String)
On Error GoTo ErrHandler
  Dim rs As DAO.Recordset
  Dim tdf As DAO.TableDef
  Dim db As DAO.Database
  Dim fld As DAO.Field
  Dim iResp As VbMsgBoxResult
  
  ' Search EVERY TABLE, EVERY FIELD in a database
  ' for a text string (substring, really)
  
  If Len(strSearch) = 0 Then Exit Sub
  
  Set db = CurrentDb()
  
  For Each tdf In db.TableDefs
    If Not (Left(tdf.Name, 4) = "MSys" Or UCase(Left(tdf.Connect, 4)) = "ODBC") Then
      Set rs = db.OpenRecordset(tdf.Name, dbOpenDynaset)
      
      rs.MoveLast
      iResp = vbYes  'reset
      
      If rs.RecordCount * rs.Fields.count > CLng(300000) Then
        iResp = MsgBox("Okay to do " & rs.Fields.count & " fields for " _
                  & rs.RecordCount & " records in " & tdf.Name & _
                  " ?", vbYesNo)
      End If
      
      If Not (iResp = vbNo) Then
        rs.MoveFirst
        For Each fld In rs.Fields
          rs.FindFirst "[" & fld.Name & "] LIKE " & "'*" & strSearch & "*'"
          While Not rs.NoMatch
            Debug.Print tdf.Name & "." & fld.Name & "=" & rs(fld.Name).Value, "Arg: " & strSearch
            rs.FindNext "[" & fld.Name & "] LIKE " & " '*" & strSearch & "*'"
          Wend
        Next fld
      End If
    End If
  Next tdf
    
  Debug.Print "DONE"

ExitHere:
  On Error Resume Next
  rs.Close
  Set rs = Nothing
  Set db = Nothing
  Exit Sub
ErrHandler:
  Select Case Err
    Case 3021        'no current record
      Resume Next
    Case Else
      Debug.Print Err, Err.Description
  End Select
  Resume ExitHere
End Sub

VBSlammer

Unemployed in Houston, Texas

 

[VBslammer]

I would limit the search to Text fields though:

        For Each fld In rs.Fields
          If fld.Type = 10 Then     [green]'Text fields only[/green]
            rs.FindFirst "[" & fld.Name & "] LIKE " & "'*" & strSearch & "*'"
            While Not rs.NoMatch
              Debug.Print tdf.Name & "." & fld.Name & "=" & rs(fld.Name).Value, "Arg: " & strSearch
              rs.FindNext "[" & fld.Name & "] LIKE " & " '*" & strSearch & "*'"
            Wend
          End If
        Next fld

VBSlammer

Unemployed in Houston, Texas