adodc recordsource problem

[ozpeppers]

I wish to locate a record within an access database using an adodc sql statement

Is it possible in visual basic to query a access 97 database using an adodc sql select statement with a variable within that statement?

i.e.

SELECT * FROM comsumers WHERE consumerID = variable

there was a seggustion to do this:

MySQL = "SELECT * FROM comsumers WHERE consumerID ='" & variable & "'"

But I'm sure on how to implement it.

Cheers In Advance

Mark

 

[stoolpigeon]

What part of the implementation is unclear to you?

The building of the query string looks correct. Are you asking about how to open the query against the database?

A small suggestion- When working with Access DAO may be better suited to what you are doing (unless you plan to move your database up to SQL Server at some time in the future- then I would stick with ADO)

 

[ozpeppers]

Yes, how to open the query against the database - it's for a datalist which will list all records whose ID match that of the query varible

cheers

Mark

 

[stoolpigeon]

O.k. - a quick snippet to connect to Access w/ADO. I'm not real good at the tag stuff- so my apologies if this is messy

dim conn as ADODB.Connection
dim rs as ADODB.Recordset
dim qry as string

Set conn=new ADODB.Connection

conn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\Microserver\eos\Providian\blkhole.mdb;Persist Security Info=False" '(This is all one line and you just need the path to your database)

qry="Select * from sometable where account = '" & table.txt & "'"

rs.Open qry,conn,adOpenKeyset,adLockOptimistic,adcmdText

--Now I'm typing this up off the top of my head but it should be pretty close. And the parameters you send to open your recordset may be different depending upon your needs. The cursor type can have a huge impact upon performance. If you can get away w/readonly or a fire hose cursor do it.

Once you have opened the recordset it contains the data of your query.

I don't mess w/datalists so I am not familiar w/how you add items to one- but it will roughly look something like this.

Do until rs.eof
datalist.addnew (?)
datalist.item=rs!somefield
rs.movenext
Loop

Like I said, I don't know the datalist but I think you will get the idea. This will loop through each item in the recordset until you reach the end of the recordset.

If you want to be sure that the query returned records before you try to loop through the recordset you could preface the loop w/a test:

If not rs.bof and not rs.eof then
Loop body
Else
What you do if nothing is returned by the query
End If

Hope that helps.

 

[lhugh]

Another problem you could run in to (depending on your version of Access and VB ) is that the statement "Provider=Microsoft.Jet.OLEDB.4.0 "

might need to be changed to 3.55