Solved

Append or Update - Access 2010

Posted on 2013-11-14
7
347 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 84
ID: 39648468
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
ID: 39648494
Each table uses the LOT # as the reference.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 39649574
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 4

Author Comment

by:ITworks
ID: 39649610
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
ID: 39649793
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
ID: 39651110
Wow!  Nothing like I expected.  So where do I place this?
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 39651875
In any standard module
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

751 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