append query with sequence number

Question

I want to append records from Table 1 to Table 2. The sequence number field will be added 1 with the max sequence number in Table 2 for each record appended.

So, I create an append query and set :

            Seq : (Select Max(Seq) + 1  From [Table 2])

When I run the append query, It can not append all the records to Table 2 at one time, but one by one. Specifically:

• Each time I run the query, there is an error : "Cannot Update. Data or object is read-only". When I click "OK" to exit and check in Table 2, however, 1 records is added
• I continue to run the query until with above error until all records are appended to Table 2 (In the last time, there is no notification for error). For example, if there are 5 records in Table 1, I have to run 5 times (4 first times with errors notification and last time without errors)

What should I do to append all records at one time ?

Thks

Answer 1

As you have found out an append query is a set operation.  To do this sort of thing the set has to have one member only, so you need to execute the query multiple times, appending one row each time.  This is done by executing the INSERT INTO statement in a loop.  You'll find an example in CustomNumber.zip in my public databases folder at:

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

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

This little demo file includes an illustration of how to insert sequentially numbered multiple rows, in this case one for each item selected in a multi-select list box.  The code to do this is as follows:

Private Sub cmdInsert_Click()

    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"

End Sub

Answer 2

Thank you very much

Answer 3

Assuming this is a one time deal, another way to do this without coding is to use a MakeTable query to get the records you want. Add to fields to the resulting table, One an Autonumber, the other a Long Integer. Next, Run an Update query to update the Long Integer field with the Autonumber plus the max # from the other table. Something like

UPDATE table SET SEQ = Autonumber+5000;

Then run an Append query to Append these records to your regular table. You can then delete the Make Table.

Answer 4

Remove the unique index on the ProductName column in the Product table.

Answer 5

Thank you very much

In this demo file, only the Tools in listbox which are not in the table "Product" will be added. If I want to insert all the rows in the listbox to table "Products" despite that they are already in the table or not, so how can I revise the code or somewhere else.