none
Adding new, unique records from one table to another. RRS feed

  • Question

  • I apologize for repeating this question, but I’ve spent many hours searching and have not found any solution that could work for me that I understand.   I’m trying to use an append query.

    Consider this scenario:  I have a copy of the same MS Access database installed on three computers (A, B & C).  Data is collected via all three computers. Computer C is considered the Master.  On a regular basis, I want to append the data from computers A & B into the table on computer C.  I also need to maintain a unique ID for each of the records once consolidated on “C”.  The collection table is named ‘tblData’.

    For learning purposes, the ‘tblData’ fields are “LocalID, RecordID, ComputerID, RecordDate, PartName, Error.  LocalID is the table autonum (not sure it’s required), RecordID (unique number across all three computers -autonum?), ComputerID (computer identification (A,B,C), RecordDate (new record date & time), PartName, and Error.  I’m tracking occurrences of and different errors in different parts.

    So, Is there a way I can aggregate the records together and keep then unique with a continuous running unique RecordID?  I believe my unique fields for aggregating are “ComputerID & RecordDate”.

    Any suggestions, pointers, and references appreciated.!!!

    TIA,
    - Pat


    • Edited by PSD1953 Tuesday, September 12, 2017 12:32 AM Added context to question
    Tuesday, September 12, 2017 12:28 AM

Answers

  • If the primary key of the target table is an autonumber then provided that the table has another candidate key, of one or more columns, a simple 'append' query should suffice as any rows which violate the candidate key will be rejected.  On the other hand, to reliably maintain a continuous sequence of integer numbers as a table's primary key the number should be computed when each row is inserted.  When inserting multiple rows from another table, therefore, rather than doing so as a single set operation, it can be done by looping through the set of rows to be inserted, executing an INSERT INTO statement to insert one row, and incrementing the ID number by 1, at each iteration of the loop.

    You'll find an example in CustomNumber.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file the option to insert multiple rows uses a multi-select list box in which any number of items can be selected and inserted into a Product table.  The code for this is:

        Const KEYVIOLATION = 3022
        Dim ctrl As Control
        Dim varItem As Variant
        Dim strSQL As String
        Dim strMessage As String
        Dim lngNextID As Long
        Dim lngSeed As Long
        Dim intCurrentRows As Integer
        Dim intNewRows As Integer
        Set ctrl = Me.lstTools
        
        lngNextID = Nz(DMax("ProductID", "Product"), 0) + 1
        lngSeed = Nz(DLookup("Seed", "Seeds"), 0)
        intCurrentRows = DCount("*", "Product")
        
        If lngSeed > lngNextID Then
            lngNextID = lngSeed
        End If
       
        For Each varItem In ctrl.ItemsSelected
            strSQL = "INSERT INTO Product(ProductID, ProductName, Category) " & _
                " SELECT " & lngNextID & ", Tool, Category " & _
                "FROM Tools WHERE Tool = """ & ctrl.ItemData(varItem) & """"
                
            On Error Resume Next
            CurrentDb.Execute strSQL, dbFailOnError
            Select Case Err.Number
                Case 0
                ' no error
                Case KEYVIOLATION
                Do While Err.Number <> 0
                    lngNextID = DMax("ProductID", "Product") + 1
                    strSQL = "INSERT INTO Product(ProductID, ProductName, Category) " & _
                        "SELECT " & lngNextID & ", Tool, Category " & _
                        "FROM Tools WHERE Tool = """ & ctrl.ItemData(varItem) & """"
                        CurrentDb.Execute strSQL, dbFailOnError
                    Err.Clear
                Loop
                Case Else
                ' unknown error
                MsgBox Err.Description, vbExclamation, "Error"
                Exit Sub
            End Select
            
            lngNextID = DMax("ProductID", "Product") + 1
            
        Next varItem
        
        intNewRows = DCount("*", "Product")
        strMessage = (intNewRows - intCurrentRows) & _
            " rows were inserted."
        MsgBox strMessage, vbInformation, "Confirmation"
        
    It would not be difficult to amend this to loop through a recordset based on one of the external tables, once you are able to establish a link with the external database.  To reject duplicates, one or more columns in the target table, other than the numeric primary key column, must be a candidate key, indexed uniquely.  In my demo it is the ProductName column in the Product table.  You have identified ComputerID and RecordDate as a composite candidate key, so these two columns should be included in a single unique index.

    Ignore the code in my demo which allows the next number to be seeded.  This is not relevant to what you are attempting.

    In your case you seem to be concerned with a single table in each database, so the above method would suffice.  Usually, of course, multiple related tables would be involved.  This makes the process of reconciling two sets of related tables rather more difficult.  The DecomposerDemo file in my same OneDrive folder illustrates how a single non-normalized table, imported from Excel in my case, can be decomposed into correctly normalized related tables.  If there were a need to reconcile multiple tables, rather than a single table, therefore, it would be possible to use the methodology illustrated in that demo by appending the result table of a query which joins all the related tables from the source database.  Note that this demo does not compute the primary key values, however, but uses autonumbers, which is in most situations sufficient, as the sole purposed of a surrogate numeric key is to distinctly identify each row.  The maintenance of an unbroken sequence is very rarely necessary, and in most cases no more than a superficial neatness with no semantic implications.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Tuesday, September 12, 2017 4:46 PM Clarification.
    • Marked as answer by PSD1953 Wednesday, September 13, 2017 12:49 AM
    Tuesday, September 12, 2017 4:38 PM

All replies

  • Can I ask why you wouldn't split the database and then distribute a copy of the front-end to each user and then they can all work on the same centralized back-end (set of data)?

    You may benefit from reviewing: http://www.devhut.net/2017/04/09/setting-up-an-ms-access-database/


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net



    Tuesday, September 12, 2017 1:20 AM
  • Hi Pat,

    Could you share us a screen shot about tblData table design and demo records? It seems you have the same tblData in three computers? Do you have any trouble to append records in the same computer with three Access database?

    I agree with Daniel, if the backends are the same, you may consider splitting the database.

    I found you have posted multiple threads before and did not come back, I suggest you check your below threads, if they are resolved, I would suggest you mark the helpful reply as answer. If not, please feel free to keep following.

    #How to deployment MS Access frontend/backend system when not on a company intranet

    https://social.msdn.microsoft.com/Forums/office/en-US/ffea7094-89f1-4e62-b19d-20746a75bff4/how-to-deployment-ms-access-frontendbackend-system-when-not-on-a-company-intranet?forum=accessdev

    #OneDrive and MS Access Backend/Frontend Configuration

    https://social.msdn.microsoft.com/Forums/office/en-US/97c159f3-1e33-483c-84d0-4d723d9b9526/onedrive-and-ms-access-backendfrontend-configuration?forum=accessdev

    Best Regards,

    Edward 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 12, 2017 1:54 AM
  • Having a front-end / back-end system was my original plan until I realized that I do not network availability most times.  Also, I don't have an Intranet capability and was told that "OneDrive" would not work with a front-end / back-end system.  I'm working on plan "C".  Hopefully I can get something to work.

    Thank you for your link, which I bookmarked!

    - Pat

    Tuesday, September 12, 2017 1:25 PM
  • How about using an INSERT statement to copy the data from your other tables to table "C". In the below example you would open the database connection to Table "C" and the SQL statement copies from Table "A".

    INSERT INTO tblData
    SELECT *
    FROM [MS Access;DATABASE=<filepath>\TableA.accdb;].tblData AS st
    WHERE (((Exists (SELECT 1 FROM tblData dt WHERE st.ComputerID = dt.ComputerID AND st.RecordDate = dt.RecordDate))=False));


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, September 12, 2017 4:13 PM
  • If the primary key of the target table is an autonumber then provided that the table has another candidate key, of one or more columns, a simple 'append' query should suffice as any rows which violate the candidate key will be rejected.  On the other hand, to reliably maintain a continuous sequence of integer numbers as a table's primary key the number should be computed when each row is inserted.  When inserting multiple rows from another table, therefore, rather than doing so as a single set operation, it can be done by looping through the set of rows to be inserted, executing an INSERT INTO statement to insert one row, and incrementing the ID number by 1, at each iteration of the loop.

    You'll find an example in CustomNumber.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file the option to insert multiple rows uses a multi-select list box in which any number of items can be selected and inserted into a Product table.  The code for this is:

        Const KEYVIOLATION = 3022
        Dim ctrl As Control
        Dim varItem As Variant
        Dim strSQL As String
        Dim strMessage As String
        Dim lngNextID As Long
        Dim lngSeed As Long
        Dim intCurrentRows As Integer
        Dim intNewRows As Integer
        Set ctrl = Me.lstTools
        
        lngNextID = Nz(DMax("ProductID", "Product"), 0) + 1
        lngSeed = Nz(DLookup("Seed", "Seeds"), 0)
        intCurrentRows = DCount("*", "Product")
        
        If lngSeed > lngNextID Then
            lngNextID = lngSeed
        End If
       
        For Each varItem In ctrl.ItemsSelected
            strSQL = "INSERT INTO Product(ProductID, ProductName, Category) " & _
                " SELECT " & lngNextID & ", Tool, Category " & _
                "FROM Tools WHERE Tool = """ & ctrl.ItemData(varItem) & """"
                
            On Error Resume Next
            CurrentDb.Execute strSQL, dbFailOnError
            Select Case Err.Number
                Case 0
                ' no error
                Case KEYVIOLATION
                Do While Err.Number <> 0
                    lngNextID = DMax("ProductID", "Product") + 1
                    strSQL = "INSERT INTO Product(ProductID, ProductName, Category) " & _
                        "SELECT " & lngNextID & ", Tool, Category " & _
                        "FROM Tools WHERE Tool = """ & ctrl.ItemData(varItem) & """"
                        CurrentDb.Execute strSQL, dbFailOnError
                    Err.Clear
                Loop
                Case Else
                ' unknown error
                MsgBox Err.Description, vbExclamation, "Error"
                Exit Sub
            End Select
            
            lngNextID = DMax("ProductID", "Product") + 1
            
        Next varItem
        
        intNewRows = DCount("*", "Product")
        strMessage = (intNewRows - intCurrentRows) & _
            " rows were inserted."
        MsgBox strMessage, vbInformation, "Confirmation"
        
    It would not be difficult to amend this to loop through a recordset based on one of the external tables, once you are able to establish a link with the external database.  To reject duplicates, one or more columns in the target table, other than the numeric primary key column, must be a candidate key, indexed uniquely.  In my demo it is the ProductName column in the Product table.  You have identified ComputerID and RecordDate as a composite candidate key, so these two columns should be included in a single unique index.

    Ignore the code in my demo which allows the next number to be seeded.  This is not relevant to what you are attempting.

    In your case you seem to be concerned with a single table in each database, so the above method would suffice.  Usually, of course, multiple related tables would be involved.  This makes the process of reconciling two sets of related tables rather more difficult.  The DecomposerDemo file in my same OneDrive folder illustrates how a single non-normalized table, imported from Excel in my case, can be decomposed into correctly normalized related tables.  If there were a need to reconcile multiple tables, rather than a single table, therefore, it would be possible to use the methodology illustrated in that demo by appending the result table of a query which joins all the related tables from the source database.  Note that this demo does not compute the primary key values, however, but uses autonumbers, which is in most situations sufficient, as the sole purposed of a surrogate numeric key is to distinctly identify each row.  The maintenance of an unbroken sequence is very rarely necessary, and in most cases no more than a superficial neatness with no semantic implications.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Tuesday, September 12, 2017 4:46 PM Clarification.
    • Marked as answer by PSD1953 Wednesday, September 13, 2017 12:49 AM
    Tuesday, September 12, 2017 4:38 PM