Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2007
    Posts
    148

    Unanswered: improve query speed!!

    This accessdatabase is a growing database. My following routine works but SLOW!!!. Anyone has any better table update method so that this routine would not have to take so long to run.

    Thank you

    ************************************************** ***

    Set rsRevise = dbRevise.OpenRecordset("Select * FROM tblChangeControlFormDetails ", dbOpenDynaset, dbSeeChanges, dbOptimistic)


    rsRevise.MoveFirst

    If Not rsRevise.EOF Then

    Do

    CurrentDb.Execute ("UPDATE tblChangeControlFormDetails SET SearchPrintReport = True where ((len(nz(Approvedate))=0) and (len(nz(denieddate))=0) and (len(nz(ChangeControlFormNum)) <> 0 ))")


    rsRevise.MoveNext
    Loop While rsRevise.EOF = False

    End If

    rsRevise.Close


    Set rsRevise = Nothing

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Why are you nesting a query into a loop? A simple UPDATE query would do the job. Here you execute the UPDATE query as many times as there are rows in your table while you could execute it only once.

    See: http://office.microsoft.com/en-us/ac...765271033.aspx

    Have a nice day!

  3. #3
    Join Date
    Sep 2007
    Posts
    148
    Thank you Sinndho,

    The link was very good. Now, it takes no time for the report to come up.

    Thank you

Posting Permissions

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