queries to code (A2002)

[ppem]

I'm running a series of queries in an event procedure for a form using DoCmd. I'd like to get rid of the queries by using SQL in the code instead. I need some guidance in how to structure it.

FYI, here is the SQL for the 4 queries:

SELECT ttblHours.HrsOpID, ttblHours.ClientOrgID, ttblHours.DaysID, ttblHours.OpenTime, ttblHours.CloseTime, ttblHours.DaysClsd INTO qryHours1
FROM ttblHours;

UPDATE qryHours1 SET qryHours1.ClientOrgID = Forms!frmOrg!txtOrgID
WHERE (((qryHours1.ClientOrgID) Is Null));

INSERT INTO tlkpHours ( ClientOrgID, DaysID, OpenTime, CloseTime, DaysClsd )
SELECT qryHours1.ClientOrgID, qryHours1.DaysID, qryHours1.OpenTime, qryHours1.CloseTime, qryHours1.DaysClsd
FROM qryHours1;

UPDATE ttblHours SET ttblHours.ClientOrgID = Null, ttblHours.OpenTime = Null, ttblHours.CloseTime = Null, ttblHours.DaysClsd = Null;

Elizabeth

[TheGry]

G'day Elizabeth
These look fairly straight forward. When I'm coding queries into code using SQL, I generally check the SQL out in the Query Builder - run them and check the results are correct - then just cut and paste the SQL text straight from the QB into the DoCmd. Often I cut corners and write the SQL off my head, but every now and then ...

DoCmd.RUNSQL "SELECT ..... ;"

The only tricky bit tends to be when you need to explicitly put values into the query, especially strings, since it can mess up your quotes. In that case - use twin quotes around the SQL and single quotes within the SQL statement as text identifiers. So if lngNumber is numeric and strText is text:

DoCmd SQL "SELECT Table.* FROM tblTable WHERE tblTable.lngField1 = " & lngNumber & " AND tblTable.strField2 = '" & strText & "' ORDER BY tblTable.dtField3;"

Note the single quotes surrounding the " & strText & "

However it doesn't seem that you'll need to do that from what you've posted. If you're filtering on dates however - that can be a whole different story. SQL seems to ignore what you've got set in your control panel and insists on it's own peculiar date format #m-d-yy#

Regards Ken

[kwvh]

Elizabeth,
I don't know how familiar you are with Access, so please accept my apology in advance if this doesn't meet your expectation.

Like TheGry, I too create queries using query builder and then copy the SQL code for pasting into "DoCmd.RunSQL" statement. Having said that, the caveats for me is knowing how to continue lines and using quote, ampersand and underscore (", &, _ respectively). The following are examples of your code in Access using line continuations for easy readability:
DoCmd.RunSQL ("SELECT ttblHours.HrsOpID, ttblHours.ClientOrgID, ttblHours.DaysID, ttbl" & _
"Hours.OpenTime, ttblHours.CloseTime, ttblHours.DaysClsd INTO qryHours1 " & _
"FROM ttblHours;" )

DoCmd.RunSQL ("UPDATE qryHours1 SET qryHours1.ClientOrgID = Forms!frmOrg!txtOrgID " & _
"WHERE (((qryHours1.ClientOrgID) Is Null));")

DoCmd.RunSQL ("INSERT INTO tlkpHours ( ClientOrgID, DaysID, OpenTime, CloseTime, DaysClsd ) " & _
"SELECT qryHours1.ClientOrgID, qryHours1.DaysID, qryHours1.OpenTime, qryHou" & _
"rs1.CloseTime, qryHours1.DaysClsd " & _
"FROM qryHours1;")

Hope this helps.

Ken

[charlotte]

Getting rid of queries is NOT a good idea. The SQL that you create and execute in code is not optimized, which means it slows down the operation. A saved query, on the other hand, is optimized by the query engine and stays that way. I use saved queries whenever possible in all versions of Access later than 97.

[ppem]

Charlotte,

Thanks for the heads up on the query optimization.

E

[ppem]

Thanks fellas, worked like a charm.

E

[Rupert]

You should always aim for optimized queries as suggested by Charlotte. However there are two occasions where hard coding is a good idea. If you want to insure your query and form never get separated (not a very likely scenario I know) and the other time is when you make your db into an mde. If you hide the query in the forms module it makes it very difficult for someone to plagiarize your work.

[kwvh]

Charlotte,
Is there a short answer to: "What does Access do when it optimizes a query?"

I didn't know about the optimization issue, and have a lot of reports that are based on SQL statement as opposed to stored queries. Is there a significant gain in stored query over SQL?

I have a bunch of reports that execute SQL queries in the "On Open" or "On Print" events of the report. Should I consider converting the statements to stored queries?

Thanks for your patience with what probably seem like dumb questions.

Ken

[HansV]

These are not dumb questions at all. There is some info on optimization in ACC2000: How to Optimize Queries in Microsoft Access 2000.

The advantages of optimizing a query will be noticeable when it involves tables with a very large number of records, especially if it is a complex query. If the tables are not so large, you will hardly notice the difference between an optimized query and a non-optimized one. If your reports take an exorbitant long time to open and to move from page to page, consider creating stored queries from the SQL strings you use now. If they react quickly enough to your taste, I wouldn't bother.

[kwvh]

Thanks Hans for the link.
There is a wealth of information relating to queries and optimization, including linked tables and ODBC. Now I have to let all that sink in.

I appreciate all your help and your willingness to respond to any and all questions.

Happy New Year!

Ken