Query whole database

[Mark121]

Hi

Does anybody know some SQL to query the whole database and bring back all records that are like a value or multiple values.

e.g ‘Select * from [Database] where like %example%’

if this cant be done can it be don in code?

Any example would be appreciated.

Thanks

 

[Galaxiom]

Can't be done in plain SQL but it can be done in VBA with nested loops using the InStr function through each field in each tabledef.

I am about to go out but hopefully someone will help you. Start reading about "tabledefs" and "For Each" loops to get some background.

 

[DCrake]

Are you wanting to search through all text fields in all tables for a matching string? Depending on the size of your database this could become a very resource grabbing exercise.

However, lets asume we are taking about 2 tables and 6 fields

Think about union queries

Select Trim(Field1) & Trim(Field2) & Trim(Field3) As Contents, "123" As TableName From Table123

Union Select All Trim(FieldX) & Trim(FieldY) & Trim(FieldZ) As Contents, "XYZ" As TableName From TableXZY

Next create a query and include the above union query
Do a Like "*Fred*" in your condition and it will bring up all records

In this example it will show you which table it came from, but not which field. But it's a start.