Find syntax for ADO

[AshikHusein]

I would lik to know how to use find for ADO.

I am using the following and it does not seem to be working.

rec.find "user_id = """ & theuser &""""

I have seen from some previous posts that it shld be

rec.find "user_id = "'" & theuser & "'"" (using ' instead of ")

but ' is interpreted to be a program comment.

Would appreciate help for the above.

 

[scottn]

you have a couple too many " in there. try this:

rec.find "user_id = '" & theuser & "'"

 

[Mile-O]

you have a couple too many " in there.

Actually, there's nothing wrong with

rec.find "user_id = """ & theuser & """"


It's more robust than your suggestion (rec.find "user_id = '" & theuser & "'") as the ' within a string can terminate if one appears within a string as part of the text (i.e. O'Donnelly)

 

[AshikHusein]

Thanks for your inputs Mile and Scott:

Scott's suggestion has worked fine so I will stick with it for now.

Regards

Ashik

 

[scottn]

Ashik,

Actually Mile-O-Phile is absolutely right about this:

Actually, there's nothing wrong with

rec.find "user_id = """ & theuser & """"

It's more robust than your suggestion...

I was just pointing out the reason why your attempt to use the single quote ' wasn't working.

 

[Mile-O]

Since your dealing with SQL style language, and your field's title contains a special character you'll also have to surround it with square brackets:

rec.find "[user_id] = """ & theuser & """"

 

[AshikHusein]

Correct me if I am wrong here:

With DAO you would need a string which translates to something like

user_id = "XYZ" (1)

whereas in ADO you would need

user_id = 'XYZ' (2)

I was trying (1) and it wasnt working.

 

[Mile-O]

You are wrong - the delimiter has nothing to do with ADO or DAO.

You simply can't use user_id = 'XYZ' in the VB editor as it's not valid syntax.

 

[AshikHusein]

Well, Scotts suggestion effectively translates the find method to

user_id = 'XYZ' and it works

Again, the syntax I am using is

rs.find "user_id = '" & theuser & "'"

where the theuser is XYZ

Are you saying then that

rs.find "user_id = """ & the user & """" shld also work?

 

[Mile-O]

Well, Scotts suggestion effectively translates the find method to

user_id = 'XYZ' and it works

Within these -> it works (ie. "user_id = 'XYZ'" ) but you asked if user_id = 'XYZ' would work and I said no because I thought you were typing it into the VB editor on its own and not as the string argument that it is. I understand now.

Are you saying then that

rs.find "user_id = """ & the user & """" shld also work?

Yes, I am.

But I also said that you should enclosed the user_id within square brackets.

 

[AshikHusein]

No Mile, this also does not work

rs.find "[user_id] = """ & the user & """"

When I use the above I get error 3021 (EOF or BOF reached)

This means that the syntax is correct but the string is not translating to something which specifies that the item to find is XYZ. If the syntax was not correct it would have given me a compile error.

When I use

rs.find "user_id = '" & theuser & "'"

it works.

I am changing all my code from DAO to ADO. Your suggestion works fine with DAO which I have always been using.

 

[Mile-O]

Out of interest why are you changing your code to ADO when you already have it as DAO?

 

[AshikHusein]

Well from the info I have, if you migrate your system to an SQL sever using the upsizing wizard, all code shld be ADO. From the way I see things SQL is where things may be going where I work.

I also know that Microsoft now has two minds about ADO but I want to be prepared.

 

[scottn]

Ashik & Mile,

Can you guys bring me up to date on the ADO vs. DAO thing? I have been using ADO w/ Access 2000 for years. When A2K came out I understood that the official line from Microsoft was that ADO was the way of the future. The development of ADO.NET, I thought, kind of confirmed that. But Mile-O-Phile, your comment and a few others I have read on this board make it sound like DAO is preferred these days.

 

[AshikHusein]

From what I have been noticing, I think ADO is the highly preferred one. The development of ADO.Net does confirm that.

The company where I work, is also moving things to the intranet so that the front end does not have to be distributed to the employees everytime.

By using ASP.Net employees can log on from the companies intranet site and perform all data base updates. The back end is usually SQL server (or could even be Oracle).

Moving to ASP.net also helps to minimize security risks and reduce network traffic.

For all newcomers to ACCESS, I would advise that learning VB.net and using it in the ASP.net framework seems to be the future.