Recordset Object Or SQL Statements - What Do You Use? (1 Viewer)

S

Scottish_Anatomist

New member
Local time
Today, 06:24
Joined
Nov 18, 2015
Messages
7
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 -

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

and using SQL action queries

Code: 
'*********
'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?

:)
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 06:24
Joined
Feb 19, 2013
Messages
16,603
whichever suits the job - recordset is generally slower than sql
 
Minty

Minty

AWF VIP
Local time
Today, 06:24
Joined
Jul 26, 2013
Messages
10,366
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.
 
T

tranchemontaigne

Registered User.
Local time
Yesterday, 22:24
Joined
Aug 12, 2008
Messages
203
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

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 22:24
Joined
Aug 30, 2003
Messages
36,123
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

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 22:24
Joined
Aug 30, 2003
Messages
36,123
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.
 
T

tranchemontaigne

Registered User.
Local time
Yesterday, 22:24
Joined
Aug 12, 2008
Messages
203
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

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)
...


Code: 
...
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

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 22:24
Joined
Aug 30, 2003
Messages
36,123
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.
 
You must log in or register to reply here.

Similar threads

A
Inserting numeric values into a Table, using SQL : What's the correct VBA code to use in the VALUES part of the code ?
Replies
10
Views
381
ebs17
E
K
Database FE freezes up every time I try to open it.
2
Replies
28
Views
640
cheekybuddha
cheekybuddha
C
Invalid Use of Null
2
Replies
20
Views
513
chuckcoleman
C
F
Runtime Error 3001 HELP I Am loosing my mind
Replies
14
Views
665
ebs17
E
B
VBA problem relating to SQL linked table
Replies
6
Views
513
CJ_London
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom