sql - Invalid Argument - Error 3001 when using VBA to copy records to an archive table - Stack Overflow

admin管理员组

文章数量:1410717

Due to everyone getting away from using share drives in favor of SharePoint, I have had to start developing or migrating my databases to use SharePoint Lists as the back end (please don't roast me... I'd prefer a MS SQL backend but it's a lot of hassle with our IT dept and SharePoint permissions make accessing data pretty easily controlled).

Regardless, I used to use a BEFORE UPDATE event to copy data from one table to serve as a log of changes and it was working fine. Once I migrated to SharePoint - a number of things broke which I have been going through and fixing. I can't seem to get the copy from one table to another to work properly and it's driving me nuts.

At first, I tried to use a simple Insert Command as follows on the Before Update Event, but it failed with all sorts of weird type errors that we inaccurate (data types between each table were identical, and it works in a normal split database setup; and SharePoint doesn't always trigger the Before Update events properly from my search anyways:

DoCmd.RunSQL "INSERT INTO tTalkingPointsChangeLog (idTalkingPoints, TalkingPoint, intendedDate, ActualDate, Response, InternalComments, idMicroTopics, finalApproved, lastEditBy, revisionX, revisionY, revisionZ, changeReason, isIWS1, isIWS2) " & _
             "SELECT idTalkingPoints, TalkingPoint, intendedDate, ActualDate, Response, InternalComments, idMicroTopics, finalApproved, '" & lastEditor & "', revisionX, revisionY, revisionZ, changeReason, isIWS1, isIWS2 " & _
             "FROM tTalkingPoints WHERE idTalkingPoints=" & idTalkingPoints
ValidateBeforeUpdate = True

So, I tried a little more of a brute force method:

Private Sub Form_Load()
    Dim NewID As Long, TalkingPointID As Long
    Dim rsOld As Recordset, rsNew As Recordset
    Dim fld As Field

    If IsNull(idTalkingPoints) Then Exit Sub
    Me.Refresh
    
    ' copy the talking point
    
    Set rsOld = CurrentDb.OpenRecordset("SELECT * FROM tTalkingPoints WHERE idTalkingPoints=" & idTalkingPoints)
    Set rsNew = CurrentDb.OpenRecordset("tTalkingPointsChangeLog")
    
    rsNew.AddNew

    For Each fld In rsOld.Fields
        If fld.Name = "isSelected" Then
        ElseIf fld.Name = "rankOrder" Then
        ElseIf fld.Name = "internalSPAApproved" Then
        ElseIf fld.Name = "internalClientApproved" Then
        ElseIf fld.Name = "isCore" Then
        ElseIf fld.Name = "TalkingPointReference" Then
        ElseIf fld.Name = "releasability" Then
        ElseIf fld.Name = "archived" Then
        Else
            rsNew(fld.Name) = rsOld(fld.Name)
        End If
    Next
    
    rsNew.Update
    rsNew.Close
    rsOld.Close
End Sub

Unfortunately, now I get a "Run-time error '3001': Invalid argument."

Debugger is stopping at the rsNew.Update line; I have used this in another function to duplicate records within the same table; so I am unsure if I missing something or if that's the correct command at all. Any help, much appreciated!

I have validated the loop works properly using toggle points, and verified that a record is not being created in the table. The error is stopping progress.

Due to everyone getting away from using share drives in favor of SharePoint, I have had to start developing or migrating my databases to use SharePoint Lists as the back end (please don't roast me... I'd prefer a MS SQL backend but it's a lot of hassle with our IT dept and SharePoint permissions make accessing data pretty easily controlled).

Regardless, I used to use a BEFORE UPDATE event to copy data from one table to serve as a log of changes and it was working fine. Once I migrated to SharePoint - a number of things broke which I have been going through and fixing. I can't seem to get the copy from one table to another to work properly and it's driving me nuts.

At first, I tried to use a simple Insert Command as follows on the Before Update Event, but it failed with all sorts of weird type errors that we inaccurate (data types between each table were identical, and it works in a normal split database setup; and SharePoint doesn't always trigger the Before Update events properly from my search anyways:

DoCmd.RunSQL "INSERT INTO tTalkingPointsChangeLog (idTalkingPoints, TalkingPoint, intendedDate, ActualDate, Response, InternalComments, idMicroTopics, finalApproved, lastEditBy, revisionX, revisionY, revisionZ, changeReason, isIWS1, isIWS2) " & _
             "SELECT idTalkingPoints, TalkingPoint, intendedDate, ActualDate, Response, InternalComments, idMicroTopics, finalApproved, '" & lastEditor & "', revisionX, revisionY, revisionZ, changeReason, isIWS1, isIWS2 " & _
             "FROM tTalkingPoints WHERE idTalkingPoints=" & idTalkingPoints
ValidateBeforeUpdate = True

So, I tried a little more of a brute force method:

Private Sub Form_Load()
    Dim NewID As Long, TalkingPointID As Long
    Dim rsOld As Recordset, rsNew As Recordset
    Dim fld As Field

    If IsNull(idTalkingPoints) Then Exit Sub
    Me.Refresh
    
    ' copy the talking point
    
    Set rsOld = CurrentDb.OpenRecordset("SELECT * FROM tTalkingPoints WHERE idTalkingPoints=" & idTalkingPoints)
    Set rsNew = CurrentDb.OpenRecordset("tTalkingPointsChangeLog")
    
    rsNew.AddNew

    For Each fld In rsOld.Fields
        If fld.Name = "isSelected" Then
        ElseIf fld.Name = "rankOrder" Then
        ElseIf fld.Name = "internalSPAApproved" Then
        ElseIf fld.Name = "internalClientApproved" Then
        ElseIf fld.Name = "isCore" Then
        ElseIf fld.Name = "TalkingPointReference" Then
        ElseIf fld.Name = "releasability" Then
        ElseIf fld.Name = "archived" Then
        Else
            rsNew(fld.Name) = rsOld(fld.Name)
        End If
    Next
    
    rsNew.Update
    rsNew.Close
    rsOld.Close
End Sub

Unfortunately, now I get a "Run-time error '3001': Invalid argument."

Debugger is stopping at the rsNew.Update line; I have used this in another function to duplicate records within the same table; so I am unsure if I missing something or if that's the correct command at all. Any help, much appreciated!

I have validated the loop works properly using toggle points, and verified that a record is not being created in the table. The error is stopping progress.

• sql
• vba
• ms-access

Share Improve this question Follow edited Mar 11 at 14:42 GSerg 78.3k1717 gold badges172172 silver badges366366 bronze badges asked Mar 11 at 14:27 GatorAdmiral03GatorAdmiral03 6955 bronze badges 2

• Try Set rsNew = CurrentDb.OpenRecordset("tTalkingPointsChangeLog",DbOpenDynaSet) – ValNik Commented Mar 11 at 15:31
• Thanks! I appreciate the recommendation. The SharePoint meta columns ended up screwing this up. Since I let the foreach loop do its thing on all columns except those I told it to omit with the Else If lines it was causing issues there. Once I explicitly omitted those additional columns with Else If statements it ended up working. – GatorAdmiral03 Commented Mar 11 at 18:36

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

This has worked for me in many variations, though never tested with Sharepoint lists:

Public Sub CopyRecord()
  
    Dim Source        As DAO.Recordset
    Dim Insert        As DAO.Recordset
    Dim Field         As DAO.Field
    
    Set Source = CurrentDb.OpenRecordset("SELECT * FROM tTalkingPoints WHERE idTalkingPoints=" & idTalkingPoints, dbOpenDynaset)
    Set Insert = CurrentDb.OpenRecordset("Select * From tTalkingPointsChangeLog", dbOpenDynaset, dbAppendOnly)
    
    Insert.AddNew
    For Each Field In Source.Fields
        With Field
            If .Attributes And dbAutoIncrField Then
                ' Skip Autonumber or GUID field.
            Else
                Select Case .Name
                    ' List names of fields to ignore.
                    Case "SomeField", "AnotherField", "YetAField"
                        ' Skip field.
                    Case Else
                        ' Copy field content.
                        Insert.Fields(.Name).Value = Source.Fields(.Name).Value
                End Select
            End If
        End With
    Next
    Insert.Update
    
    Insert.Close
    Source.Close
    
End Sub

本文标签： sqlInvalid ArgumentError 3001 when using VBA to copy records to an archive tableStack Overflow