storing public variables in a split database front end

[conception_native_0123]

per the title, what are the disadvantages of doing this? running out of memory? duplicate declarations? I can't think of anything else.

 

[Pat Hartman]

Typically tables stored in the FE are related to the objects of the FE and change when the FE changes. They are not changeable by the user.

Given that, I'm not sure what you want to store. If you are talking about custom settings selections for a specific user, I would not store those in the FE because you will clobber them if you release a new FE.

 

[conception_native_0123]

actually Pat, I was just wanting to gather thoughts on storing public variables in modules and using them sort of like session variables. like this:

public user as string
public userLoggedIn as bool

then letting them keep memory for as long as a FE user has their file open. that's what I meant.

 

[cheekybuddha]

>> running out of memory? <<
You'd be hard pressed to do this using module level variables!

>> duplicate declarations? <<
Your code wouldn't compile, so this is a bit of a non-issue too

 

[conception_native_0123]

You'd be hard pressed to do this using module level variables!

clarify?

 

[cheekybuddha]

Data type summary

Office VBA reference topic

docs.microsoft.com

Your intended use:

public user as string
public userLoggedIn as bool

Memory consumed: 10 bytes + string length + 2 bytes = ~50bytes with a long user name.

How many gigabytes of RAM do you have!

Unless you are stuffing objects like new Excel instances into a collection in an infinite loop you are going to have difficulty running out of memory!

 

[CJ_London]

if your code fails because of failed error control, you will lose the public variable values, So use tempvars instead which a) keep their value until the session is closed and b) they can be referenced in queries

 

[conception_native_0123]

So use tempvars instead which a) keep their value until the session is closed and b) they can be referenced in queries

thank you.

Unless you are stuffing objects like new Excel instances into a collection in an infinite loop you are going to have difficulty running out of memory!

not tough to understand that!

 

[The_Doc_Man]

For my personal viewpoint, there is nothing wrong with storing some things in the FE for, as you put it, "session variables." There IS the problem that a mishandled error would lead to the dreaded "reset" situation, which is why someone suggested TempVars as one way to solve this. They are not wrong to be concerned. It is a matter of how well you trust your error handling routines to stop an unhandled error from getting through.

IF you have a setup that reloads the FE with an auto-downloader/launcher script (which has appeared in this forum several times), one of the main objections to having localized FE tables for temporary work also vanishes because you would get a new, fresh FE copy each time.

I used local FE tables now and then to create working lists where actions would add targeted machines to the list, after which something affect those machines using a WHERE clause with an "WHERE x IN (SELECT Y FROM TEMPTABLE)..." So I would load up TEMPTABLE, possibly in multiple steps, and then work on them all at once in a suitable SQL statement. This was not always the optimum way but there were times when I got better speed out of the process. Particularly if the user had specified several things in the dynamic report criteria.

 

[MarkK]

Globals are good because you can use them immediately. They are bad because there is no way to know for certain what will break if you delete one. Globals are an insanely handy maintenance nightmare.
I expose a few classes I use a lot, one of them is a FileSystemObject. Rule of thumb: if you won't use it a lot, don't use it at all.

 

[gemma-the-husky]

The main problem with public variables is that they can be difficult to manage, because they have full project scope.

If you refer to a public variable in formA, and then refer to the same variable in formB, you have to be careful of the effect of changing the variable in one of the forms. I still do use them a lot, and I am am very careful about their use. Tempvars weren't around when I started, and I never got into the habit of using them.

I do put a lot of public variables into type containers, and then I get intellisense, and some limit/control to the variable use, and I have fewer "freestanding" public variables. I should use classes more than I do, which also provides more protection.

eg type container

TLogon as type
   userid as long
   username as string
   ''
   ''
end type

public Mylogon as TLogon

then I can easily select MyLogon.username in my code.

 

[conception_native_0123]

The main problem with public variables is that they can be difficult to manage, because they have full project scope.

I assume you wouldn't say the same thing if they were only used with a single form or for a single purpose, would you?

 

[gemma-the-husky]

if you use them on a single form, then you can declare the variable inside the form. It won't then start with a value, but then the variable only has scope within that form, and there is no possibility at all of another form using that variable. The other form can have a variable of the same name but it's a "different" variable stored in a different location of memory. I think you can declare a form variable as static and it will keep its value even after the form is closed, and then reopens. It all depends what you want to do with the variable. You can still easily get issues tracking variables.

All of these can have the same name, but they are stored in separate places in your memory, and VBA knows which one you mean each time, as it "tokenizes" each instance with a different address in each case.

You can have a named variable with public scope in a module. This is the widest. Every bit of code in the programme can see this variable.
A private variable in a module visible only to that module
A private variable in a form (module) visible only to that module
You can also have a class module that contains variables, and which makes the variables available only by class methods the programmer designs.
You can also have an variable (or a constant) passed from one location to a procedure (sub) or function as an argument (parameter)
That argument can be byval or byref. If byref, then changing it inside the procedure will change the real variable. If byval then a procedure will make a local copy just for use inside the procedure.
You can even have controls on a form with a similar name.
Offhand I am not sure without checking whether you can declare both a control and a variable with the same name on the same form.

The actual variable used tends to work backwords
So a procedure argument is the lowest level, and that will be used first, with precedence all the way up to a public variable in a module.

So, as the programmer you need to manage how you use your variables. It's just something you get used to without thinking, but if you do accidentally misuse a variable you can get programme errors that become quite hard to track. You won't get a logic/run time error - you just get a variable that doesn't have the value you expect, because the code is using a different instance if a variable than the one you think. and it can be tricky to pinpoint the issue.