Using VBA to search all tables in a database

[NL111]

How do I search for a value in all tables?

Does anyone know how I can do it? I am just for example searching for a value:
393592 in all of the tables which is in a database and make it display which table it's in :\

Thanks for any help

 

[Galaxiom]

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

Use VBA to loop through the tabledefs collection and then the fields collection in each of those tabledefs to create and run dynamic queries against each field.

However, requiring that kind of search suggests the structure of your data is not ideal.

 

[jdraw]

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

As Galaxiom has said, it appears you may have some design issues.

Ask yourself, What is a 393592?
Your question lacks any context -- partial phone number, bad zip code, serial number of a part, #393592 hex color, 13 Reviews of Property 393592 in San Francisco, California; ebay article.......

In relational database terms, you would have some idea What this represents and would know which table to query.

 

[NL111]

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

Thanks for all the replies.
@Galaxiom: How would I use VBA to loop through this? I am not the most experienced programmer so if you can guide me, that will be great.

@jdraw: the number is just a serial number, which means that if needed, I might need to search for a string such as 39NAK194N193, and sometimes just a number such as 1183831. I have realized that what I really need is something like the search box that Access has, except that it searches through all the different tables instead of just the one that is opened.

This is what I mean about the search box:
imgur.com/YmaWT.jpg

 

[NL111]

Does anyone know how I can do that? My database is split into at least 20 tables, and I would like to be able to search for a value in all 20 tables, and make it list out which table the value appears in.
Thanks

 

[AccessMSSQL]

How familiar are you with VBA? Just checking to see what kind of solution to provide you

 

[NL111]

I am not very familiar with it. Mostly what I am looking for is some kind of prebuilt solutions that I can just paste into the visual basic code.
If you are wondering, all I need is a text box where I can enter the search string, and a Search button. After that it will display where the string appears (which table it is in)

 

[AccessMSSQL]

Are all of your tables the same structure?

 

[NL111]

Yes they are. All the tables have the same layout. The difference is that each table is created for different shipments going out.
Some of the headers are, Serial, Position, Part Number, IP Address.

 

[jdraw]

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

NL111,
If you intend to use Access, you need to structure your tables. I recommend you read this material on relational database principles.
http://forums.aspfree.com/attachment.php?attachmentid=4712&d=1201055452

If you are going to randomly search for a string of characters regardless of where they reside, you don't need a database management system. Put everything into a simple text file if there is no meaning associated with the strings.

Good luck with whatever you decide.

 

[AccessMSSQL]

And are you only trying to search in one field?

 

[spikepl]

Double posting - http://www.access-programmers.co.uk/forums/showthread.php?t=234843

Please refrain from double posting.

 

[NL111]

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

@jdraw:
There are meaning associated with them, as there are fields, like part number, serial number, time, etc.

 

[NL111]

@AccessMSSQL: I am trying to search in all of the columns and rows of the table, and all tables in the database.
@spikepl: I am wondering how to use VBA to search now. The other one is more open.

 

[jdraw]

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

@jdraw:
There are meaning associated with them, as there are fields, like part number, serial number, time, etc.

Agreed, but you'd be searching for a part number or a serial number.

Did you read the article on Relational database Principles?

My guess is you need to design your tables.

 

[AccessMSSQL]

@NL111: I read your other post and Galaxiom suggested the best solution for accomplishing your solution. This might get you started but you will have to tweak this code as needed. Good luck.

Public Sub SearchTables(SearchString as String)
Dim tdf as dao.tabledef
Dim sTable as String
For each tdf in Currentdb.Tabledefs
sTable = tdf.Name
SearchTable(sTable, SearchString)
Next
end sub
 
Public Function SearchTable(Tablename as string, SearchString as string)
Dim rs As DAO.Recordset
Dim i As Integer
i = 0
Set rs = CurrentDb.OpenRecordset(TableName, dbOpenDynaset, dbOpenDynamic)
If Not rs.EOF Then
Do Until rs.EOF
For i = 0 To rs.Fields.Count - 1
    if instr(1,rs.fields(i).Value,SearchString) > 0 then
        Msgbox("Search Found in " & TableName & " field: " & rs.Fields(i).Name
        SearchtTable = TableName
   end if
Next
rs.MoveNext
Loop
End If
End Function

 

[gemma-the-husky]

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

i agree with the recommendations. it just cannot make sense to search every table. there should a context for the search item, which will point you to search a single column of a field, to be honest.

indeed you may find the search string in both a phone number, and a part number - but if the user is interested in parts, the phone number will be of no interest to him.

 

[NL111]

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

Oh ok, let's just say I want to search in a specific column, how would I do that?

 

[jdraw]

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

see the free video tutorial here
http://www.datapigtechnologies.com/flashfiles/searchform.html

 

[NL111]

Hi AccessMSSQL,
Thanks for the code. Right now I have a form which has the following:
A textbox where the search string is entered
A button that is supposed to search the string when clicked.
How would I use that code so that when the button is pressed, it searches for the value in all of the tables, and displays the name of the table where the number appears? I know that this is quite a hassle, but it is important so I hope you can help.
Thanks,