插入数据以访问重复数据错误

Simon Staton 发表于 Dev

西蒙·斯塔顿

我写了一些vbscript来从另一个数据库中的表更新一个访问数据库中的所有新字段，但是我遇到了重复主键的问题。

我无法更改数据库的结构，因此无法删除主键，但是理想情况下，我希望它自动填充主键。这是我的表结构（它们有两个表）

表“订单”：

Order Sequence Number   About 20 more rows of data that do not have to be unique
a primary key e.g 2     other data

表格“ OrderDetail”：

OrderDetail     OrderSequence       Some other rows that don't need to be unique
a primary key   the key from Order  some other data

我的第一个问题是两个表的主键都自动填充，因此它们是唯一的，然后我的第二个问题是要添加的两行匹配，并且如果“订单”表上“订单序列号”的主键发生了更改，则会对其进行更新表“ OrderDetail”中的“ OrderSequence”上。

这是我的vbscript，如果值唯一，则可以复制它们：

Public Function dhupdate1()

    'Temp field
    Dim fField As Field
    Dim bCopy As Boolean

    'Open source database
    Dim dSource As Database
    Set dSource = CurrentDb

    'Open dest database
    Dim dDest As Database
    Set dDest = DAO.OpenDatabase("\\BMCDONALD-PC\SellerDeck 2013\Sites\New_Site\ActinicCatalog.mdb")

    'Open source recordset
    Dim rSource As Recordset
    Set rSource = dSource.OpenRecordset("OrderDetail", dbOpenForwardOnly)

    'Open dest recordset
    Dim rDest As Recordset
    Set rDest = dDest.OpenRecordset("OrderDetail", dbOpenDynaset)

    'Loop through source recordset
    While Not rSource.EOF

        'Reset copy flag
        bCopy = False

        'Look for record in dest recordset
        rDest.FindFirst "OrderDetailID = " & rSource.Fields("OrderDetailID") & ""

        If rDest.NoMatch Then
           'If not found, copy record
            rDest.AddNew
            bCopy = True
        End If

        'If copy flag is set, copy record
        If bCopy Then
            For Each fField In rSource.Fields
                rDest.Fields(fField.Name) = rSource.Fields(fField.Name)
            Next fField
            Set fField = Nothing
            rDest.Update
        End If

        'Next source record
        rSource.MoveNext
    Wend

    'Close dest recordset
    rDest.Close
    Set rDest = Nothing

    'Close source recordset
    rSource.Close
    Set rSource = Nothing

    'Close dest database
    dDest.Close
    Set dDest = Nothing

    'Close source database
    dSource.Close
    Set dSource = Nothing

End Function
Public Function dhupdate2()

   'Temp field
    Dim fField As Field
    Dim bCopy As Boolean

    'Open source database
    Dim dSource As Database
    Set dSource = CurrentDb

    'Open dest database
    Dim dDest As Database
    Set dDest = DAO.OpenDatabase("\\BMCDONALD-PC\SellerDeck 2013\Sites\New_Site\ActinicCatalog.mdb")

    'Open source recordset
    Dim rSource As Recordset
    Set rSource = dSource.OpenRecordset("Order", dbOpenForwardOnly)

    'Open dest recordset
    Dim rDest As Recordset
    Set rDest = dDest.OpenRecordset("Order", dbOpenDynaset)

    'Loop through source recordset
    While Not rSource.EOF

        'Reset copy flag
        bCopy = False

        'Look for record in dest recordset
        rDest.FindFirst "[Order Number] = '" & rSource.Fields("Order Number") & "'"

        If rDest.NoMatch Then
           'If not found, copy record
            rDest.AddNew
            bCopy = True
        End If

        'If copy flag is set, copy record - ignore errors
        If bCopy Then
            For Each fField In rSource.Fields
                On Error Resume Next
                rDest.Fields(fField.Name) = rSource.Fields(fField.Name)
                On Error GoTo 0
            Next fField
            Set fField = Nothing
            rDest.Update
        End If

        'Next source record
        rSource.MoveNext
    Wend

    'Close dest recordset
    rDest.Close
    Set rDest = Nothing

    'Close source recordset
    rSource.Close
    Set rSource = Nothing

    'Close dest database
    dDest.Close
    Set dDest = Nothing

    'Close source database
    dSource.Close
    Set dSource = Nothing

End Function

我一直在阅读有关自动填充（如果不是唯一的）的信息，但是我很困惑，在哪里我需要这两个函数来获取一个订单的两行并更改订单序列的两个数字。我对VB还是很陌生，因此非常感谢您的帮助。

谢谢西蒙

戈德·汤普森

您无需一次将每个表的所有记录从“源”复制到“目的地”，而是可以遍历父记录，为每次迭代复制一个父记录及其相关的子记录。那是：

复制父记录1
将子记录复制为父记录1
复制父记录2
将子记录复制为父记录2
...等等。

以下示例代码可能会有所帮助：

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

编辑：新信息

子表中的主键不是自动编号，因此您只需要“自己滚动”就可以了。请尝试以下操作（标记为的更改<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