Hello UA

It is ok not to declare and instantiate the CurrentDB Object?

E.g

.

But i can use


and it works

My question is whether there is a problem with this approach

Ronald

I know with Microsoft's examples, they always do declare it. I would imagine that's so, you are implicitly declaring that variable to be that value each time the code runs. So, I would say that it's probably good practice to do so though many times, I just typically use CurrentDb.Execute.... and I've never ran into problems doing it that way.

quite often things like this fail

dim tdf as tabledef
for each tdf in currentdb.tabledefs
....
next

whereas this does not fail.

dim dbs as database
dim tdf as tabledef

set dbs = currentdb
for each tdf in dbs.tabledefs
....
next

knowledgeable users will explain exactly why the former instance goes out of context - but as a result I am in the habit of always declaring the variable.

Thank you for the reply

I thought it is harmless

Going to stop it

Change all the code


Ronald

I do iterations a lot through through database objects like in your example and more often than not I don't set a variable to the currentdb and I've never once ran into a problem with this. I'm not saying the way I did it is correct because I absolutely agree with you, it should be set. I'd be curious to find out why in your case and perhaps others that they get fail errors with this. Is it something to do with the references perhaps?

The reason why you want to set a variable for CurrentDb is because CurrentDb is a function and not a property returning the instance. When you invoke a CurrentDb, it implicitly calls Refresh on all of its collections before returning the instance. A little demonstration:



This is also why a For...Each may also fail or behave strangely with CurrentDb as subsequent loop may re-initialize the implicit variable from CurrentDb, confusing the internal looping mechanism. I know there's case where this works fine but I've also cases where it doesn't. Setting a variable based on CurrentDb takes away all this problem.

so, am I not to do it this way ....


dim rst as recordset
set rst = currentdb.openrecordset("mytable", dbOpenDynaset, dbSeeChanges)

In this case, it's OK because you're calling it only once and you're using recordset subsequently. Whenever you need to use same database variable from CurrentDb more than once, use a variable.

Fairly lengthy discussion on it, which feels like ancient history now... :-s
Weird huh?

Hello LPurvis

Thank you for the link


It has greatly enriched me on the subject

Ronald

Thanks for the link, Leigh. After reading that, I think it's a matter of everyone will just have to agree to disagree on what's right and what's wrong with this one.

Oh, well I wouldn't necessarily say that's what I take away from this.
Just that there are times when an object variable is required and times when a single call to the function will suffice.
I reckon Brent and I fairly reasonably identify the occasions and a valid supposition is made as to the reasoning behind it.
We're left with, not so much a blanket preference, but a description of requirements.
So if someone were to be of the "never use a variable" practice, they'd come unstuck at times - as described. If they're aware of when they need to use a variable - then it's all just good information to inform more effective development. :-)

Cheers.

Leigh,

I've always been of the mindset that you're always better off declaring variables. Whether it's something as simple as

strValue = Date
Me.MyTextBox = strValue

Vice

Me.MyTextbox= Date

to more elaborate arguments with the CurrentDB. In my personal opinion, it's just good practice, though I'll be the first to admit that I'm often times guilty of not practicing what I preach but I try.