Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    queries to code (A2002)

    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

  2. #2
    2 Star Lounger
    Join Date
    May 2002
    Location
    Dubai, UAE, United Arab Emirates
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: queries to code (A2002)

    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

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: queries to code (A2002)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: queries to code (A2002)

    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.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: queries to code (A2002)

    Charlotte,

    Thanks for the heads up on the query optimization.

    E

  6. #6
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: queries to code (A2002)

    Thanks fellas, worked like a charm.

    E

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: queries to code (A2002)

    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.

  8. #8
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: queries to code (A2002)

    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

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: queries to code (A2002)

    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.

  10. #10
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: queries to code (A2002)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •