The Better Approach SQL or DAO

[Steve R.]

I am using an unbound form that checks all the entries before creating the new record. The DAO code below is what I am currently using to create the new record and works fine. The obvious "downside" is that the entire recordset is opened just to add one record. The "upside" is that the DAO code is "cleaner".

         Set RST = CurrentDb.OpenRecordset("SELECT * FROM dbo_InspectionTable ORDER BY Recordid", dbOpenDynaset, dbSeeChanges)
            RST.AddNew
                RST!PermitNumber = Me.Text5
                RST!Permittee = Me.Text1
                RST!PermitType = Me.Combo11
                RST!InspectionDate = Me.Text17
                RST!EnterDate = Format(Now(), "short date")                
            RST.Update

It dawned on me that I could use the SQL code below to achieve the same objective of adding a new record.

        Set DBS = CurrentDb
        strValues = "'" & Me.Text5 & "', '" & Me.Text1 & "', '" & Me.Combo11 & "', '" & Me.Text17 & "', '" & Format(Now(), "short date") & "'"
        DBS.Execute "Insert into dbo_InspectionTable (PermitNumber, Permittee, PermitType, InspectionDate, EnterDate) VALUES " _
                     & " (" & (strValues) & ");"
        DBS.Close

Both codes work. My question is which approach would be the preferred method of adding a new record? Any alternatives or preferences?

 

[DCrake]

What is confusing me is that in your first example you are opening the recordset with an order by statement. Then proceeding to AddNew. If you are adding why are you doing so.

         Set RST = CurrentDb.OpenRecordset("dbo_InspectionTable", dbOpenDynaset, dbSeeChanges)

Should suffice and make it quicker.

 

[pbaldy]

In my experience, the recordset will be faster to add a lot of records. I would do this:

"SELECT * FROM dbo_InspectionTable WHERE 1=0"

which will open an empty recordset you can still add records to.

 

[DCrake]

I have seen this method before, but just to clarify. If I have a table with say 10k of records and I want to add one or more records to it using .AddNew if I do the following

Set Rs = CurrentDb.OpenRecordset("Select * From Tbl Where 1=0")

It will open a empty recordset however when I do the .Update it is added to the Tbl. If so this prevents dragging 10k of records over the LAN.

 

[Steve R.]

What is confusing me is that in your first example you are opening the recordset with an order by statement. Then proceeding to AddNew. If you are adding why are you doing so.

Blind re-use of the same code. The same form is used to edit the recordset so when the form toggles to edit mode, it uses the same set command.

In my experience, the recordset will be faster to add a lot of records. I would do this:

"SELECT * FROM dbo_InspectionTable WHERE 1=0"

which will open an empty recordset you can still add records to.

I have seen this method before, but just to clarify. If I have a table with say 10k of records and I want to add one or more records to it using .AddNew if I do the following

Set Rs = CurrentDb.OpenRecordset("Select * From Tbl Where 1=0")

It will open a empty recordset however when I do the .Update it is added to the Tbl. If so this prevents dragging 10k of records over the LAN.

Thanks. I will have to experiment with that alternative. We just crossed the 5K record mark so the unnecessary movement of records over the LAN could soon become a potential issue. Nice to learn this tricks of the trade.

 

[pbaldy]

David, I'm not sure if you're asking me for clarification there, but in my experience that indeed stops the records from being pulled over the network. I have an application that adds records to the main SQL Server tables from a local table. I first wrote it the way you have it in post 2. It worked great for a while, and then the users started to complain that they were waiting longer and longer for the process to complete. I switched to the method I posted and it sped right back up again. The destination tables now have a couple of million records in them, but this process runs instantaneously.

 

[Steve R.]

Tested your suggestions. Worked great. Such a simple solution it retrospect. Thanks.

 

[vbaInet]

Let's hope Microsoft doesn't make 1=0 one day

 

[DCrake]

It was for clarification, I was just to lazy to open up Access and try it.

 

[LPurvis]

Dealing only with the recordset consideration for now - you also have the option to simply request an empty recordset by including the option dbAppendOnly in the Options parameter of the OpenRecordset method.

