Results 1 to 15 of 15
  1. #1
    Join Date
    May 2004
    Posts
    159

    Unanswered: need help understanding this SQL

    here is an SQL statement in an access form

    If IsNull(DLookup("lastrun", "tblUpdatelastrun", "lastrun =" + CStr(Date))) Then
    Dim dbs As DAO.Database
    Dim strsql As String
    Set dbs = CurrentDb
    strsql = "Insert into tblUpdatelastrun (lastrun) VALUES ( " + CStr(Date) + ")"
    dbs.Execute (strsql)

    what it does is inserts a date into a table for checking if an event already has run that day. It works but i don't understand why what seems to be the value of the date string is stored and not just the date which would be easier to read. Can you explain why it was done this way and if there is an easier way to do this?

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    hmmm, just a shot in the dark here, but yes, you can store a data as a date - you need a field of the Date/Time type to store it in.

    My guess is that somewhere along the line, somebody needed that value to be a string (or they thought it did). There are occasions where a date is more useful as a string, although none come to mind at the moment .....

    Try it out: add a new field, dat/time type and revise the code. Remember, when writing an SQL string, the CODE is a string, but you want to tell Access that the value is to be treated as a DATE, so you have to enclose it in pound signs #
    Code:
    strsql = "Insert into tblUpdatelastrun (lastrun) VALUES ( #" + CStr(Date) + "#)"

  3. #3
    Join Date
    May 2004
    Posts
    159
    Thanks for the response. Interestingly if I change as you reccomend the code puts in the date in standard human readable form but it no longer is able to read it in the first line and know the date has been posted. Putting # signs in the first line generates a compile error. The code works as I posted but the table is unreadable and I would lke a cleaner way to do it.
    What would be the fastest way to generate the same result? EG To know if a proceedure has run that day even if the program shuts down.

  4. #4
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201
    The reason why the string version is used is that IsNull requires three strings as its arguments. Given that, I am a bit surprised that using the # sign in line 1 generates an error. However, that is obviously why the author stored the date as a string.
    Another approach might be to create a string such as
    "SELECT lastrun FROM tblUpdatelastrun WHERE lastrun = #" & Date & "#"
    or "SELECT count(*) AS runcount FROM lastrun WHERE lastrun = #" & Date" & "#"
    and then open a recordset. If RecordCount = 0 or runcount = 0, the event has not been logged that day.

  5. #5
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Assuming the procedure you are refering to records the date/time it was run when it runs, then using any of a number of "domain lookups" will work.

    Use DMax() on the run date field to see when it was run last.
    Use DCount() of today's date to see how many times it was run today.
    Use DLookup() with run date = today's date to see if there is a run today.

    These functions can be used directly in code or even as the record source of a control on a form.

    Each function is in the help file. To see if it was run today, I'd probably use DMax.

    Have fun!
    tc

  6. #6
    Join Date
    May 2004
    Posts
    159
    Thanks for the suggestions on this. I went back and redid the code in the first line and I must have had an error before. This is the code that does now work and it gives me a table with a readable date:

    If IsNull(DLookup("lastrun", "tblUpdatelastrun", "#" & Date & "#")) Then
    Dim dbs As DAO.Database
    Dim strsql As String
    Set dbs = CurrentDb
    strsql = "Insert into tblUpdatelastrun (lastrun) VALUES ( #" + CStr(Date) + "#)"
    dbs.Execute (strsql)
    runmyproceedure
    end if

    what I want is the simplist way to do the above. I will look at dmax as suggested and see if I can use it. Like I said the ultimate goal of this is to have a piece of standard simple fast code I can plug in to check if a proceedure was run during that day.

  7. #7
    Join Date
    May 2004
    Posts
    159
    Quote Originally Posted by tcace
    Assuming the procedure you are refering to records the date/time it was run when it runs, then using any of a number of "domain lookups" will work.

    Use DMax() on the run date field to see when it was run last.
    Use DCount() of today's date to see how many times it was run today.
    Use DLookup() with run date = today's date to see if there is a run today.

    These functions can be used directly in code or even as the record source of a control on a form.

    Each function is in the help file. To see if it was run today, I'd probably use DMax.

    Have fun!
    tc
    Thanks. I am using dlookup() and it appears to be doing pretty much the same as a dcount() .. would dmax be faster?

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Domain Agg functions are notoriously slow. Here you are reading the table (using a slow function) and then altering the table if a condition is not met. If you want fast then I would use a single SQL statement.
    DLookup equiv:
    Code:
    strsql = "Insert into tblUpdatelastrun (lastrun) VALUES ( #" + CStr(Date) + "#) WHERE lastrun <> #" & CStr(Date) & "#"
    
    dbs.Execute strsql, dbFailOnError
    Dmax equiv:
    Code:
    strsql = "Insert into tblUpdatelastrun (lastrun) VALUES ( #" + CStr(Date) + "#) HAVING MAX(lastrun) <> #" & CStr(Date) & "#"
    
    dbs.Execute strsql, dbFailOnError
    Or even just the insert into without a where condition if there is no constraint on the table. So you end up with a record everytime the procedure runs - would this be a problem? The fact it is called lastrun makes me suspect you are only ever interested in the Max value anyway.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    May 2004
    Posts
    159
    Quote Originally Posted by pootle flump
    Hi

    Domain Agg functions are notoriously slow. Here you are reading the table (using a slow function) and then altering the table if a condition is not met. If you want fast then I would use a single SQL statement.
    DLookup equiv:
    Code:
    strsql = "Insert into tblUpdatelastrun (lastrun) VALUES ( #" + CStr(Date) + "#) WHERE lastrun <> #" & CStr(Date) & "#"
    
    dbs.Execute strsql, dbFailOnError
    Or even just the insert into without a where condition if there is no constraint on the table. So you end up with a record everytime the procedure runs - would this be a problem? The fact it is called lastrun makes me suspect you are only ever interested in the Max value anyway.

    HTH
    Hmm. if you update the table every time the program runs though it generates a lot of growth in that table's number of records.. that could slow down the dlookup.
    The code as is only adds one entry per day. It would be nice to have a way to limit the table to only todays value. As it is unless the table is manually cleaned out it grows unchecked..that could be a problem after a few years.
    The code as is checks if there is todays date and only then runs the update . Usually there is only the time wasted for dlookup to determine there is a null condition or not and if not the rest of the code is bypassed.How would your examples work with an isnull test?

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    The growth would be irrelevent as far the DLookup is concerned because the DLookup is removed. eread the SQL I posted - it effectively combines the DLookup check and insert into one single SQL statement by the WHERE clause. The Max one was daft - it serves no purpose - ignore it.

    Usually there is only the time wasted for dlookup to determine there is a null condition or not and if not the rest of the code is bypassed
    That's the problem - the DLookup takes longer than the insert it is designed to avoid. It would be quicker to always insert the data without the DLookup test. Whether or not you included the WHERE condition would be up to you.

    BTW - are you only ever interested in the last date the code was run? Do you view this data as an audit trail or do you merely want to see if the code was run today? If the latter then why not update rather than insert?

    Code:
    UPDATE tblUpdatelastrun 
    SET lastrun = #" + CStr(Date) + "#"
    Only ever one record.

    Just throwing some alternatives your way.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Seems I've forgotten how to write SQL too:
    Code:
    strsql = "INSERT INTO tblUpdatelastrun (lastrun) " & _ "SELECT DISTINCT #" + CStr(Date) + "# " & _ "FROM tblUpdatelastrun " & _ "WHERE NOT EXISTS(SELECT NULL FROM tblUpdatelastrun WHERE lastrun = #" & CStr(Date) & "#)"
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    May 2004
    Posts
    159
    an update would be the best choice but I am finding the code bombs out if there is no data at all in the table. also the update SQL doesn't want to work when there is data to update- it bombs out with a 128 error.
    Here is how I am using it
    strsql = "UPDATE tblUpdatelastrun SET (lastrun) = ( #" + CStr(Date) + "#)"
    dbs.Execute strsql, dbFailOnError

  13. #13
    Join Date
    May 2004
    Posts
    159
    Here is what I am going with at this point. I put in an SQL DELETE to keep the table at one record- all I am interested in is whether or not to run the proceedure for this day and only run it once. Using an SQL INSERT is necessary because I just deleted all the records and an update would not work if there is no data in the table.
    Pootle I looked at your SQL INSERT code but I still need to do the dlookup to run the IF statement which runs the proceedure so I don't see a speed increase resulting..
    Can anyone improve this?

    CODE:
    If IsNull(DLookup("lastrun", "tblUpdatelastrun", "#" & Date & "#")) THEN
    DoCmd.RunSQL "DELETE * FROM tblupdatelastrun", True
    Dim dbs As DAO.Database
    Dim strsql As String
    Set dbs = CurrentDb
    strsql = "Insert into tblUpdatelastrun (lastrun) VALUES ( #" + CStr(Date) + "#)"

    dbs.Execute strsql, dbFailOnError
    runmyproccedure
    end if
    CODE:

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    what is all this delete stuff?

    make a table 'myTable" magically, specifically, essentially a one-record table
    give it a PK 'myPK', long
    manually make one record myPK=1, put whatever garbage you like in any other fields for that one and only record with myPK=1

    from then on, never, never, NEVER delete

    write to it:
    UPDATE myTable SET glooob = Blah WHERE myPK=1

    read from it:
    SELECT glooob , bloooob, frooob FROM myTable WHERE myPK=1

    izy
    currently using SS 2008R2

  15. #15
    Join Date
    May 2004
    Posts
    159
    Quote Originally Posted by izyrider
    what is all this delete stuff?

    make a table 'myTable" magically, specifically, essentially a one-record table
    give it a PK 'myPK', long
    manually make one record myPK=1, put whatever garbage you like in any other fields for that one and only record with myPK=1

    from then on, never, never, NEVER delete

    write to it:
    UPDATE myTable SET glooob = Blah WHERE myPK=1

    read from it:
    SELECT glooob , bloooob, frooob FROM myTable WHERE myPK=1

    izy
    You'll have to be specific.
    I can set a PK in updatelastrun which is a one record table for record lastrun- do I reference lastrun then as 1?
    can you show specifically the code based on what I have?
    I tried using the update mytable set gloob = blah previously and it did not seem to work so I need the code spelled out on that.
    Last edited by WilliamS; 04-12-06 at 16:25.

Posting Permissions

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