Queries SQL and VBA

[ASherbuck]

I've been fine with using access so far but I guess I'm hitting a point where I need to start using straight VBA instead of Macros and expressions. I think what I need is a little dot connecting at this point.

I understand that I can look at my queries in SQL view, and I believe somewhere I picked up that this is used in conjunction with VBA somehow. My question is, how do I use that information to accomplish that.

If I am completely missing something that's fine, I'm just trying to get away from making OnClick a macro so that I can get more functionality and power out of my control buttons. E.g. Instead of running an update query when a button is clicked I'd like to Run that update query and perform other VBA functions.

 

[gemma-the-husky]

if you write sql directly
eg sqlstrg = "delete * from temptable" you can run this by

docmd.runsql sqlstrg or
currentdb.execute sqlstrg

or you can design a stored query to do this and then run the stored query with either

docmd.openquery ("queryname") or
currentdb.execute ("queryname")

these two operations are broadly similar in effect, but handle errors differently, so its a matter of choice which one you need

you can string as many functions together as you want on a button click, but you have to have good error handling in case sonething goes wrong.

Dealing directly with sql is more arcane, as its completely unforgiving of syntax errors and you can generally design a stored query to do the same thing. If you look at the sql for a complex query you will see how awkward the syntax is.

stored queries should be slightly more efficient as access creates and stores a "plan" to process them in the most efficient way, whereas if you use raw sql it cant do this - this only realy matters for large complex queries i should think

hope this helps

 

[pbaldy]

(you have to do the last bit in this way, because execute needs a sql string, not a stored query, so you have to get at the stored sql)

One correction; Execute can run stored queries. This is straight out of a production db I'm working on at this moment:

db.Execute "QueryName"

 

[gemma-the-husky]

silly me

runs an action query or sql statement - you got me on to these anyway paul, and i use thme a lot of the time now

edited out my bad now - lol