Adding a complete row to a table from a DAO.Recordset

Question

I have an access table containing product information with 100 Columns (it is a bought system so not of my creation). I want to be able to copy a row and insert it as a new row with 3 fields updated. I am using VBA on an access DB.

I am selecting the row I want to copy (Select Product.* FROM .....) and putting it into a DAO.Recordset. This works fine. I then want to insert this data back into the table as a new row with all the same data apart from the Product ID (key) and the Product short description.

As there is 100 columns I am trying to avoid typing in all the column names and assigning the values individually. Is there a way to insert from a DAO.Recordset so I can avoid typing in all the columns? if not is there another way to avoid typing in all the columns and all the values? It would save me a very big insert statement!

Many thanks

Tony

Answer 1

You can loop the Fields collection of the recordset to do this.

This approach may be more maintainable then a giant INSERT statement if the table structure changes from time to time.

If the table is static, I would rather use a saved INSERT query with parameters for the columns that are modified.

Sub CopyProductRow()

    Dim rsSrc As DAO.Recordset
    Dim rsTgt As DAO.Recordset
    Dim fld As DAO.Field
    Dim sFld As String

    Set rsSrc = CurrentDb.OpenRecordset("SELECT * FROM Products WHERE ProductID = 4711", dbOpenSnapshot)
    Set rsTgt = CurrentDb.OpenRecordset("Products", dbOpenDynaset, dbAppendOnly)

    rsTgt.AddNew

    For Each fld In rsSrc.Fields
        sFld = fld.Name
        Select Case sFld
            ' special cases
            Case "ProductID": rsTgt(sFld).Value = GetNewProductID()
                              ' or if ProductID is AutoNumber, don't assign anything
            Case "ProductDesc": rsTgt(sFld).Value = "my new description"
            ' all other field values are simply copied
            Case Else: rsTgt(sFld).Value = fld.Value
        End Select
    Next fld

    rsTgt.Update
    rsTgt.Close
    rsSrc.Close

End Sub

Answer 2

If you use a form where you select the record to copy, you can also use the RecordsetClone:

Copy selected record to new record

Answer 3

If you are trying to insert it back into the same table then you can do it without puling it into a recordset at all.

You just need to write the correct SQL query and execute it.

The data that you select will be the recordset that you are already pulling with the updated values.

For instance:

INSERT INTO tableX(field1, productId, productDesc)
SELECT field1, 777 as productId, "NewString" as productDesc
FROM tableX
WHERE productId=7

Another approach which I mentioned in comments would be to loop through each of the fields to build your string used as the SQL command, the execution of this would be a lot faster then processing record by record. (such as inserting a new product for each order in an orders table where another product has already been ordered, which could have 10s of 1000s of orders)

'Edited the code supplied by another response above'
Sub CopyProductRow()

    Dim sFld, iField, sqlQuery As String

    i= "INSERT INTO products("
    s= "SELECT "
    w= "WHERE ProductID = 7"
    For Each fld In rsSrc.Fields
        Select Case fld.Name 'Check the field name'
            ' special cases'
            Case "ProductID": 'If field is Product ID'
                  iFld = "777 as ProductID" '777 will be the product id returned by the select query (therefore inserted)'
            Case "ProductDesc": 'If field is Product Description '
                         'The description below will be selected / inserted instead.'
                  iFld = "'New Product Description' as ProductDesc"
            Case Else: 
                  iFld = fld.Name 'No change just select the field and insert as is.'
        End Select

        i = i & ", " & fld.Name
        s = s & ", " & iFld 
    Next fld

    'Build complete query (removing the first comma in the select statement)'
    sqlQuery = i & ") " & replace(s, "SELECT ,", "SELECT ") & " " &w

    'Resulting in a string such as the following'
    'INSERT INTO products(field1,field2, productId, productDesc, field5, ...)'
    'SELECT field1, field2, 777 as ProductID, 'New Product Description' as ProductDesc, field5, ...'
    'FROM tableX'
    'WHERE productId=7'

    'Print the query to immediate window
    Debug.print sqlQuery

    'Execute the query'
    currentdb.execute sqlQuery
End Sub