Web Development

Ask something
Discussion Locked

Find using SQL and ADO?

0 helpful
5 replies

by craig shepherd 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am using VB6 with Access2002 and ADO2.6

I have used DAO with some sort of SQL string to do a find. Problem is that was a while ago now and with Access2002 I'm pretty much forced into using ADO.

So can someone give me a basic example of how to find a record by a field. And am I able to use SQL, or, is there another way.

I am using a recordset which has been set by an ADODC.

Thanks for your time

5 replies

by medievaldude

In my code example, I'm assuming that rs and conn have already been set appropriately.

Dim strSQL

strSQL = "SELECT field1, field2 FROM mytable WHERE field3 = 'blah'"
rs.ActiveConnection = conn

rs.Open strSQL, conn

Hope that helps. Postmore if you have additional questions.

by shanghai sam

I'm using an ADODC, so I havn't dimmed anything in code. It didn't seem to work with that though.
How do I retrieve the results afterwards any way.

Do you reckon you could send me a sample program using the nwind db for example.

by sl-campbell

Use the FindFirst on the rs in the DC:
data1.Recordset.FindFirst strFieldNameToSearch & "='" & strFindWhat & "'"
Note: for Number fields leave out the single quotes and for date fields use the #date# sign.
You can still create and use a rs objectwith-out having to use the DC. If you use the DC you can set the data1.recordset to another rs at run time (set data1.recordset = myRS) or apply a sql string to the data1.recordsource and do a refresh.

This discussion is closed to new replies.