Results 1 to 6 of 6
  1. #1
    arjun5381 is offline Novice
    Windows 8 Access 2007
    Join Date
    May 2016
    Posts
    12

    MS Access VBA Search Result Performance Improvement

    Dear All,

    I need your help for MS Access VBA Search Result Performance Improvement, below is the code which i used, i want to get the result based on string matching on % basis, this code is correct but it is taking too much time to perform almost 25 Minutes To 30 Minutes for Single String.

    I have 3 tables
    (1) MasterTable (Linked with SQL Server with 30 Lacs Records)
    a. OM_ACCT_NBR (Text Primary Key)
    b. Customer_Informations (Memo)
    (2) tblSearchString (Using this table as Search String for bulk search)
    a. Your_Search_String (Memo)
    (3) tblResults (Store Matched Details)
    a. OM_ACCT_NBR (Text)
    b. Matched_% (Text)
    c. Customer_Informations (Memo)

    Example : If my search string = “Arjun Singh Pune Maharashtra 511015 CRXPS0288X”, i want >80% string matching result on Result Table.
    This string will check one to one & Apple to Apple on Customer_Informations (Memo) Master Table.


    Private Sub Command53_Click()



    Dim varRet 'Variant Array to hold Elements of [Your_Search_String]
    Dim intCtr As Integer 'Used to Loop thru Elements of [Your_Search_String]
    Dim intNumOfMatches As Integer 'Number of Matches (Elements in [Your_Search_String]
    Dim intOverallCtr As Integer 'Overall Counter (Number of Elements in [Your_Search_String])
    Dim conPERCENTAGE As Single
    conPERCENTAGE = CInt(Me.txtMatchingPercentage) / 100 '> this Value on [Your_Search_String] Elements indicates Match
    Dim TempTextMatched As String

    Dim con As ADODB.Connection
    Set con = Application.CurrentProject.Connection
    Dim RsResult As Object
    Set RsResult = CreateObject("ADODB.Recordset")
    Dim RsSearchString As Object
    Set RsSearchString = CreateObject("ADODB.Recordset")
    Dim RsMasterTable As Object
    Set RsMasterTable = CreateObject("ADODB.Recordset")

    'Clear the Results Table
    CurrentDb.Execute "DELETE * FROM tblResults", dbFailOnError
    Me.Refresh

    RsSearchString.Open "Select * from tblSearchString", con, 1, 3, dbSeeChanges
    RsMasterTable.Open "Select * from De_Dupe_Final_db_Consolidated_Final_PercentMatchin g", con, adOpenForwardOnly, adLockReadOnly, adCmdText 'dbSeeChanges
    RsResult.Open "Select * from tblResults", con, 1, 3, dbSeeChanges

    With RsSearchString
    Do While Not .EOF
    Do While Not RsMasterTable.EOF

    TempTextMatched = ""

    varRet = Split(![Your_Search_String], " ")
    For intCtr = LBound(varRet) To UBound(varRet)
    intOverallCtr = intOverallCtr + 1
    If InStr(Replace(RsMasterTable![Customer_Informations], " ", ""), varRet(intCtr)) > 0 Then
    intNumOfMatches = intNumOfMatches + 1
    TempTextMatched = TempTextMatched & Trim(varRet(intCtr)) & " "
    End If
    Next

    'Do > 80% of Elements in [Your_Search_String] match [Customer_Informations]
    If (intNumOfMatches / intOverallCtr) >= conPERCENTAGE Then 'Percentage Criteria for a 'MATCH'
    RsResult.AddNew
    RsResult![OM_ACCT_NBR] = RsMasterTable![OM_ACCT_NBR]
    RsResult![Customer_Informations] = RsMasterTable![Customer_Informations]
    RsResult![Matched_%] = intNumOfMatches / intOverallCtr
    RsResult.Update
    End If
    intNumOfMatches = 0: intOverallCtr = 0 'RESET, critical
    RsMasterTable.MoveNext
    Loop
    RsMasterTable.MoveFirst
    .MoveNext
    Loop
    End With

    RsSearchString.Close
    Set RsSearchString = Nothing
    RsMasterTable.Close
    Set RsMasterTable = Nothing
    RsResult.Close
    Set RsResult = Nothing

    Set con = Nothing

    Me.Refresh

    End Sub

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Cross posted here: http://www.access-programmers.co.uk/...d.php?t=290079

    When you cross post, tell readers you have done so and provide the associated link(s).

    Here is why! http://www.excelguru.ca/content.php?184

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    OP has already been advised of the risks in a previous cross posting.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I think you're doing this with way more work than is necessary, try this:

    Arjun5381.zip

    The SQL can be built dynamically as can the match percentage, then simply compare the calculated percentage to the desired percentage and display the results in a list box (or instead of a select statement make it an append query and insert the records into a table).
    Attached Files Attached Files

  5. #5
    arjun5381 is offline Novice
    Windows 8 Access 2007
    Join Date
    May 2016
    Posts
    12
    Quote Originally Posted by rpeare View Post
    I think you're doing this with way more work than is necessary, try this:

    Arjun5381.zip

    The SQL can be built dynamically as can the match percentage, then simply compare the calculated percentage to the desired percentage and display the results in a list box (or instead of a select statement make it an append query and insert the records into a table).

    Dear rpeare,

    Thanks a lot for making a Access Macros for me, I just linked my SQL Server MasterTable in your Access Macros and Search a string

    "TATA HITACHICONSTRUCTION MACHINERY COMPANY LIMITED TELCO CONSTRUCTIONEQUIPMENT LTD 4066332069 9246397641 anand.pathak@telco.co.in CONSTRUCTION EQUIPMENT 5-3-338 2ND FLOOR ABOVE UTI BANK ROAD. HYDERABAD 500003 ROAD"

    with 80% matching it is taking almost 21 minutes, where in my Database Table having ~31 Lacs Record.

    Kindly suggest me some search engine mechanism which will give result quickly.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Also cross posted on other sites

    cross-posted at: https://social.msdn.microsoft.com/Forums/of...forum=accessdev where there are a few other ideas that have been proposed.

    and http://www.utteraccess.com/forum/index.php?showtopic=2040082


    See this comment from a UA volunteer who tried repeatedly to help the OP
    http://www.utteraccess.com/forum/index.php?showtopic=2040082&view=findpost&p=262033 6


    From my searching and reviews, I still don't think arjun5831 has acknowledged that he is cross posting. What I do see is that he is saying things like
    Dear David, Please respond, i need your help.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 03-26-2015, 11:08 AM
  2. Count Function Improvement Advice
    By asmolow in forum Queries
    Replies: 5
    Last Post: 03-22-2015, 08:45 AM
  3. Catch empty search result
    By octsim in forum Programming
    Replies: 5
    Last Post: 12-02-2013, 02:23 PM
  4. Return the result of a search
    By Loc in forum Programming
    Replies: 11
    Last Post: 06-12-2013, 06:23 PM
  5. Highlight key search result
    By uronmapu in forum Access
    Replies: 28
    Last Post: 06-17-2012, 09:32 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums