Results 1 to 14 of 14
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need code to run when backend is closed. (2002/SR-1)

    I've created a delete query that purges all records marked for delete from my table. If this is a multi user system then I don't want it to run every time the front end closes, so I was trying to have this query run when all users have closed their front ends. So basically I want it to run on application exit in the backend. This query resides in the backend. Any suggestions would be welcomed!
    Thanks for your time.
    Don

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

    Re: Need code to run when backend is closed. (2002/SR-1)

    Wouldn't it be simpler to run it the next time someone opens the FE database? You're going to have to have *some* database open to run that query, and it isn't a good practice to work directly in the backend..

    It also isn't a great idea to have queries or code in the backend ... unless the backend happens to be SQL Server. Queries, and especially action queries, contribute to a significant amount of bloat.
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need code to run when backend is closed. (2002/SR-1)

    Thanks for the reply Charlotte,
    I thought of running it from the fontend but it will run every time a different user opens the database in the morning. Wouldn't that cause delays and unnecessary network traffic? I wanted it to run on the backend because "ideally" the backend is opened once and closed once each day. Is there an easy way to make it run once from the frontend?
    Thanks
    Don

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Need code to run when backend is closed. (2002/SR-1)

    Store the "date last run" in a table and have the front end check whether it has been run today, if not, run it? Nothing like punishing the early risers. <img src=/S/snore.gif border=0 alt=snore width=32 height=15>

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need code to run when backend is closed. (2002/SR-1)

    Thanks for the help! Works great!
    One more minor problem. Here is the code that executes the query:

    Dim rst As New ADODB.Recordset
    rst.Open "tblConfigBE", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

    If rst!DatePurgeLastRan <> Date Then
    DoCmd.OpenQuery "qdelPurgeClosedCases"
    rst!DatePurgeLastRan = Date
    rst.Update
    End If
    rst.Close

    This works great but I don't want to run the query if there are no records to delete.

    If rst!DatePurgeLastRan <> Date Then

    How do I stop this next line from executing if there are no results returned from the query?

    DoCmd.OpenQuery "qdelPurgeClosedCases"

    Do you see any problems with a delete query running when there are no records to delete? Would it corrupt the database over time? I get the message "about to alter 0 records" or something to that affect. Obviously I'm going to set warnings to false so the user doesn't see this message, but was just wondering if I should check for this in my code and handle it accordingly and if so, how??

    Thank you,
    Don

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Need code to run when backend is closed. (2002/SR-1)

    Cannot see any value in checking if there are any records to delete.

    It should not corrupt your database. There would have been a million screams by now if that were the case.

    HTH
    Pat

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need code to run when backend is closed. (2002/SR-1)

    If rst.RecordCount > 0 Then
    If rst!DatePurgeLastRan <> Date Then
    DoCmd.SetWarnings False 'Turn the warning messagebox off
    DoCmd.OpenQuery "qdelPurgeClosedCases"
    rst!DatePurgeLatsRan = Date
    rst.Update
    End If
    Else
    MsgBox "There are no records to delete.",vbInformation,"Status:"
    End If
    rst.Close
    set rst = nothing


    HTH <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need code to run when backend is closed. (2002/SR-1)

    Mark,

    My opening of rst is just to check the date value of DatePurgeLastRan from my config table which stores parameters for queries and other settings. I would have to open a recordset on my delete query "qdelPurgeClosedCases" and check for RecordCount > then 0. I tried doing this, by opening a rst2 but for some reason this didn't work, I'm not sure if you can open a recordset on a delete query?? Anyhow it was late last night. I was probably typing with my eyes wide shut! Anyhow I will give it a try in a little bit and if it doesn't work I think I will go with Pat's suggestion to just not worry about it since the users will never get a prompt anyway.
    BTW Mark, I got my delete query working by doing a search here in the forum and pulled up a post by you that mentioned using dlookup in the design grid to reference a value stored in a table. I was trying to add the table to the query when I didn't have to! Just wanted to say thanks!

    One more quick question. Why do I always get an error when I try to set rst = nothing after closing the rst? Do you not have to set it to nothing in ADO?

    Thanks again,
    Don

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need code to run when backend is closed. (2002/SR-1)

    Hi Don,

    You should be able to open a second recordset with no problems. Make sure your declarations are correct (Dim rst2 as New ADODB.Recordset) and that you've opened the second record source properly. Rather than opening a delete query to check for records, I would create a SELECT query with the same criteria - then check it's record count before deciding whether to run the Delete query.

    As for destroying the rst variables - I'm not sure why you're getting an error. As far as I know, ADO variables are the same as any other - destroying them is ideal, although not always necessary. What kind of error are you getting?

    Please keep us informed with your progress!

    HTH <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

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

    Re: Need code to run when backend is closed. (2002/SR-1)

    You can't open a recordset on an action query, because they don't return recordsets. You would have to open a recordset on the equivalent select query.
    Charlotte

  11. #11
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need code to run when backend is closed. (2002/SR-1)

    Mark,
    Thanks for the help! Creating a select query was all I needed to do. As far as setting the recordsets to nothing, it's working now? I may have tried setting them to nothing before closing them?? I can't remember now? This is my code revised with additional validation, msgs, and a counter to count how many records they are actually deleting and display it in a msgbox.
    <pre>Dim rst As New ADODB.Recordset
    Dim rst2 As New ADODB.Recordset
    Dim lngCount As Long
    rst.Open "tblConfigBE", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    rst2.Open "qryCountPurgedCasesClosed", CurrentProject.Connection, adOpenStatic
    lngCount = rst2.RecordCount

    If rst!DatePurgeLastRan <> Date Then
    If lngCount = 0 Then
    MsgBox "There are no archived records to delete." _
    , vbInformation, "Empty Archive"
    ElseIf MsgBox("Are you sure you want to delete " _
    & lngCount & " Record(s) from Archive?" _
    , vbExclamation + vbYesNo, "Warning") = vbYes Then
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qdelPurgeClosedCases"
    DoCmd.SetWarnings True
    rst!DatePurgeLastRan = Date
    rst.Update
    MsgBox "You have successfully deleted " _
    & lngCount & " record(s) from Archive.", , "Success"
    End If
    Else
    MsgBox "Records have already been purged today." _
    , vbExclamation, "Purge Not Needed"
    End If
    rst.Close
    rst2.Close
    Set rst = Nothing
    Set rst2 = Nothing
    </pre>


    Should I be setting the variable lngCount to nothing? When I do I get thrown into the de###### with message highlighting lngCount saying "Compile error - object required".
    Thanks,
    Don

  12. #12
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need code to run when backend is closed. (2002/SR-1)

    Hi Don,
    I'm glad to hear that you got things working!

    AFAIK you shouldn't need to set a long variable to nothing. I only do that with data types of variables. As soon as they're out of scope (i.e. when the function is over) they free any tied-up memory. (emphasis on the AFAIK)

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

    Re: Need code to run when backend is closed. (2002/SR-1)

    You CAN'T set a long variable to Nothing. Only variables instantiated using the Set keyword (in other words, object variables) can be set to Nothing.
    Charlotte

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

    Re: Need code to run when backend is closed. (2002/SR-1)

    See my response to Mark. You can only set object variables to Nothing. Trying to set any other kind of variable to Nothing will cause that error.
    Charlotte

Posting Permissions

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