rs.Addnew or INSERT INTO

[simon woods]

Hi

When should you use

rs.AddNew
....
rs.Update

instead of the SQL statement INSERT INTO, bearing in mind that on issuing
rs.Update, the SQL statement is created and executed

Come to think of it, when should you use recordsets and when should you use
action queries ?

Just wondering

Thanks

Simon

[Richard Sbragia]

Simon, I think it is

rs.edit
rs.update

or
rs.addnew
rs.update

if you are updating data, it implies that there is already a record in the
table, if it is Addnew, it implies that there is no record so create
one...SQL equivalent to an update or append qry.

I look at queries and rs as one in the same, views of data...Typically, an
update qry created and stored in the db will run faster than having to loop
through the rs with a do until rs.eof or something. I find that the
flexability provided by vba offsets the time expense incurred. I think vba
lets you evaluate the records with more control than the sql equivalent...

Plus, vba option keeps my query tab much cleaner and not bogged down with
dozens of little queries...

simon woods wrote in message ...

[Hans-Chr. Francke]

Use action queries whenever possible. They are ways faster than looping
through a recordset.

simon woods skrev i meldingen ...

[Allen Browne]

Always use SQL if you can. Build up the SQL statement as a VBA string
and then Execute it with dbFailOnError.

Reserve AddNew for occasions where you don't see a way to achieve the
result in SQL. For example:

1. You need the AutoNumber of the record you just created.
With rs
.AddNew
'...
.Update
.BookMark = .LastModified
Debug.Print "The ID was " & !ID
End With

2. The values to assign are not constant through the INSERT, such as
to 100 related uniquely-numbered records:
With rs
For i = 1 to 100
.AddNew
!EditionNumber = i
.Update
Next
End With

simon woods wrote:
>
> When should you use
>
> rs.AddNew

> .....