Solved

Append or Update - Access 2010

Posted on 2013-11-14
7
328 Views
Last Modified: 2013-11-20
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
Comment
Question by:ITworks
  • 3
  • 3
7 Comments
 
LVL 84
Comment Utility
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
 
LVL 4

Author Comment

by:ITworks
Comment Utility
Each table uses the LOT # as the reference.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 4

Author Comment

by:ITworks
Comment Utility
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
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 500 total points
Comment Utility
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
 
LVL 4

Author Comment

by:ITworks
Comment Utility
Wow!  Nothing like I expected.  So where do I place this?
0
 
LVL 31

Expert Comment

by:Helen_Feddema
Comment Utility
In any standard module
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Correct ordering 14 43
Supress Detail 4 17
Trying to open a report with a filter 2 16
subform does update on ms/access 2 14
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now