Searching for a particular ID number across all tables in an Access Database

[83dons]

I have inherited an old Access database with about 300 tables. We need to be able to search for a client ID number (for example 'R12345') in every table in the database and to return all tables that it exists in (all rows would actually be good but a list of the tables it exists in would do as a summary). So I guess the query would need to go through every column and every row in every table in the database before returning its results. I am guessing this must be possible but haven't the faintest idea what SQL would achieve this! Can anyone help? It is important we find every entry that references this client ID in the database as otherwise medical records for that person could be missed out of the search.

[orange]

You could try the info in this link (modified Universal search)

[83dons]

Hi that is not exactly what I am requiring. I require all columns and all rows of every table to be searched for the ID number.

[orange]

Hmmm? Did you read post #4 on the link I provided?

Perhaps you can describe the details of

Hi that is not exactly what I am requiring

?

[83dons]

I already have see the first post...

[orange]

So you're saying 'R12345' is a number and not text?

[Micron]

To get around the primary key restraint in the above link, I think you'd need to
- iterate over the db tables collection in an outer loop, ignoring the system tables
- iterate over the fields collection of a table in an inner loop, and execute a simple sql search on a field
- move to the next field and search again until you find a result
- store the 'active' table name and field, and if it seems reasonable, the value in the first field (in case it's an index that can aid in finding the record row)
- exit the inner loop, and move to the next table in the outer loop
- rinse and repeat
- present the results as a report from wherever the values were stored (table would be simplest)

Perhaps you will get a better response here, or if you search the 'net yourself, you might find someone who's already done this. Or maybe Orange's db could be easily adapted to not require that the tables have a PK field? Failing that, I've sketched out the basic steps I think you'd have to find code for.

[orange]

I mocked up 2 tables and tried the universal search.

I guess I'm missing something.




Micron, where did you see anything in the OP's info that the PK is an issue?

[Micron]

Ya know, I think that stuck in my head after reading at the other thread. Sorry.

[orange]

No, I also thought the OP was commenting on either PK or limited to "text".
The routine works with text and memo fields, so I questioned the 'R12345' .