variable handling with multiple users (1 Viewer)

[Fantast]

Hi. I have an Access 2007 database which is located on a shared disk. De the database is an ACCDE in running environment (renamed to accdr).
I created some Module classes and many functions which are used by the forms in the database. The user will be able to make some selections in listboxes, checkboxes and other controls and then generate an Excel file with the filtered data.

My question is whether the values of the global variables and classes used by the form will be affected when a second, third or nth user opens the database at the same time and makes his own selections. So in short: will Access create a new set of global variables for each user and make those variables only accessible within the session of that user?

I might be able to test this in a week, but I need to advance with my coding now. Any insight regarding forms with multiple sessions is much appreciated.


Thanks in advance,
Fantast~

 

[Galaxiom]

Each user should have a separate local copy of the front end.

 

[jleach]

Each user should have a separate local copy of the front end.

Idneed. Multiple users in the same frontend is an excellent way to introduce various forms of corruption, most notably FE object problems, but backend data problems as well.

If the database is not split into a front and back end, you should make it a priority to do so.

Ideally each FE is a local copy, but in some cases (terminal servers, notably) this is not possible. In such cases, make a separate file for each user, but don't allow more than one user to access more than on FE at a time.

hth

 

[Fantast]

Thanks for the replies so far.

Unfortunately it is not up to me how to handle the accessibility to the database. The database is located at a shared drive, and the users are allowed to log in through a public computer to access the shared drive and run the accdr directly from the shared drive.

Reading the replies above I conclude that all variables are editable for all users on one FE. So there is no way to make the variables session related instead of environment related. Is my conclusion correct?

 

[jleach]

Are multiple people logged in at the same time, or is there only one public computer from which people will log in, one at a time?

If people only log in one at a time, then there'll be no problem. Global variable data is flushed each time the application closes - data is only retained past shutdown if written to disk, or database properties.

If there is more than one person accessing the file at the same time, then you're in trouble. Access was never intended to work this way (except now with Web Databases, but that's a whole different boat), and thus has no provisions for the way it's attempting to be used. If this is the scenario, the best I can suggest would be to go to whoever IS in charge of the accessibility of the DB, and tell them that the means they're implementing it is completely wrong.

Cheers,

 

[mdlueck]

Reading the replies above I conclude that all variables are editable for all users on one FE. So there is no way to make the variables session related instead of environment related. Is my conclusion correct?

Even though in your configuration you run with one shared database file, the VBA code executes in the context of the instance of Access running the database. I see no reason that the Access front end executing the VBA code should be able to see the variable values of other instances of Access running elsewhere on the network.

The variable values reside in the program running on each computer, not in the shared database file.

Agreed, +1 for the suggestion to separate the FE from the BE DB, and script deploy the FE to each workstation using the application.

 

[Fantast]

@jleach
I have to take both possibilities into account. And obviously the second scenario, where multiple users will use the same file from several computers is what concerns me.

@mdlueck

Even though in your configuration you run with one shared database file, the VBA code executes in the context of the instance of Access running the database. I see no reason that the Access front end executing the VBA code should be able to see the variable values of other instances of Access running elsewhere on the network.

The variable values reside in the program running on each computer, not in the shared database file.

This is still unclear to me. Consider user1 and user2 both logging on to the shared drive K through a VPN connection, using a public computer, and execute an accdr file (being only a FE). They do not download the file to the public computer, they execute it directly from the K drive. I would assume (and was hoping) that the accdr would run on the OS of the public computer they are using, and thus all variables created during their session would only exist for them. So if in VBA a new instance of a class is created when the file is opened, that class would exist independently on both public computers and will not be effected for user2 when user1 edits a property of that class. The same would go for public variables of course.

Am I wrong or is the above correct?

Thanks for bearing with me.

 

[spikepl]

Your scenario is likely to kill your db sooner or later.

1. Access is NOT a database server, but a dumb file. It is executed on whichever computer has Acess installed, ie.NOT on any storage server. If you run it across WAN, then the the contents of the Access file are dragged across the public network back and forth to the computer on which you are running, and that is not recommended for WAN, since any dropouts can corrupt it, and it will be exceedingly slow.

2. If you wish to run Access via WAN, then the way to do this is using Citrix, Terminal Server, or Remode Desktop, so you from the outside can login on a local computer on the LAN, and execute Access there. And this irrespective of split or unsplit DB.

 

[mdlueck]

@Fantast: Where is Access actually running? That is what is critical.

I assume each computer, no matter how it gets to the database, has its own local copy of Access installed. And that is where the variables / VBA will be isolated to... no matter if you have VPN's or what not connecting back to the shared Access database file.

Even in a Citrix environment where Access would be running on a Citrix app server, I still believe each user running Access would end up with their own instance of Access, thus its own VBA variable pool, just it would be in the container of the Citrix app server and not on the local machine.

 

[DJkarl]

@jleach
I have to take both possibilities into account. And obviously the second scenario, where multiple users will use the same file from several computers is what concerns me.

@mdlueck


This is still unclear to me. Consider user1 and user2 both logging on to the shared drive K through a VPN connection, using a public computer, and execute an accdr file (being only a FE). They do not download the file to the public computer, they execute it directly from the K drive. I would assume (and was hoping) that the accdr would run on the OS of the public computer they are using, and thus all variables created during their session would only exist for them. So if in VBA a new instance of a class is created when the file is opened, that class would exist independently on both public computers and will not be effected for user2 when user1 edits a property of that class. The same would go for public variables of course.

Am I wrong or is the above correct?

Thanks for bearing with me.

You are correct so far as VBA is concerned, variables are just pointers to where the data is stored in your computer's memory, so it is not shared across instances. If you were using database properties, or tables to store variable information then that would be shared.

I will throw my two cents in as far as using Access over a VPN, don't. Nothing but headaches and corruption in your future, understand it's not your call, but if you are supporting this database, speak up now or be prepared for a lot of unexpected "work" to come your way.

 

[mdlueck]

Your scenario is likely to kill your db sooner or later.

Ja.... !!!! The OP best not try what they describe!!!

 

[Fantast]

Thanks for your input spikepl. This is indeed what I expected.

Do you still expect corruption risks if the forms do not edit or add any data in the database, but only create SELECT queries based on the form input?

 

[mdlueck]

I will throw my two cents in as far as using Access over a VPN, don't

Ja...!!!

The application I am developing with Access is able to operate over a VPN, but the VPN links back to SQL Server ONLY. Each computer has its own instance of the Access FE DB file which is executed on the local C: drive. Such is Access in a Client / Server environment, and SQL Server = Client/Server, not Access by itself!

 

[Fantast]

@all
I will definetly discuss this the coming weeks with the ones responsible, and take all the information above into account. It is good to know all the information above, both for the near and further future.

 

[spikepl]

The ways of Access are inscrutable. But the slowness will be evident. You may consider moving your backend to SQL server, as mdlueck suggests. At least that is a proper server. But moving is a whole new can of worms, unless you have some experience of SQL server or can get support.

 

[Fantast]

To be honest, from the start of this project my personal preference was to work with a SQL server and building all the forms in PHP on an APACHE server. This is also what I advised, multiple times. Unfortunately it was rejected, multiple times~

I think my question is pretty much cleared up now. Thanks all for your advise.

 

[spikepl]

LOL - the noobs in suits win again ?

 

[Fantast]

Well, "winning" is only relative of course. Their choice will make them loose on accessibility convenience, report generation speed, ease of future changes implementation and independency. Of course I would rather build a really good tool and be proud of it, but being restricted like this also has its advantages. I learned a lot during past couple of weeks~