table1 field1 (text <-search me)
table1 field2 (memo <-- and me)
table1 field3 (memo <-forget me)
i have many(30 ish) access tables of same structure they arent linked together/indexed/keyfielded)? at all if it matters ,i need to search the two fields from each table( one field is memo type other is text type) and return them to dbgrid
field names are same as tablename + space then another word and im reading them from combobox which was filled via gettablenames;
the following statement works fine on the text field
'SELECT * FROM ['+Combobox1.text+'] WHERE['+
combobox1.text+' title] LIKE "'+Edit1.Text+'%"'
the sql it returns is
SELECT * FROM [testtable] WHERE[testtable field1] LIKE "shell%"
ok great so filling field1 with api calls it finds shellexecute,shellexecuteex etc..
now im stuck
first problem is it doesnt work on the memo field ,i think i need to use "in" statement for that field.
and main prob how to do the search over both fields from all the tables in combobox.
i dont know where to go from here i dont know much sql so maybe i should be using "union all" or adding each found record to a completely new table using "insert into" ?do i need to set keyfields for many tables to make it easier?
i really need a complete sql(tested ideally) answer so plenty of points for sql gurus..oh if there more than one way please say what is better and why ..
(hasnt someone made a tool for doing this yet ;-)