Append or Update - Access 2010

I have a database that has 50,000 records.  There are 82 columns in the db, one of which is a Lot #, that can't be duplicated and one that displays the path to a scanned document.  Of those 50k records only a hundred of them have a path.  I had an excel file that has 4k lots that I have the path info.  So I imported that Excel file and created a new table.  What's the easiest way to get all the paths from the new table into the original table?  Would that be an append query?  Remember, I have about 100 records that already have the path info.  Or is this an update query?  I've never done either so don't know where to start.
LVL 4
ITworksAsked:
Who is Participating?
 
Helen FeddemaConnect With a Mentor Commented:
Here is the procedure:

Public Sub GetPaths()
'Created by Helen Feddema 14-Nov-2013
'Last modified by Helen Feddema 14-Nov-2013
'Assumes that the table and query are both ordered by P21Lot
'If the table is not ordered by P21Lot, make a query that is and use in place of table

On Error GoTo ErrorHandler

   Dim rstSource As DAO.Recordset
   Dim rstTarget As DAO.Recordset
   Dim strPath As String
   Dim strLot As String
   Dim strSearch As String
   
   Set rstSource = CurrentDb.OpenRecordset("Append_Doc_Links", dbOpenDynaset)
   Set rstTarget = CurrentDb.OpenRecordset("qryMTRNoPaths", dbOpenDynaset)
   
   Do While Not rstTarget.EOF
      strLot = rstTarget![P21Lot]
      strSearch = "[P21Lot] = " & Chr(39) & strLot & Chr(39)
      Debug.Print "Search string: " & strSearch
      rstSource.FindFirst strSearch
      
      If rstSource.NoMatch = False Then
        strPath = Nz(rstSource![OriginalScan])
         rstTarget.Edit
         rstTarget![OriginalScan] = strPath
         rstTarget.Update
      End If
      
      rstTarget.MoveNext
   Loop
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in GetPaths procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit
   
End Sub

Open in new window

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
An APPEND query would add new records to the destination table. An UPDATE query would update existing records only.

How are the two tables related? That is, how do you know which Record in your imported Excel table should be used to update a specific record in your existing table (the one with 50k records)?
0
 
ITworksAuthor Commented:
Each table uses the LOT # as the reference.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Helen FeddemaCommented:
There are a few ways you could do this.  One would be to make a query filtered for the large table records that lack a path, then iterate through that recordset, and for each record, search for the matching record in the table imported from Excel, get the path from there, and put it into the big table.

You would need to declare two recordsets for this purpose.  I can provide some sample code if you give me the table and field names.
0
 
ITworksAuthor Commented:
The LARGE table is called 'tblMTR'.  The small table that has the path for the documents is called 'Append_Doc_Links'.

Both tables has a field called 'P21Lot' which is the unique ID that links the 2 tables together.

In the Large table, the field where I need to store the path is called 'OriginalScan'
In the small table, I currently have the field named the same "OriginalScan", but I did have it named something else before when I attempted to merge them together.  

If either of these need renamed to something unique, just let me know.

I have both fields, from each table, set the same...meaning the same size, the same format (text), etc.
0
 
ITworksAuthor Commented:
Wow!  Nothing like I expected.  So where do I place this?
0
 
Helen FeddemaCommented:
In any standard module
0
All Courses

From novice to tech pro — start learning today.