Inserting data to Access Duplicate Data Error

ms-access, ms-access-2007, vba

Solution

Instead of copying all records for each table from "source" to "dest" in one shot, you could loop through the parent records, copying one parent record and its related child records for each iteration. That is:

- copy parent record 1

- copy child records for parent record 1

- copy parent record 2

- copy child records for parent record 2

- ...and so on.

The following sample code may prove helpful:

Option Compare Database
Option Explicit

Public Function CopyOrders()
    Dim dSource As DAO.Database, rSourceOrder As DAO.Recordset, rSourceDetail As DAO.Recordset
    Dim dDest As DAO.Database, rDestOrder As DAO.Recordset, rDestDetail As DAO.Recordset
    Dim fld As DAO.Field, newDestOrderID As Long

    Set dSource = CurrentDb
    Set rSourceOrder = dSource.OpenRecordset("Order", dbOpenSnapshot)

    Set dDest = DAO.OpenDatabase("C:\__tmp\OrderCopy\dest.mdb")
    Set rDestOrder = dDest.OpenRecordset("Order", dbOpenDynaset)
    Set rDestDetail = dDest.OpenRecordset("OrderDetail", dbOpenDynaset)

    Do Until rSourceOrder.EOF
        ' copy one Order record
        rDestOrder.AddNew
        For Each fld In rDestOrder.Fields
            If fld.Name <> "OrderID" Then
                rDestOrder.Fields(fld.Name).Value = rSourceOrder.Fields(fld.Name).Value
            End If
        Next
        newDestOrderID = rDestOrder.Fields("OrderID").Value
        rDestOrder.Update  ' commit parent record so child records can be added

        ' now copy all related OrderDetail records
        Set rSourceDetail = dSource.OpenRecordset( _
                "SELECT * FROM OrderDetail " & _
                "WHERE OrderID=" & rSourceOrder!OrderID, _
                dbOpenSnapshot)
        Do Until rSourceDetail.EOF
            rDestDetail.AddNew
            ' use new AutoNumber from parent table (rDestOrder) as foreign key
            rDestDetail.Fields("OrderID").Value = newDestOrderID
            For Each fld In rDestDetail.Fields
                Select Case fld.Name
                    Case "OrderDetailID", "OrderID"
                        ' do nothing
                    Case Else
                        rDestDetail.Fields(fld.Name).Value = rSourceDetail.Fields(fld.Name).Value
                End Select
            Next
            rDestDetail.Update
            rSourceDetail.MoveNext
        Loop
        rSourceDetail.Close
        Set rSourceDetail = Nothing
        rSourceOrder.MoveNext
    Loop
    rDestDetail.Close
    Set rDestDetail = Nothing
    rDestOrder.Close
    Set rDestOrder = Nothing
    rSourceOrder.Close
    Set rSourceOrder = Nothing
    dDest.Close
    Set dDest = Nothing
    Set dSource = Nothing
End Function

edit re: new information

The primary key in the child table is not an AutoNumber, so you're right that you'll just have to "roll your own". Try the following (changes marked as `<v1.1>`):

Public Function CopyOrders()
    Dim dSource As DAO.Database, rSourceOrder As DAO.Recordset, rSourceDetail As DAO.Recordset
    Dim dDest As DAO.Database, rDestOrder As DAO.Recordset, rDestDetail As DAO.Recordset
    Dim fld As DAO.Field, newDestOrderID As Long
    Dim nextDestOrderDetailID As Long  ' <v1.1/>

    Set dSource = CurrentDb
    Set rSourceOrder = dSource.OpenRecordset("Order", dbOpenSnapshot)

    Set dDest = DAO.OpenDatabase("C:\Users\ANON\Documents\OrderMove\dh\ActinicCatalog.mdb")
    Set rDestOrder = dDest.OpenRecordset("Order", dbOpenDynaset)
    ' <v1.1>
    Set rDestDetail = dDest.OpenRecordset("SELECT Max(OrderDetailID) AS maxODI FROM OrderDetail", dbOpenSnapshot)
    nextDestOrderDetailID = Nz(rDestDetail!maxODI, 0) + 1
    rDestDetail.Close
    ' </v1.1>
    Set rDestDetail = dDest.OpenRecordset("OrderDetail", dbOpenDynaset)

    Do Until rSourceOrder.EOF
        ' copy one Order record
        rDestOrder.AddNew
        For Each fld In rDestOrder.Fields
            If fld.Name <> "Order Sequence Number" Then
                rDestOrder.Fields(fld.Name).Value = rSourceOrder.Fields(fld.Name).Value
            End If
        Next
        newDestOrderID = rDestOrder.Fields("Order Sequence Number").Value
        rDestOrder.Update  ' commit parent record so child records can be added

        ' now copy all related OrderDetail records
        Set rSourceDetail = dSource.OpenRecordset( _
                "SELECT * FROM OrderDetail " & _
                "WHERE OrderSequenceNumber=" & rSourceOrder![Order Sequence Number], _
                dbOpenSnapshot)
        Do Until rSourceDetail.EOF
            rDestDetail.AddNew
            ' use new AutoNumber from parent table (rDestOrder) as foreign key
            rDestDetail.Fields("OrderSequenceNumber").Value = newDestOrderID
            ' <v1.1>
            rDestDetail.Fields("OrderDetailID").Value = nextDestOrderDetailID
            nextDestOrderDetailID = nextDestOrderDetailID + 1
            ' </v1.1>
            For Each fld In rDestDetail.Fields
                'Select Case fld.Name
                 '   Case "OrderDetailID", "OrderSequenceNumber"
                        ' do nothing
                '    Case Else
                '        rDestDetail.Fields(fld.Name).Value = rSourceDetail.Fields(fld.Name).Value
                'End Select
                If fld.Name <> "OrderDetailID" Then
                    If fld.Name <> "OrderSequenceNumber" Then
                        rDestDetail.Fields(fld.Name).Value = rSourceDetail.Fields(fld.Name).Value
                    End If
                End If
            Next
            rDestDetail.Update
            rSourceDetail.MoveNext
        Loop
        rSourceDetail.Close
        Set rSourceDetail = Nothing
        rSourceOrder.MoveNext
    Loop
    rDestDetail.Close
    Set rDestDetail = Nothing
    rDestOrder.Close
    Set rDestOrder = Nothing
    rSourceOrder.Close
    Set rSourceOrder = Nothing
    dDest.Close
    Set dDest = Nothing
    Set dSource = Nothing
End Function

Problem

Original source