Solved To define a variable or not? (1 Viewer)

[LGDGlen]

Wondering if there is ANY difference in the way in which the following different versions of the same code are executed.

    Dim db as database
    set db = CurrentDb
    db.Execute "UPDATE [FACT-DeliveryInvoicesAndCNs] SET " & trimSelection("," & SQL) & " WHERE [FACT-DeliveryInvoicesAndCNs].id In (" & trimSelection(Me.txtSelected) & ");", dbFailOnError
    set db = nothing

OR
    CurrentDb.Execute "UPDATE [FACT-DeliveryInvoicesAndCNs] SET " & trimSelection("," & SQL) & " WHERE [FACT-DeliveryInvoicesAndCNs].id In (" & trimSelection(Me.txtSelected) & ");", dbFailOnError

As in is it better to create a CurrentDB variable and execute the query using that or just use CurrentDB directly. This might be a stupid question but it just seemed a bit silly to define a variable, set it, use it and then clear it when the same can be achieved in 1 line of VBA

again might be that there is no difference and it comes down to preference and if so great, but if there is a definitive "best practice" when it comes to these sorts of things i'd appreciate the collective wisdom of this place pointing me in the right direction

Kind regards

Glen

 

[Mike Krailo]

Don't think it makes any difference. There are many ways to do the same thing in VBA. Only thing is that it would be better to disambiguate by using DAO.Database.

Readability trumps line reduction in code. In this case though, both are perfectly fine.

 

[LGDGlen]

@Mike Krailo thank you appreciate your response, i'll stick with less code. one thing though, what do you mean by disambiguate?

 

[LGDGlen]

ah-ha, i found http://allenbrowne.com/ser-29.html and i can see what you mean

 

[MajP]

Be careful though there are times that you have to set a variable. And this error can be mind-boggling if you do not know why. It will not seem to make any sense. If you plan to work with the table defs, query defs, or modify other properties in the database collection. Opening a recordset is not one of these instances so no problem there.

See long discussion.

CurrentDB.TableDefs vs db.TableDefs ... Object Invalid or Not Set Error

social.msdn.microsoft.com

Example. This will fail with an object not set error

Public Sub Test()
  Dim tdf As dao.TableDef
  Set tdf = CurrentDb.TableDefs("Cat")
  MsgBox tdf.Name
End Sub

But this simple change works

Public Sub Test()
  Dim tdf As dao.TableDef
  Dim db As dao.Database
  Set db = CurrentDb
  Set tdf = db.TableDefs("Cat")
  MsgBox tdf.Name
End Sub

The reason is has to do with "CurrentDB' being a function that returns an instance of the current datbase and not an object.

 

[sonic8]

The reason is has to do with "CurrentDB' being a function that returns an instance of the current datbase and not an object.

This sentence does not make sense to me. What would be the difference between an instance of a database and an object?

I've got only a vague wild guess why the behavior differs. So, I'm not going to offer an alternative explanation.

 

[LGDGlen]

@MajP if i'm working with more than just executing 1 query i have always declared a database variable and used that, it was just in this instance for 1 line of query it just seemed a bit of over kill but i didn't want to not do it if it is something that should be done as a matter of course. i am currently doing that thing where you go back to the stuff you did at the start when you didn't really know what you were doing and correcting based on experience, things like using queries instead massive .edit/.update code where large numbers of records are updated, closing recordsets and databases when subroutine is complete etc etc etc and this was one of those instances where i was migrating from edit/update loops to a query

 

[MajP]

@sonic8,
My understanding is as Mark Burns describes in that link.

 

[The_Doc_Man]

We had a more lengthy discussion about this some time ago. Rather than relying on my rusted-out brain cells to recall what was said (and which links were posted), I just found the thread.

dbEngine(0)(0) vs Currentdb

I found this article a few days back and re-read it again today when I had some time to actually read it. https://www.experts-exchange.com/articles/2072/CurrentDB-vs-dbEngine-Workspaces-0-Databases-0-and-an-alternative.html The author obviously knows what he is talking about but it never hurts...

www.access-programmers.co.uk

