Search all Tables

B

Brant

Is there a way to search all tables for records containing certain
values. For example, say i have non related tables with different types
of IDs, and I want to return all ids that have 123 in them. Like i have
a table for drinks, artisits, and furniture that are non related and I
want all of the Ids that contain a certain value in them (so if an id
was A123 and another B123 and i searched 123 A123 and B123 would appear)
thanks
 
A

Arvin Meyer [MVP]

What you'd need to do is get all the tables (in a query or SQL statement the
following will get them):

SELECT Name
FROM MSysObjects
WHERE ((Name Not Like "MSys*")
AND (Type=1 Or Type=6));

Use the SQL above to build a recordset, then iterate through the recordset
getting all the fields. Or you can look at the free utility at my website
that can build a table of all the table and field names:

http://www.datastrat.com/Download/FieldDescription2K.zip

Then iterate through each field looking for your criteria. I have a feeling
that you have built multiple tables containing the same type of data, and
that is the wrong way to design a database. You would not have this problem
(at least to the same degree) if you had only 1 table.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Top