recordset VS query object

[Etxezarreta]

Hello everyone,
I would like to find the best way to modify a table, out of two methods:
I usually create a table builinding a string with SELECT INTO and the content of form controls as variables in the WHERE clause, then using this a sourrce for anADO recordset.. Then I use and UPDATE query in VBA to modify the table. I am sure there are best ways for the same result.
-I have just tried almost the same sql string, only with the using SELECT instead of SELECT INTO in an access query object, then I have used this query in a subform: the table is directly modified if you modify this subform. Quick and simple.
I wonder how you can do the same without using the access query object, using a 100% VBA code and only SELECT instead of INSERT INTO and UPDATE : if I use the following code to populate an independant subform, the correct information appears, but no way to change the subform content, impossible to change the table content. I checked the properties of the form and subform: nothing is locked or restricted.

date_DateSelection = Me.txtbox_AppelDate.Value
str_NomDSPSelection = Me.combo_AppelNomDsp.Value

'MsgBox Format(date_DateSelection, "dd\/mm\/yyyy")
'Sql eskaria:
str_Sql = "SELECT Id_TacheAdmin, Date_tache, FK_ID_DSP, FK_ID_Tache, Duree, Lieu" & _
" FROM DSP_TachesAdmin_SaisirTachesAdmin" & _
" WHERE FK_ID_DSP =" & str_NomDSPSelection & " " & _
" AND Date_tache = #" & Format(date_DateSelection, "mm\/dd\/yyyy") & "#"

With oRcdset
.Source = str_Sql
.ActiveConnection = oConnection
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open
End With

Set Me.DSP_TachesAdmin_sf_Modif_SaisirTachesAdmin.Form.Recordset = oRcdset

Me.Refresh

oRcdset.Close
oConnection.Close

Set oConnection = Nothing
Set oRcdset = Nothing


Thank you for your help.
Etxe.

 

[theDBguy]

Hi. Building a table on the fly is not always a recommended approach unless you're talking about a temporary table. I am not sure I follow what you're trying to compare. Can you show us both methods by posting your code for the first approach too? Thanks.