Link to home
Start Free TrialLog in
Avatar of conardb
conardbFlag for United States of America

asked on

How can I code access 2010 vba to search all data tables for keywords

Need to modify code below to be able to search entire database for term in table tblSearchTerm.Term field.  


dim tbl as tabledef
dim fld as field
dim rs as recordset
for each tbl in dbEngine(0)(0).tabledefs
  for each fld in tbl.fields
    set rs = dbEngine(0)(0).OpenRecordset("Select " & fld.name & " from " & tbl.name & " Inner Join tblSearchTerms On " & fld.name & "=tblSearchTerms.Term")
    if rs.EOF then
    else
        debug.print "keywords found in table: " & tbl.name & " field: " & fld.name
    end if
  next
next
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Perhaps something like:

dim tbl as tabledef
dim fld as field
dim rs as recordset
for each tbl in dbEngine(0)(0).tabledefs
  for each fld in tbl.fields
    set rs = dbEngine(0)(0).OpenRecordset("SELECT * FROM " & tbl.name & " WHERE " & fld.Name & " IN  (SELECT * FROM tblSearchTerms)")
    if rs.EOF then
    else
        debug.print "keywords found in table: " & tbl.name & " field: " & fld.name
    end if
  next
next
Avatar of conardb

ASKER

getting type mismatch using below:

                strSQL = "SELECT * FROM " & tbl.Name & " WHERE " & fld.Name & " IN  (SELECT * FROM tblSearchTerms)"
                Set rs = DBEngine(0)(0).OpenRecordset(strSQL)
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of conardb

ASKER

thanks... getting "block variable not set" on if rs.eof