Using VBA to search all tables in a database

[NL111]

Re: How do I search for a value in all tables?

Hi jdraw. I have just watched the tutorial. Everything about that is fine but I just have 1 last problem. That query only searches in 1 table, I need it to search in all of the tables which I have.

 

[jdraw]

Please explain why you have to search all Tables.
When you are searching for something, you know what type of thing it is you are looking for.
For example:
I'm looking for Product Serial Number --then look in the Product table.
I'm looking for a Customer --then look in the Customer table.
I'm looking for the phone number of Supplier ABC Company. Then look for a Phone number in the Supplier table --- it makes no sense to look in the Products table.

You could adapt AccessMssql's code to look in a specific table.
You could have a Form with a combobox for the user to identify WHAT type of thing he/she is looking for,

Product
Customer
Supplier
then in a text box let the user enter a value to find in the selected table.

see Paul's comment in post #17 also.

 

[gemma-the-husky]

in fact, in a form bound to a table - just click on the cell/control that contains the data you are serching for - then use trhe binoculars (edit/find) to use the native access search mechanism

 

[NL111]

OK, sorry for making it a bit confusing.
Each table in the database will be a shipment on a day. the contents of the tables are computers. Each column is the computer part's serial number.
So I might need to find a serial number in the database. I want to be able to search the serial number up from the whole database and let me know when it was shipped (which table it's in).
I would have done the ways listed above, but as of right now, it's simply not feasible for me, considering each table will have about 2000 lines.

 

[jdraw]

???Research normalization and design your table(s).

 

[NL111]

I know that this is not the best database structure, but I can't edit the layout of the different tables as it is something that is set in stone for me. If I could edit how it is, I would have tried to get a better table layout from the beginning. What I need now is a way for me to just do the search please.

 

[DrallocD]

If you know the name of the column in the table, and it is a text field you can use something like the following:

Public Function FindTextValueAllTables(ByVal ColumnName As String, ByVal txtSearch As String) As String
On Error GoTo Errorhandler
Dim td As DAO.TableDef
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strReturn As String
Set db = CurrentDb
For Each td In CurrentDb.TableDefs
    strSQL = "SELECT COUNT(*) FROM [" & td.Name & "] WHERE [" & ColumnName & "] LIKE '%" & txtSearch & "%'"
    Set rs = db.OpenRecordset(strSQL)
    If rs.Fields(0) > 0 Then
        strReturn = td.Name
        Exit For
    End If
SkipTable:
Next td
ExitHandler:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    Set td = Nothing
    Set db = Nothing
    FindTextValueAllTables = strReturn
Errorhandler:
If Err.Number = 3061 Then Resume SkipTable
Resume ExitHandler
End Function

 

[AccessMSSQL]

@NL11: Place my original code in a new Module. From your form with the search textbox on it place a button there and in the button_click event call the function like this:

SearchTables(me.txtSearchString)

That will loop through every table and pop a message up that has the table name.

 

[NL111]

@accessMSSQL: I have done that and I am getting a syntax error. This is what my VBA page looks like.
The textbox's name is SearchBox. The search button's name is SearchButton. The clear button's name is ClearQuery.
http://i.imgur.com/MAMoO.jpg

 

[AccessMSSQL]

Sorry but I don't see what your syntax error is. The screenshot you posted doesn't really tell me much. I need to know what line the error is on, the exact error, and what your search string is.

 

[AccessMSSQL]

Also, I don't think I asked this before or if you posted this information but are your tables Linked tables from another Access DB? or are these SQL tables?

 

[DrallocD]

It looks like you need to assign the following line to a variable, or call it like a Sub
SearchTable(sTable, SearchString)
Try changing it to:
SearchTable sTable, SearchString

You are also missing a parenthesis on the MsgBox line.

 

[NL111]

@accessMSSQL: I have tried what dralloc said below, and it says there is an error at the Public Function SearchTable line, and the Msgbox line.
The tables are from excel documents which I have imported in, so they should be access DB.
@Dralloc: I have tried changing, and adding the paranthesis, and i am still getting errors. I have posted a new screenshot

http://i.imgur.com/SmDWA.jpg

 

[DrallocD]

try changing the MsgBox line to:

Msgbox "Search Found in " & TableName & " field: " & rs.Fields(i).Name

 

[NL111]

Thanks a lot, I can't believe I missed that. It works now, however, after the value is displayed and I clicked OK, this pops up:
http://i.imgur.com/EJuv5.jpg
When I click on Debug, this line is highlighted in yellow:
If InStr(1, rs.Fields(i).Value, SearchString) > 0 Then

On another note, is there a way for it to display the table just once? If I search for a value like 101, there are hundreds of matching numbers to that, and it just makes a bunch of result boxes with the same name :\

 

[DrallocD]

You are searching all field types, this will only look at text fields:

    If rs.Fields(i).Type = dbText Then
        If InStr(1, rs.Fields(i).Value, SearchString) > 0 Then
            MsgBox "Search Found in " & TableName & " field: " & rs.Fields(i).Name
            SearchTable = TableName
            'Exit For
            'Exit Do
        End If
    End If

Uncomment the Exit For if you want to stop searching the table and move to the next table.

Uncomment the Exit Do if you want to only find the first table.

 

[NL111]

I want to be able to search in all field types, not just text, as some of them are numbers with letters in it, etc.

I have tried to put your code in, but now the textboxes just keeps appearing, and there is no way to get out of that perpetual loop :\
If I don't put the first line, and I am trying to use the Exit For, it still loops around and keep displaying the textboxes.

 

[DrallocD]

Try using just text fields for now, number fields cannot contain letters.

    If rs.Fields(i).Type = dbText Then
        If InStr(1, rs.Fields(i).Value, SearchString) > 0 Then
            If MsgBox("Search Found in " & TableName & " field: " & rs.Fields(i).Name, vbOKCancel) = vbCancel Then
                Exit Do
            End If
            SearchTable = TableName
            Exit For
        End If
    End If

 

[NL111]

Thank you very much. It displays the Cancel button now, so I guess that is fine. However, if I search for a value that appears multiple times in a table, when I press OK, it just displays the box again, do you know how I can fix that problem? I just want it so that it will display the name of the table once, and if the search string comes up again, it won't display the name of the table again.

Edit: i just noticed that clicking Cancel again will make it go to the next table. Is there anyway for the OK button to do that?

 

[DrallocD]

Maybe try this then:

Public Sub SearchTables(SearchString As String)
Dim tdf As DAO.TableDef
Dim sTable As String
Dim sField As String
For Each tdf In CurrentDb.TableDefs
    sTable = tdf.Name
    sField = SearchTable(sTable, SearchString)
    If sField <> vbNullString Then
        If MsgBox("'" & SearchString & "'" & " Found in Table: " & sTable & " Field: " & sField, vbOKCancel) = vbCancel Then
            Exit For
        End If
    End If
Next
End Sub
 
Public Function SearchTable(Tablename As String, SearchString As String)
Dim rs As DAO.Recordset
Dim i As Integer
Dim sReturn As String
Set rs = CurrentDb.OpenRecordset(Tablename, dbOpenDynaset, dbOpenDynamic)
Do Until rs.EOF
    For i = 0 To rs.Fields.count - 1
        If rs.Fields(i).Type = dbText Then
            If InStr(1, rs.Fields(i).Value, SearchString) > 0 Then
                sReturn = rs.Fields(i).Name
                Exit Do
            End If
        End If
    Next
    rs.MoveNext
Loop
SearchTable = sReturn
End Function