SQL query & FIND string

[Nikos]

Hi...
I have 3 tables in an Access Database and I am using the
SELECT table1.*, table2.*, table3.*
statement in order to select everything from this database.

My question is how to search for a specific string in all
the tables...

In other words doing the same with access requires to run
the query and then push the find/replace button.

How can I do that with code in VB?

Thanks in advance
Nikos

[chongo 2002]

I don't know if this is what you mean or not:

dim rs as recordset
dim db as database

set rs = db.openrecordset("SELECT [Your Field Name you want to search for] FROM Table1, Table2, Table3 WHERE [Your Field] = somevalue" , dbopendynaset)

or if it is a textbox then

set rs = db.openrecordset("SELECT [Your Field Name you want to search for] FROM Table1, Table2, Table3 WHERE [Your Field] = '" & txt.text & "'", dbopendynaset)

To display the results use the
If Not rs.EOF then
txt1 = rs![yourfield]
End IF

[HunterMcCray]

Originally posted by chongo 2002
I don't know if this is what you mean or not:

dim rs as recordset
dim db as database

set rs = db.openrecordset("SELECT [Your Field Name you want to search for] FROM Table1, Table2, Table3 WHERE [Your Field] = somevalue" , dbopendynaset)

or if it is a textbox then

set rs = db.openrecordset("SELECT [Your Field Name you want to search for] FROM Table1, Table2, Table3 WHERE [Your Field] = '" & txt.text & "'", dbopendynaset)

To display the results use the
If Not rs.EOF then
txt1 = rs![yourfield]
End IF

The best answer if you want to replace the text is to open each table one at a time. Openning all three can create a great deal of confusion in updating.

Dim txtSQL as String
Dim txtSearch as String
Dim txtReplace as String
Dim rs as RecordSet
Dim db as DataBase

'Open your database

txtSQL="Select Table1.*, FROM Table1 WHERE (((Table1.FieldName) LIKE " & Chr(34) & "*" & txtSearch & "*" & Chr(34) & "));"

set rs=db.openrecordset(txtSQL,dbOpenDynaSet)

if rs.recordcount<1 then
set rs=nothing
exit sub
else
rs.movefirst
do
if instr(rs.FieldName, txtSrch) then
rs.edit
rs!FieldName=Instr(.....
rs.update
end if

loop
end if

Repeat For Table2

Repeat For Table3

.
.
.
.
.

Sorry, have to go, but this should give you the idea.
Email me if you need help [email protected]

Hunter