conardb
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).OpenRecords et("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
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).OpenRecords
if rs.EOF then
else
debug.print "keywords found in table: " & tbl.name & " field: " & fld.name
end if
next
next
ASKER
getting type mismatch using below:
strSQL = "SELECT * FROM " & tbl.Name & " WHERE " & fld.Name & " IN (SELECT * FROM tblSearchTerms)"
Set rs = DBEngine(0)(0).OpenRecords et(strSQL)
strSQL = "SELECT * FROM " & tbl.Name & " WHERE " & fld.Name & " IN (SELECT * FROM tblSearchTerms)"
Set rs = DBEngine(0)(0).OpenRecords
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks... getting "block variable not set" on if rs.eof
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).OpenRecords
if rs.EOF then
else
debug.print "keywords found in table: " & tbl.name & " field: " & fld.name
end if
next
next