Listbox, AddNew versus INSERT SQL

[hkaing79]

I have two listboxes with four buttons: <<, <, >, and >>. I'm using SQL statements INSERT and DELETE to move data from one listbox to another. The two listboxes are unbound.

I've been reading some threads that use .AddNew then .Update. What's the difference between the two? Is there a difference?

 

[TheAceMan1]

How are ya hkaing79 . . . . .

[blue]AddNew[/blue] & [blue]Update[/blue] are required methods when adding a record to a table via [blue]Recordset[/blue].

[blue]   Dim db As DAO.Database, rst As DAO.Recordset

   Set db = Currentdb()
   Set rst = db.OpenRecordset("TableName",dbOpenDynaset)

   rst.[purple][b]AddNew[/b][/purple]
   rst!Name = "Harold"
   rst!Phone = "212 987-6541"
   rst![purple][b]Update[/b][/purple]

   Set rst = nothing
   Set db = nothing[/blue]

[blue]The [purple]SQL Insert[/purple] method is much faster![/blue]

See Ya! . . . . . .

 

[hkaing79]

I converted all my code from...

Dim strSQL as String

strSQL = "INSERT INTO TableName (Name, Phone) VALUES ("Harold", "212 987-6541");"

With DoCmd
.SetWarnings False
.RunSQL strSQL
End With

to using Recordset. Is there a difference between the two approach? I seem to have gotten the same result, but I'm just curious as to the pros and cons of each method.

Also, how do you do that code box in your message?

 

[RoyVidar]

Executing queries is faster than using recordsets (as also stated by TheAceMan1). The usual recommandation is to use queries over recordset wherever possible. Even faster than the docmd thingie, would be using the .execute method of either the (DAO) database object or an ADO connection:

[tt]currentdb.execute strsql ' or
currentproject.connection.execute strsql[/tt]

To make this

type
[ignore]

To make this

[/ignore]

Roy-Vidar

 

[TheAceMan1]

hkaing79 . . . . .

[blue]RoyVidar[/blue] has given an [blue]excellent explanation[/blue] to your question.

The only thing I see I can add is, if you notice, [blue]you generate more code using recordset.[/blue] Also if you had alot of data, you would see just [blue]how much faster the SQL method is![/blue]

See Ya! . . . . . .

 

[gol4]

one more thing to add to this. Ensure that you set warnings back to true

With DoCmd
.SetWarnings False
.RunSQL strSQL
.SetWarnings True
End With

 

[TheAceMan1]

How are ya gol4 . . . . .

I usually turn [blue]action query warnings[/blue] off period!

MenuBar - Tools - Options - Edit/Find Tab - Confirm Section - [blue]UnCheck Action queries[/blue] . . . .

See Ya! . . . . . .

 

[hkaing79]

Thanks for all the explanation.

Sorry I didn't catch that last line in AceMan's explanation (and it's bolded too...sigh).

Thanks for the tip about currentdb.execute, RoyVidar. I was looking for a command like that but only found the DoCmd.RunSQL.

I'm creating a user interface, so I only want to display my own warnings. Thanks for the tip on turning off action query warnings, AceMan.

I have two more question about the two methods. I was asked to include a filter function. On the left listbox is a list of available staff and on the right is a list of registered staff.

Let's say I have a button to filter the available staff adding "WHERE " & strFilter to the Rowsource. Is the SQL method still the way to go, or should I use the RecordSet approach?

I read somewhere that there's a .Filter command? If I use that, will those filter settings be implemented if I use the SQL approach? In other words, if I filter the listbox and I click on an "Add All" button, will the filter limit the SQL command or do I still need to add "WHERE " & strFilter?

And the second question, what if I have a join as my recordset? Can I still use the update commands?