While I agree that "WHERE 1=0" is an old valid trick to limit results, bear in mind the possibility for it perfrom less brilliantly.
Generally speaking - your database engine (whatever it happens to be) will optimise this pretty well, identifying the contradiction (and in the case of smarter, server RDBMS, will likely optimise that fact itself).
However 1=0 is ultimately a constant expression. It could cause inferior performance compared to, say,
WHERE PKField = "Impossible Value"
Or, similarly,
WHERE PKField Is Null
(bearing in mind that a primary key is required and, therefore, can't be Null).

Different RDBMS will likely treat these requests differently.
You can always test relative response under your own scenario.

But, as I say, there's the recordset equivalent. (And there's nothing stopping you from doing both - in tests from yesteryear I found both makes the tiniest fraction benefit, but really nothing of note).

Cheers.

 

[ChrisO]

Rainy day in Brisbane and nothing much else to do.

Well, if it’s just a matter of preference then…

Because it is running behind a Form it would appear that there is only one record to append.

I prefer the DAO method in that it can be somewhat documented in line.
It also seems to open up individual lines for inspection/correction.
I can’t see any point in making it dependent on references.
It fits on screen so that the ‘whole ball of wax’ is visible.
I don’t like the choice of Control names.

So, as a preference: -

Option Explicit
Option Compare Text

[color=green]' Make these Public if you use them a lot.[/color]
Private Const conDbOpenDynaset As Long = 2
Private Const conDbAppendOnly  As Long = 8


Private Sub cmdTestIT_Click()

    With CurrentDb.OpenRecordset("dbo_InspectionTable", _
                                  conDbOpenDynaset, _
                                  conDbAppendOnly)
        
        .AddNew
            !PermitNumber = Me.txtPermitNumber      [color=green]' Text field        Assuming manual entry[/color]
            !Permittee = Me.txtPermittee            [color=green]' Text field        Assuming manual entry[/color]
            !PermitType = Me.cboPermitType          [color=green]' Number field      Combo Box PK of tblPermitTypes[/color]
            !InspectionDate = Me.txtInspectionDate  [color=green]' Date/Time field   Assuming manual entry[/color]
            !EnterDate = Now()                      [color=green]' Date/Time field   Assuming automatic entry[/color]
        .Update
        
        .Close                                      [color=green]' Probably not necessary ;-)[/color]
    End With

End Sub

Hang on, the sun just came out, might take myself to a seafood lunch.

 

[Steve R.]

Rainy day in Brisbane and nothing much else to do.

............

Hang on, the sun just came out, might take myself to a seafood lunch.

Reminds me of the song. Hello Muddah, Hello Faddah! (A Letter from Camp), by Allan Sherman in 1963.

Thanks very much for the alternative approaches, I'm "it" at my work location, (there is an IT department somewhere, but as Topol (?) said "May the Czar stay far away") so there is literally no one else for me to discuss alternative approaches at work. This forum has been very helpful.

The Access 2007 part is a front-end connected to a back-end Microsoft SQL server. We have five offices that are each separated by over 80 miles from each other. Four of the offices use this form to enter their inspection reports. The form actually has 15 fields, but I cut it down for clarity in this post. My fingers and eyesight were getting frayed at looking at the SQL string. The DAO approach is definitely cleaner and easier to construct.

 

[Galaxiom]

With CurrentDb.OpenRecordset("dbo_InspectionTable", _
conDbOpenDynaset, _
conDbAppendOnly)

.Close ' Probably not necessary ;-)

Probably not necessary??

I have been led to believe that it is important to close recordsets to remove them from memory. Please explain.

 

[ChrisO]

Galaxiom.

Well, a question in the opening post in this thread is ‘Any alternatives or preferences?’

So I answered with the way I would actually write it or, to put it another way, my preference. That’s all it is, my preference, no twisting arms to get other people to write it the same way that’s just the way I would write it.
(Except I would not comment the code to that degree {smacks of Sesame Street} and I would not close the recordset.)

And so to your question…
“I have been led to believe that it is important to close recordsets to remove them from memory. Please explain.”

The arguments both for and against closing recordsets, and setting to nothing, have been raging for so long they hardly need revisiting. You have read some things and I, in all probability, have read and written more.

