Page 1 of 5 123 ... LastLast
Results 1 to 15 of 67
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Time lag (Access03)

    I want reports turned out based on departments selected from a sub form. I want to be able to select all or deselect the selected records. I finally figured out to use an update query to do both actions. Then activated the query with a button. They work as programs except that it literally takes 55 seconds to clear the records and 20 seconds to select all. What can I do to speed up the response? Should I write it in SQL and run it all from VBA? If so can anyone help me. I am an extreme novice with SQL. The code that I get from the QBE when I go into SQL view is as follows:

    UPDATE tblDepartments SET tblDepartments.Requested = 0
    WHERE (((tblDepartments.Requested)=-1));

    Thank you for your help.

    Fay

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time lag (Access03)

    Fay,
    How many records do you have in your table?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time lag (Access03)

    There are only 52 departments. Fay

  4. #4
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time lag (Access03)

    Is the field [Requested] a check box?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time lag (Access03)

    Yes. Thanks for your help.

  6. #6
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time lag (Access03)

    If you're just trying to clear the check boxes, remove your WHERE clause.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  7. #7
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time lag (Access03)

    Both now take 55 seconds to do their job. I also should mention the production computer I am using has 3 GB of RAM. The users are much lower.

  8. #8
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time lag (Access03)

    There's no reason it should take that long....are you using a frontend/backend split design?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  9. #9
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time lag (Access03)

    No

  10. #10
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time lag (Access03)

    <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> When you run your query from the database window, does it take that long?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  11. #11
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time lag (Access03)

    It takes 45 seconds to select and clear. I developed a new update query to select and it took 20 seconds. Fay

  12. #12
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time lag (Access03)

    Fay,

    With 52 records, it should only take a second to run. Try creating a new query. Show your table tblDepartments. Change to an update query. Drag your field (Requested) onto the QBE, and for Update To, put: 0.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  13. #13
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Time lag (Access03)

    Fay To update the whole table to 0's you could just update all to zero

    UPDATE tblDepartments SET tblDepartments.Requested = 0

    This may save a little time.
    Jerry

  14. #14
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time lag (Access03)

    I double checked and that is exactly what I have. I redid the query as you directed and the results are SLIGHTLY better. This makes no sense. I imported the table into a new database and the time is as slow. I have attached the database here. Maybe you can see the issue.

  15. #15
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Time lag (Access03)

    Fay

    Looking at this it appears quite strange that it should take that long. Is there anyway we can see the DB with private info removed...it should definitely not take this long for a simple query <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Jerry

Page 1 of 5 123 ... LastLast

Posting Permissions

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