Sql wont run in VBA

[dulla]

Hello i cannot get this statement to execute in vba:

Private Sub Operation_AfterUpdate()

DoCmd.RunSQL ("SELECT Last(UnitRate.UnitRate) AS LastOfUnitRate FROM UnitRate RIGHT JOIN EmpTicket ON UnitRate.Operation = EmpTicket.Operation;")

End Sub

i get the error: a run sql action requires an argument consisting of an sql statement.


Please help!

ameen

 

[Dimandja]

RunSQL cannot (search me) execute SELECT statements. It will execute DELETE, INSERT, UPDATE, though.

What these queries have in common is that they actually 'manipulate' data - where SELECT only makes data available without changing it.

There must be rules buried somewhere in MS manuals.

Dimandja

 

[scriverb]

The DoCmd.RunSQL VBA command can only execute ACTION queries. You are trying to run a SELECT query.

You can however run a SQL string with a DoCmd.Execute command:

Syntax:

object.Execute source, options

Post back if you have any other questions.



Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[dulla]

the reason i wanted to use this is so i can actually have a query run. when i use docmd.openquery in vba or even open a query myself manually, access opens the query without running it so i don't have updated information. My problem is that the query is not updated based on new values entered into the table. Is there another was to RUN or refresh a query after new values are put in (without having to open it, THEN run it)?

 

[psemianonymous]

...access opens the query without running it... "

You have to explain this. Every query, everywhere, opens with the most up-to-date information. If you open it in certain modes (e.g. snapshot mode) in a form, then your information slowly grows stale. If you are opening it in the query window, the information updates pretty much every time you move the cursor between rows.

So your complaint isn't valid. If you need some OTHER query run, so that your TABLE is kept updated, or ... I don't even know what. Given what you've said above, the information should be fresh, all the time.

So give a specific example of how this goes wrong, and maybe someone can help you.

 

[scriverb]

I think you are not understand a Select query vs an ACTION query. If you are updating a Select with new data while in the Datasheet view, any changes that you make while the query was open are now updated. There is no need to RUN the SQL for the query.

An Action query reads in tables and queries, linking the relationships, and then performs either a delete of certain records, update of certain records, or insert/append of certain records. These queries do require execution of the query or SQL.

I hope this helps you understand queries and SQL a little better.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]