Update query through vba

[Thingol]

Hi All,

I'm trying to create an Update query through VBA. This is the code I currently use to set the sql of the query:

Set db = CurrentDb()
strSQL = ""
Set Q = db.CreateQueryDef("qrySelectionLabelsUpdate")
strSQL = "UPDATE qrySelection SET qrySelection.Gender = 'Sir' WHERE (((qrySelection.Gender)='Male' Or (qrySelection.Gender)='2'));"
Q.sql = strSQL
Q.Close

I want to open the update query from code, so that the query qrySelection gets updated automatically. How can I do this?

Thanks a lot for any help!

Best regards,
Martijn Senden.

In the Beginning there was nothing, which exploded.

--Terry Pratchett, Lords and Ladies--

 

[KenReay]

Hi

how about

Set Rs = q.OpenRecordset

alternatively (and probably better from a maintenance point of view), why not add a Parameters Clause, and set the parameters in code using the parameters collection?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Hi

how about

Set Rs = q.OpenRecordset

alternatively (an probably betetr from a maintenance point of view), why not add a Parameters Clause, and set the parameters in code using the parametrs collection?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PHV]

And what about something like this ?
strSQL = "UPDATE qrySelection SET Gender='Sir' WHERE Gender In ('Male','2');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

But is qrySelection a real table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Thingol]

Hi all,

Thanks for the input! I have worked around the problem in a different way.

Best regards,
Martijn Senden.

In the Beginning there was nothing, which exploded.

--Terry Pratchett, Lords and Ladies--