X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Finding Matching Records In Another Table., Access 2016    
post Nov 1 2017, 10:59 AM

Posts: 1,088
Joined: 1-December 12

I want to update records in one table based on records in another.

I've always use a do loop construct to do this, but, I'm wondering if using a Do ...Loop and docmd or SQL statement is quicker?

this is what I'm trying:
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset

On Error GoTo ErrHan

Set dbs = CurrentDb

Set rs = dbs.OpenRecordset("tblIsleInfo")
Set rs2 = dbs.OpenRecordset("tblAisleWithProductInfo")

    docmd.SearchForRecord,[tblAislewithProductInfo],acFirst,rs![storagelocation]& " =  " & rs2![StorageLocation]

Loop While Not rs.EOF

    Set dbs = Nothing
    Exit Sub
    MsgBox Err.Description, vbInformation, "Error "
    Resume ExitErr
End Sub

Do you thing I'm on the right track or should I
Go to the top of the page
Doug Steele
post Nov 1 2017, 02:51 PM

UtterAccess VIP
Posts: 21,916
Joined: 8-January 07
From: St. Catharines, ON (Canada)

Using a query that joins the two tables will likely be significantly faster.

Doug Steele, Microsoft Access MVP (2000-2018)
Co-author: Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs, published by Wiley
Co-author: Effective SQL: 61 Specific Ways to Write Better SQL, published by Addison-Wesley Professional
Technical Editor: Access 2010 Bible, Access 2013 Bible, Access 2016 Bible, all published by Wiley
Technical Editor: SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, 4th Edition, published by Addison-Wesley Professional
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    24th September 2018 - 04:17 PM