CurrentDb.Execute Or QueryDef?

[pr2-eugin]

Howdy people,

Just a quick question. Let us say you have an action Query as simple as inserting a record with just two fields Name and Date into a table. Just to make it a bit complicated, let us say it needs to be dynamic or in other words need to take the current date and the current user. So a parameter query is required.

If it were me, I would simply use.

CurrentDB.Execute "INSERT INTO Log " & _
            "(UserName, DateAccessed) VALUES " & _
            "('" & GetLogonName & "', " & Format(Today, "\#mm\/dd\/yyyy\#") & ");"

The other way to go around is to use a QueryDef.

Dim query As QueryDef
Set query = dbs.CreateQueryDef("", "INSERT INTO LOG (UserName,DateAccessed)" & _
                                         " VALUES(@user,@time)")
query.Parameters("@user").Value = "Moo"
query.Parameters("@time").Value = Now
query.Execute

My head thinks, why do you need to use Query def, a very expensive procedure when you can sort it out by simple Execute? So I would like to know what method would you prefer. The pros and cons maybe.

 

[BlueIshDan]

For me it would be the same as anything else when it comes down to deciding between two ways of doing the exact same thing.

Who's going to be looking at this code in the future, and which of the ways will they be able to understand clearly.

Weighed against

Does this procedure need to be optimized to the max due to recursion and quantity of processed information.

In short, I guess it just depends on the scenario lol.
(In my ignorant opinion.)

 

[pr2-eugin]

That is mainly the point Dan, personal choice against standard procedures. Cost against efficiency. I would go for the CurrentDB method.

 

[namliam]

Shouldnt use temp (query) objects to prevent bloating (how ever little it may be)

You forgot an option, but it really dont matter, of all options available in this case Currentdb.Execute is the best construct.

 

[gemma-the-husky]

an other way is to construct the SQL as a string rather than use the parameters option.

then either
currentdb.execute sqlstrg OR
docmd.runsql sqlstrg

 

[pr2-eugin]

Wish I could add the other option to the poll, DoCmd.RunSQL. Unfortunately I could not. But over DoCmd.RunSQL I would still use CurrentDB.Execute.

 

[MarkK]

I like how a querydef handles delimeters automatically, and if you code it tidier, I think it's more readable . . .

Private Const SQL_INSERT As String = _
   "INSERT INTO LOG " & _
      "( UserName, DateAccessed )" & _
   "VALUES " & _
      "( p0, p1 )"
[COLOR="Green"]'. . .[/COLOR]
   With CurrentDb.CreateQueryDef("", SQL_INSERT)
      .Parameters(0) = "Moo"
      .Parameters(1) = Now
      .Execute dbFailOnError
      .Close
   End With

This, to me, is a less readable, more fragile approach . . .

            "('" & GetLogonName & "', " & Format(Today, "\#mm\/dd\/yyyy\#") & ");"

 

[spikepl]

Yeah a mix of stuff like

Brian O'Donnell and
Johnny "The Machine" Zigaropappahimidimitropoulos

is always fun to deal with in SQL

But - the main issue is maintainablity and readability. The cost of hardware is skyrocketing downwards, and the cost of meatware is the one variable where everyone has direct influence on the tally at the end.

Efficiency? Disk place? Use reason. Ages ago I coded on some 32K-memory computer with multiple statements in each line, to save on the line counters ... those days are long gone.