6

I have inherited a lot of code that is essentially like this:

dim dbs as dao.database
set dbs = currentdb()
dbs.execute "Some SQL string"
set dbs = nothing

Is there any reason not to recode it as:

currentdb().execute "some  SQL string"

(I know that if I want to use .recordsaffected, currentdb().recordsaffected won't yield usable results).

Are there any benefits from recoding it, other than simplifying the code?

ColeValleyGirl
  • 577
  • 15
  • 40

1 Answers1

8

Simply using CurrentDb.Whatever is a tempting shortcut, but there are quite a few circumstances where it causes strange behaviour. That's because CurrentDb is not an Object itself, it is a Function that returns a copy of the current Database object.

Years ago I swore off trying to use CurrentDb like it was an Object after the umpteenth time I was debugging code that I knew was "right", and it was... once I created a proper DAO.Database object (Set cdb = CurrentDb) and used cdb.Whatever instead of CurrentDb.Whatever.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • 1
    What about when you use it only to set up a recordset? ie. set rst = currentdb.openrecordset()... – HelloW Jan 28 '14 at 21:35
  • 3
    @HelloW For production code I always create a proper DAO.Database object regardless of its intended use. Occasionally for "throw-away" code I'll get lazy and use `CurrentDb.Whatever` but in my opinion it's a bad habit. Any supposed gains in performance or code readability are really negligible, and since there *are* circumstances where using `CurrentDb.Whatever` gets weird I'd rather be consistent and not have to remember when it works and when it doesn't. – Gord Thompson Jan 28 '14 at 21:48