[AccessD] Copy a record in a table

Hi Rocky

Thanks. 
I got the idea that using Clone for opening the second recordset would be faster:

 - from

  Set rst = dbs.OpenRecordset("Select * From " & strTable & " Where " & strId & "=" & lngId & ";")
  Set rstAdd = dbs.OpenRecordset("Select Top 1 * From " & strTable & ";")

 - to

  Set rst = dbs.OpenRecordset("Select * From " & strTable & " Where " & strId & "=" & lngId & ";")
  Set rstAdd = rst.Clone

But it isn't; it runs about 5% slower.
On my 2.2 GHz machine the function runs in less than 20 ms.

/gustav

>>> bchacc at san.rr.com 15-11-2006 15:42 >>>
G:

I'm a big DAO fan.  Do lots of little things with it.  So I love the
routine.  My approach on this one, since there were only five fields in the
table was to Dim a variable for each field, put the values in the variables,
do an .AddNew, and copy the variables into the new records fields.  That's
crude!  Yours isn't.

Best,

Rocky


-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Wednesday, November 15, 2006 3:03 AM
To: accessd at databaseadvisors.com 
Subject: Re: [AccessD] Copy a record in a table

Hi Rocky

Crude? How come? DAO is normally the fastest method to add a record to a
recordset.
Here's a basic function:

<code>

Public Function CopyRecord( _
  ByVal strTable As String, _
  ByVal strId As String, _
  ByVal lngId As Long) _
  As Boolean

  Dim dbs     As DAO.Database
  Dim rst     As DAO.Recordset
  Dim rstAdd  As DAO.Recordset
  Dim fld     As DAO.Field
  Dim strFld  As String
  
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("Select * From " & strTable & " Where " &
strId & "=" & lngId & ";")
  Set rstAdd = dbs.OpenRecordset("Select Top 1 * From " & strTable & ";")
  
  With rstAdd
    .AddNew
      For Each fld In rstAdd.Fields
        With fld
          strFld = .Name
          If Not strFld = strId Then
            .Value = rst.Fields(strFld).Value
          End If
        End With
      Next
    .Update
    .Close
  End With
  rst.Close
  
  Set fld = Nothing
  Set rstAdd = Nothing
  Set rst = Nothing
  Set dbs = Nothing
  
End Function

</code>

Of course, you will need to refine this for compound indices, other unique
indices, OLE fields, default values etc. Also add error handling.

/gustav

>>> bchacc at san.rr.com 15-11-2006 02:11 >>>

Dear List:

Is there a quick or slick or elegant way to copy a record in a table to a
new record in the same table - just duplicating a record  

I've done it using DAO and cycling through the fields and creating the new
record and adding it but that seems awfully crude. 

MTIA
Rocky