Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2011

    Unanswered: in Need of a faster search

    Hi all,

    I am using Access 2003 and want to make a faster search (if possible). The current code I have waits for the user to input first name, last name, MR #, and Occr Date. After the data is enter it search to make sure that it's not already in the database. Here is the code (any way of making it faster? It takes too long with 60,000 records):

    Dim rs As DAO.Recordset
    Dim iAns As Integer
    Set rs = Me.RecordsetClone
    rs.FindFirst "[MEDREC] = '" & Me!MEDREC & "' And [DOCCRS]= #" & Me!DOCCRS & "#"
    If Not rs.NoMatch Then
    iAns = MsgBox("This record already exists! Jump to it? Press (NO) to add", vbYesNo)
    If iAns = vbYes Then
    ' Clear the current form, jump to the record
    Cancel = True
    Me.Bookmark = rs.Bookmark
    'Just add it
    End If
    End If

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    define too long
    is it too long in system terms, is it unexpectedly long for you?
    check your columns are indexed?

    looking at the code Im not surprised it takes a long time. I think you should revise the algolrhythm to use either a SQL clause or possibly the domain function Dlookup. Dlookup to is alwasy iffy as it is a resorce pig and SQL is quicker, but if you prefer usign DLookup do so.

    consider using a SQL statement with a where clause. you then need to execute the SQL, forget how you do that at present its docmd or run SQL.

    select my,column,list from mytable where medrec='" Me!MEDREC & "' And [DOCCRS]= #" & Me!DOCCRS & "#"

    having found if the record exists you could then bookmark you current position, set a filter on the recordset and apply the filter. rememebr if you apply a filter you may want to remove the filter in subsequent operations.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Provided Answers: 14
    If what you're looking for is in a table, the fastest result would be obtained by opening a Table type recordset and using indexes to search for a record.
    Have a nice day!

Posting Permissions

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