Recordset Object Or SQL Statements - What Do You Use?

[Scottish_Anatomist]

Hey Everyone,

Not so much a problem as curiosity. While going through all the code I've written for a project over the last year, I noticed that I've used two different methods to achieve the same result to add and edit/update records in tables. It seems that at the beginning of the project I used one and for some reason a few months ago switched to another.

Namely:

Working with record set objects -

 With rs
        .FindFirst "UserID = " & Forms!frm_Login.cba_UserName.Column(0)
        .Edit
        'etc etc
        .Update
    End With

and using SQL action queries

'*********
'Insert SQL
'*********
DoCmd.SetWarnings False
DoCmd.RunSQL ("INSERT INTO tbl_AdminError(AdminCode, AdminDescription)" & _
            "VALUES (" & adminErrorCode & ",'" & adminErrDescrp & "');")
DoCmd.SetWarnings True
DoCmd.Close

'*********
'Update SQL
'*********
DoCmd.SetWarnings False
If Me.Dirty Then
    Me.Dirty = False
End If
DoCmd.RunSQL ("UPDATE tbl_UserError SET tbl_UserError.ErrorFixed = -1, tbl_UserError.FixedBy = " & gblUserNumber & ", tbl_UserError.FixedTime = '" & Now() & _
                "' WHERE tbl_UserError.[UserErrorID]= " & Me.lst_UserErrors.Column(0) & ";")

DoCmd.SetWarnings True

Now, I know there are many ways to accomplish the same thing, especially when programming, however I was wondering which you preferred to use in your VBA coding and why?

 

[Ranman256]

Queries are faster.

 

[CJ_London]

whichever suits the job - recordset is generally slower than sql

 

[Minty]

Where possible I use queries as it's generally easier to see what is happening, and most of the time is more verbose coding.

As a rule of thumb i nearly always create the SQL as a string variable first then simply currentdb.execute the string . I find It's easier to debug when it doesn't work.

 

[tranchemontaigne]

I concur with everyone in the preference for an SQL approach when actually reading/writing data. I use recordsets to define loops that often build SQL (DML) action queries.

Troubleshooting is easier for me using this approach, and code is often more portable when the back end database engine is changed from MS Access to Oracle etc because the interaction with an external database can be restricted to simple ANSI-92 standard SQL.

 

[pbaldy]

I would also lean towards SQL. If I used a recordset, I'd be opening it on an SQL string with a criteria, not using FindFirst to find the record within an unfiltered recordset. That would narrow the performance gap. I might use a recordset when I wanted to first see if the record existed; edit it if so, add it if not.

 

[pbaldy]

Oh, and I might use a recordset if there was a chance of form controls being left blank (and doing so was allowable), and that would break the SQL.

 

[tranchemontaigne]

filling null values/empty strings with a value is exactly what the NZ() function was created for

I use NZ() calls within my dynamic SQL code to fill in missing values so the SQL won't break. Here's a snipped from some dynamic SQL code

...
strSQL = strSQL & ", " & Chr(34) & Nz(rst1![FKEY].Value, strEmpty) & Chr(34)
        strSQL = strSQL & ", " & Chr(34) & Nz(rst1![IntroCheckBox].Value, strEmpty) & Chr(34)
        strSQL = strSQL & ", " & Chr(34) & Nz(rst1![FirstName].Value, strEmpty) & Chr(34)
        strSQL = strSQL & ", " & Chr(34) & Nz(rst1![MiddleInitial].Value, strEmpty) & Chr(34)
        strSQL = strSQL & ", " & Chr(34) & Nz(rst1![LastName].Value, strEmpty) & Chr(34)
        strSQL = strSQL & ", " & Chr(34) & Nz(rst1![Suffix].Value, strEmpty) & Chr(34)
        strSQL = strSQL & ", " & Chr(34) & Nz(rst1![Gender].Value, strEmpty) & Chr(34)
...

...
If (Nz(rst1![Date_PHL_Requisition_Sent].Value, "missing") = "missing") Then
            strSQL = strSQL & ", " & "Null"
        Else
            strSQL = strSQL & ", " & Chr(35) & rst1![Date_PHL_Requisition_Sent].Value & Chr(35)
        End If
...

 

[pbaldy]

No question it can be handled, but it's simpler to have

rs!FieldName = Me.ControlName

which won't care if there's anything there or not in my experience, barring it being a required field, which would never have gotten to this point anyway.