But all the readings and writings don’t prove a thing. At this point of time it’s still all conjecture, nothing has been proved one way or the other.

One could reasonably say that, with all the debate over the years, neither side of the debate has any proof. If either side of the debate had proof then the debate should fold under the weight of that proof, but the debate is still with us.

There is also the concept of ‘well why don’t we do it anyway just to be safe?’
That notion has been called ‘cult programming’ and it touches on other aspects as well. But we really don’t want to go there but rather just stay with this instance.

There’s a term ‘Golden Egg’ which I think Leigh Purvis coined. It refers to the reproducible proof, in the form of a database, that can demonstrate a failure caused by not closing or not setting to nothing.

That’s what Leigh and I want, a Golden Egg, the reproducible proof.

So, if you have a Golden Egg please post it and I’m sure Leigh will dump it on Microsoft via the very next email.

 

[Galaxiom]

Thanks Chris. I have only seen postings that say closing is important so I have always done it. I guess it is similar to an old idea that some claimed it is inappropriate to end an application by closing the window rather than using the File-Close menu selection. I've never seen anything bad happen from that.

I'll have to check out those debates.

I expect it is sensible to close a recordset during a sub if it is no longer used in the rest of it. This would free memory. However I long suspected that ending the sub was like killing an application window. Any memory it used would surely be cleared by the process that ran the sub or that would be poor programming practice by the writers of that process.

I always wondered about the difference between and the variety of usage with some developers using rs.Close, other Set rs = Nothing and some both. Are they even different?

And what about connections and other objects?

 

[ChrisO]

Galaxiom.

“Any memory it used would surely be cleared by the process that ran the sub or that would be poor programming practice by the writers of that process.”

I would like to think so but I would also question the word surely used it that context.

I’m way past making assumption about this subject, all I want is proof. That is, incidentally, why I said I would not close the recordset…attempting to manufacture a Golden Egg.

But one day that Golden Egg may come along and if it does we will have no choice but to accept it…

-----------------

“Any physical theory is always provisional, in the sense that it is only a hypothesis: you can never prove it. No matter how many times the results of experiments agree with some theory, you can never be sure that the next time the result will not contradict the theory. On the other hand, you can disprove a theory by finding even a single observation that disagrees with the predictions of the theory... Each time new experiments are observed to agree with the predictions the theory survives, and our confidence in it is increased; but if ever a new observation is found to disagree, we have to abandon or modify the theory.”
Stephen W. Hawking

-----------------


As to the Set rs = Nothing I really don’t know. To my knowledge rs is only a pointer to the recordset and as such may only be 4 bytes or 8 bytes depending on the machine. So a question does crop up in my mind if that is all it is. Are we just freeing up 4 bytes of memory in a 2 gig memory space? But then again; how do we prove it?


As to connections and other objects, I have no knowledge… ask Leigh.

 

[Galaxiom]

But one day that Golden Egg may come along and if it does we will have no choice but to accept it…

If the Golden Egg does come along one day then would not that mean many previous projects have a potential flaw? So wouldn't it be better to subscribe to the cult to be safe?

 

[ChrisO]

Not necessarily, there is also an argument that closing in the wrong order may cause more harm than good.

But I think some degree of rationality needs to be applied to the ‘cult’ thing.
Reductio ad absurdum; by the second law of thermodynamics a cat might spontaneously combust and hence we should put the cat out at night.
(Like the little pun? )

But if the ‘cult’ got its way we would also set strings to ZLS, numbers to zero, variants to empty and so forth.

But we don’t…

 

[LPurvis]

As someone who is not a member of this "cult" but has, in general, erred on the side of caution over the years - I have to say there's a difference to the extreme Chris offered in his summary there.
We treat objects variables differently, they're pointers to objects in memory that we hope will be destroyed once the last pointer to it is removed. When we explicitly destroy objects we're just trying to give this every chance of occuring.
But ultimately the process should be the same (destroy or drop out of scope).

If they have a close method - we have to take it as read that it's for a reason.
I gave a lengthy exposition on why I feel this way in the last thread here that Chris and I chased the golden egg - without success ;-)

Cheers.