Access SQL Query

[Otacustes]

Hello Guys,

I am in need for some help with SQL code to search multiple columns in multiple tables in an Access database. We have a database supplied to us, as part of our CAD software, which contains multiple tables each with slightly different column counts and headings.

One thing in common is that every table has the columns 'Catalog', 'Manufacturer' and 'Description'. What we need is some SQL that will search each of these columns (in every table) and return any matches for the data and the table in which it is found.

All help is greatly appreciated
Kind regards
Ota

 

[JBinQLD]

Have a look at using VBA to loop through the table collection

For each table loop through the fields collection

if you find the fields you're looking for then store current tablename in a results table

When the loop is done you'll have a results table listing all tables that have the 3 fields. If you look at my reply "

http://www.tek-tips.com/viewthread.cfm?qid=1495073&page=1"

you'll also see a technique for generating a query from your result instead of just storing the relevant table names....

Hope this helps,

JB

 

[JBinQLD]

Hi,

On re-reading your op I think i've given you a bum steer.

Try this:

dim db as database
dim td as tabledef
dim strSQL s string
dim blFirstPss as boolean
dim strTablename as string
dim qd as querydef

blFirstPass=true
set db=currentdb

for each td in db.tabledfs
   strTablename=td.name
   if blFirstass then
       strSQL="SELECT Catalogue, Manufacturer, Description FROM " & strTableName
       blFirstPss=false
   else   
       'ossibly iterate through field lists to ensure cat, man and desc eist?
       'if the three fields exist then...
       strSQL=strSQL & " UNION SELECT Catalogue, Manufacturer, Description FROM " & strTableName
       'endif 
   endif
next td
strSQL=strSQL & ";"

set qd=new querydef
qd.SQL=strSQL
qd.name="Result"
db.querydefs.append qd
set db=nothing
set qd=nothing

I've not tested the code but it should make you a query called Result that displays the info you need. It will fail if any of the tables DO NOT contain catalogue, manafucturer and description fields so you may want to incorporate my idea from last post of iterating through the fields collection to ensure they exist before you append to strSQL. I've commented above where you'd need to perform this step

JB