Solved

Append or Update - Access 2010

Posted on 2013-11-14
7
343 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
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

762 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