MS Access (ADO): AddNew/Delete/Update vs. SQL statements

I tried using SQL, but was unsuccessful - do I have to use .BeginTrans, .CommitTrans, etc.?

I thought you had to declare a record set when doing an add/delete or update.

For the 500 points or a split, please provide code snippets.

Thanks.

Thanks for the reply.  The samples are great.

One more question: Is it good to use .BeginTrans, .CommitTrans, .Rollback, etc?  I have a very simple application and being new to DB programming, I don't know what's the best way of implmenting things.

If it's good to do it, please show an example.

Thanks again!

Thanks everyone for your replies.  They were all great.

halfondj,

Wheter you use SQL or VBA depends on your exact requirements.

In a distributed environment, SQL is probably preferred, since the code runs on the server. Other than previous posters, I found that VBA is in many cases faster than SQL since you can optimize the code for a specific task; the SQL engine can't do this since it doesn't know what you want to achieve.

Some Examples (off the top of my head, not verified):

SQL:   UPDATE MyTable SET FieldA = "bla" WHERE FieldB = "anykey"

VBA:   Dim RS as Recordset
          Set RS = CurrentDB.OpenRecordset("SELECT * FROM MyTable")
          RS.FindFirst "FieldB='anykey'"
          RS.Edit
          RS("FieldA") = "bla"
          RS.Update

Obviously, the VBA is longer and in this example, it might even be slower. However, if you need to iterate through a set of records, VBA will be faster:

SQL:
    SQL = "SELECT COUNT (FieldA) FROM MyTable";
    n = DoCmd.ExecSQL(SQL)
   
    For i = 1 to n
       SQL = UPDATE MyTable SET FieldA = "bla" & i WHERE FieldB = "anykey" & i
       DoCmd.ExecSQL SQL
    Next i

VBA:  
   Dim RS as Recordset
   Set RS = CurrentDB.OpenRecordset("SELECT * FROM MyTable ORDER By anykey")
   RS.MoveLast
   n = RS.RecordCount
   RS.MoveFirst
   While NOT RS.EOF
       RS.Edit
       RS("FieldA") = "bla" & i
       RS.Update
       RS.MoveNext
   Wend

Again, the VBA is longer, but I believe it will be significantly faster, because all the overhead of processing SQL is not required.
   
Transactions:
- can be used to increase performance, especially in a distributed environment.
- can be used to make sure that either all or no change on the DB will occur.
You wan't need transactions normally, however, they can come very handy in specific situations.

Hope this helps
Jan

Good to hear that...thanks for the points :-)

To fulscher: Thanks for your explaination.  It was very well done.  I would have certainly given you points too. :).

Well, I was a bit late. Hope you can use some of the stuff, anyway.

Jan

Definitely.  I owe you :).

i am doing one project vb with msaccess 97 ,i am connected all command add,select delete ,
this add commands for example i am add to the name via vb to msaccess that particular names are added ,but i want that name already here recently add a name not checked