Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 367
  • Last Modified:

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.
0
ITworks
Asked:
ITworks
  • 3
  • 3
1 Solution
 
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
 
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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
 
Helen FeddemaCommented:
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
 
ITworksAuthor Commented:
Wow!  Nothing like I expected.  So where do I place this?
0
 
Helen FeddemaCommented:
In any standard module
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now