Tempvars - Multiple ways to declare any reason to do one over the other?

[lameid]

When I first learned about Tempvars I saw the simple immediate declaration...

     Tempvars!SomeVariable = "SomeValue"

I have also seen...

     Tempvars.add "SomeVariable", "SomeValue"

Out of curiosity I was looking for any notes on why one might use one or the other and ran accross...

     Dim SomeVariable as Tempvars
     Tempvars!SomeVariable = "SomeValue"

I did not see these examples in one place anywhere. MSDN seems to have documentation of the second method. Anyone have any thoughts on best practice on this one or is it simply a matter of style and personal preference?

 

[Darrylles]

I've never used Tempvars, never come across it, and for the life in me - cannot think of an advantage of using it.
(You have to manually 'manage' it in memory also).

I've also done a search and not found anything concrete on it's use.

It seems to me that the first declaration will be more efficient, but would always use the last as it is explicitly declaring the variable (as opposed to simply referring to something mid-code that has never been pre-declared).

The last declaration simply 'feels' right from a best-practice point-of-view as it's declaring a variable in a location where you expect all variables to be declared, which begs the question: why use it at all?

Good luck in digging that out.

;-)

p.s. After doing a little more digging, here is a blog explaining it's benefits / usage...

https://blogs.office.com/2010/09/27/power-tip-maximize-the-use-of-tempvars-in-access-2007-and-2010/

 

[strongm]

This page explains some of the nominal benefits. I don't use Access in anger enough to have a view on this, although I can see that surviving an error condition, being useable in queries without using functions and being accessible from both VBA and macros might be handy. The fact that they were a late, deliberate introduction to Access shows someone thought the functionality they introduced was worthwhile

 

[lameid]

Yes it is directly usable in querydef objects and potentially useful that way and easier than writing a wrapper function for a global function. However it is worth mentioning that Tempvars ARE NOT useable in SQL statments executed in the DAO object mode. In other words Tempvars hangs off the application object model and is only available to objects that use the Application object model. Of course it is a simple matter to concatenate your values into the SQL statement to use in code but it can bite you if you expect it to work. By extension I would assume it is not available in ADO either but I felt the pain in DAO - because I'm working with native databases therfore DAO is faster and the correct choice.

For example...

'This will fail because the Tempvars value is embedded and not available in DAO
Currentdb.Execute "Update Set SomeTable.Somevalue = [red]Tempvars!SomeParam[/red] From Sometable"

Cycling back to the original question, I do not think it matters which way it is used.

The add method does lead you to expecting there is a Remove method and possibly other methods (OP Option 2). Just using the tempvars when needed (OP Option1) is the least amount of code and the more traditional declaration with DIM (OP option 3) would help people new to Tempvars find the values. The add method is code therefore you do have to declare in a procedure to use that method.

As a general rule of thumb, I think global scoped values of any sort, tempvars included, are to be avoided. However, there are cases and applications where they make sense. Tempvars do have some clear advantages over traditional global variables and I do prefer them for that reason, most notably the value persists if there is a hard crash.

 

[lameid]

Woops...

I so rarely write Access engine database queries I made a mistake...

'[green]This will fail because the Tempvars value is embedded and not available in DAO
[/green]
Currentdb.Execute "Update Sometable Set SomeTable.Somevalue = Tempvars!SomeParam"

I am always getting confused on which 'action' queries are weird over standard SQL in Access.

The above only doesn't work because of the Tempvars

 

[Andrzejek]

Out of curiosity...
Would this work:
[tt]
Currentdb.Execute "Update Sometable Set Somevalue[blue] = " & [/blue]Tempvars!SomeParam[/tt]


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[lameid]

Yes Andy, assuming it is a number and did not need delimiters added because it is a text or date. I only mention the delimiters for completeness.

Anyone else have thoughts on the OP?

I am basically thinking it is a style preference with the notable caveat that MSDN chose to document the add method. So that may be the preferred method. I am not even sure that means anything because I do not expect Microsoft to deprecate a way to use a feature.

A more interesting related question may be why are there three different ways to declare them?

I can see adding it as a collection and then deciding it should work like all variables which means it does not have to be declared because modules exist without Option Explicit. That would give rise to option 1 in the OP. And then because tempvars always exist nothing was done to deal with the Option Explicit implementation because it would require a fundamental change to the behavior of objects. And then by the same token I can see adding the the third option to be consistent with other variable declarations. Which means it is a collection of variant variables. The collection implements methods. So to go to the root behavior of the collection, you would use methods. So a purist might say it is a collection, treat it as such. But given it is a special collection with several enhancements, I do not see them changing anything either. Having said that, if they did kill tempvars ever, someone could write a tempvars class to make code magically work again without a total rewrite. So I guess if it is involved and big enough to be on the safe side, the second option with the method is the way to go.

 

[strongm]

Well, they are not really declarations, are they? They are, at best, assignments.
The first two are really synonyms for adding a member to the collection, and which you use is pretty much simply a matter of personally preferred style.

But I wouldn't use the third variant

Dim SomeVariable as Tempvars
Tempvars!SomeVariable = "SomeValue"

since that clearly originated with someone who didn't understand what was going on, and has then proliferated as a cut'n'paste solution on t'Internet

 

[lameid]

Well, they are not really declarations, are they? They are, at best, assignments.

Yes the problem with my thinking.... Tempvars simply exists by virtue of Access running. So there are no declarations and the add is implicit if you use one that is not already in existence (and this is regardless if Option Explicit is used).

I played with just using DIM in a module (option 3) and then the immediate window... It does not exist (I don't have an empty tempvars when accessing using numeric index). Which further supports it being a bad method.

I've also established that tempvars can be a null value... but I can't seem to figure out how to test for it... both of the below say object required in the immediate window.

? Tempvars("New") is null
? Tempvars("New").value is null

 

[strongm]

? IsNull(TempVars("New"))

 

[lameid]

D'oh. That's embarrassing.

I knew yesterday was bad for clarity of thought for me but I did not realize it was that bad.

Now I'm extremely grateful I didn't really accomplish much, therefore badly, yesterday.

I don't recommend forgetting medicine, Asthma and allergens... That leads to low oxygen and associated stupidity.