Remember, we don't know what code is actually generated for VBA and can't see object references in fine detail. I believe that what happens is that an actual object (the thing for which an object variable is a reference, i.e. a pointer) retains the defining steps that it takes through other objects. If I create an object that is derived from a temporary object, an element of that path can become invalided by a change in scope and thus all subsequent objects can become equally invalidated.

I first saw this in Excel, which has a similar problem if you define things using ActiveWorkBook as opposed to assigning a workbook variable first. ActiveWorkBook and ActiveWorkSheet both return temporary references. Where I was having the trouble was that if I used ActiveWorkBook (which is temporary), my range definition was, IN EFFECT,

Workspace.<temporary variable referencing a workbook>.<variable referencing a worksheet>.<variable referencing a range>

BUT the temporary reference to the workbook vanished leaving me with

Workspace.<vanished item>.<variable referencing a worksheet>.<variable referencing a range>

Rather obviously, traversing that path after the reference vanished is a place to trip over. In the Excel case, you get the dreaded 1004 error, Application-Defined Error.

The point is that CurrentDB, ActiveWorksheet, and (in Word) ActiveDocument all involve TEMPORARY pointers to objects that vanish when you change contexts. Which is why "Me." doesn't work across VBA modules. The short-cuts work great but have limited scope of applicability.

The moral of the story? When creating references that "chain" their way through a sequence of objects, ALWAYS use an object variable for every intermediate step, not one of the dynamic reference methods/functions.

Another place that tripped me up was using db.Execute followed by db.RecordsAffected. If I used "CurrentDB" for both of those, .RecordsAffected was always 0, but if I created a DB object-variable, the .RecordsAffected was correct. Again, it was the ephemeral nature of the object "behind the pointer."

 

[CJ_London]

Personally I would go the longer route. Main benefit of using db rather than currentdb is that you can get back the recordsaffected value and the PK of a newly created record

I would also assign your sql to a sqlStr then execute that instead - easier to debug

And you can set db as a global variable in a standard module. So assuming you have

standard module

public db as dao.database

in your opening form open event

set db = CurrentDb

in your function/sub

dim sqlStr as string

sqlStr="UPDATE [FACT-DeliveryInvoicesAndCNs] SET " & trimSelection("," & SQL) & " WHERE [FACT-DeliveryInvoicesAndCNs].id In (" & trimSelection(Me.txtSelected) & ");"
'debug.print sqlStr
db.Execute sqlStr, dbFailOnError

MsgBox db.RecordsAffected & " records Affected" 'will return 0 for currentdb
MsgBox "New Identity " &  db.OpenRecordset("select @@identity")(0) 'only relevant for single record inserts

and if you want reduce the amount of typing, put it in a separate function, and pass the sql string as a parameter.

 

[LGDGlen]

@The_Doc_Man thank you for the detailed explanation, as stated previously i am creating DB vars for CurrentDB pretty much all the time as mostly it tends to be more complex than the 1 line query that this particular thread is about, i had suspicions that creating a DB var was always the thing to do when doing things more complex so i'm glad that i have been doing that as a matter of course. this thread was mostly for my understanding that you and @MajP and @CJ_London have all helped with.

@CJ_London i think i might take your cue and at least make sure that i check that something was updated, although as this is a code snippet the context isn't present and i do do a few bits of checking prior to this so there would always be something updated (or at least attempted to be updated) at the point the query is run. i guess the check might be useful in the event of an issue with the query running itself

 

[CJ_London]

For what it is worth, one of the functions I use has 4 parameters - one for sql, one for records affected or identity (can be determined from the sql), one for error code and a final optional one as to where to handle errors (default true). And the function itself returns true or false, true means no error, false means there was an error

so my calling code is something like this - I might manage errors within the function or from the calling code (true, handled within the function, false handled on the return)

'these could be declared globally
dim recNum as long
dim errNum as long
dim sqlStr as string

sqlStr="Update myTable Set ....."
if sqlExecutedOK(sqlStr,recNum, errNum, false) then 'no errors so
    'maybe do something
    msgbox "records updated " & recNum
    'or perhaps for an insert query use the recnum to populate the FK of a child table to be inserted
else 'manage error - the err.number and err.description should still be available and technically does not need to be returned, I just do
    select case errnum
....
end if

 

[LGDGlen]

@CJ_London thats a useful bit of code thank you