Developing an Access application to execute in a multi-user environment, where all users use the exact same accde.

I've never posted a question with a high priority before but I have a meeting with the client tomorrow and would like to be prepared.  The client only informed me of their final decision 1 hour ago.

I posted a question on this topic back in December.  Please review the question for background.


Prior Question on this topic



The short story is that my client wants to have all users on a multi user network, use the exact same copy of the application accde.  The  client says they have an existing Access application already running in this mode, so apparently it can be done.

I have been developing in Access since Access 2003 and this goes against everything I have ever been told and the information given by EE'ers in the prior post.  I sent the post to the client so they are aware.  I'm having difficulty even wrapping my head around multiple users in the same accde using the same form at the same time but working with different data.


My application is being created in MS Access 2013 and the back end DB is SQL Server.  A lot of development has already gone into this application prior to the client making this requirement known.  

Among other things my application uses linked SQL tables, linked SQL view, forms bound to the linked SQL table, forms bound to local Access tables, MS Access queries, local access tables, global variables and calls many stored procedures.  Let me know if there is any other pertinent information about the application that I need to supply.

I would like to have a systematic approach to undoing what is done to comply with the client request.

Given that I have to conform to this environment my question becomes.  "What do I have to remove/revise in my application to accomodate the environment?"

I've tried thinking this thru and came up with some obvious changes that need to be made.  I'm hoping other EE'ers have written to operate in this environment and can offer insight.  Something along the lines of "To create an Access application to operate in this environment you can't use......" would be perfect but probably not going to happen.

I know I have to replace any local access tables.
I know I have to replace all global variables.

I have forms bound to the back end SQL table.  Currently I am using tables linked to SQL as the data source for many forms.  Will I have to make all of these forms unbound?  Even if I make them unbound I don't understand how two users in the same accde could be using the same form at the same time but entering different data or revising different records.

Do I have to replace my local Access queries?

Can I still use SQL linked tables and views?

What else needs to be done?
LVL 1
mlcktmguyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
So the requirement, which makes you uncomfortable is
The short story is that my client wants to have all users on a multi user network, use the exact same copy of the application accde.
?

This just means that each user gets a local copy of the front-end accde on his machine.

It just not very well phrased..

btw, this is the common way to use Access applications.
0
Gustav BrockCIOCommented:
The short answer is, that the previous question has the answer.

The code etc. will be completely identical, though run from multiple copies of the frontend.

So, have a nice meeting.
0
PatHartmanCommented:
The short story is that my client wants to have all users on a multi user network, use the exact same copy of the application accde.  The  client says they have an existing Access application already running in this mode, so apparently it can be done.
Just because you can do something doesn't mean that you should.

EVERY SINGLE professional who works with Access as well as MICROSOFT itself recommends individual copies for each user.  Sharing a single copy of the FE is a recipe for corruption.  When one person has a problem EVERYONE has a problem.

Ask the client if every user is using the same copy of Word.exe or do they each have their own copy.

It is very easy to manage distribution of new versions.  I use a .bat file that simply downloads the current version and starts it.  You may need something more sophisticated for this client.  You can still use the .bat file method to download but you might need a version check.  The simple way to do this is to have a table in the BE and a table in the FE with one row in each table that includes the correct version.  In your opening form, you run a query that compares the two versions.  If they are different, you stop the app with a warning message and shut it down.

You can get a glimpse of what causes the issue if you notice that every time you open an Access FE and close it, the update date changes even though you didn't change any objects.  When the database is opened, parts of it are loaded into memory on the CLIENT's PC.  That means that 10 clients have part of the app loaded into memory.  What happens if someone does something to cause Access to want to update the form.  Perhaps the user sorts a subform.  This change gets saved to the disk and now what is on the disk is different from what is in memory on the other 9 computers.  Just say NO.  YOU are the "professional".
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Gustav BrockCIOCommented:
Well, to complete the picture, it is possible to run off one single physical file - you just have to mark the file as read-only.

However, Access will pop a message at every launch, that changes cannot be saved which, of course, is right, but as changes to the data happen in the backend only, it is absolutely possible if you don't write to the application file itself.

I know I have to replace all global variables.

That is not true. Variables are set for each session.
But local tables is, per definition, a no-go except in a separate writeable database.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlcktmguyAuthor Commented:
Some clarifications:
Ste5an:
    The short story is that my client wants to have all users on a multi user network, use the exact same copy of the application accde.

?

This just means that each user gets a local copy of the front-end accde on his machine.

It just not very well phrased..

btw, this is the common way to use Access applications.

No, every user is using the exact same accde at the same time.  There are not multiple copies of the accde, they all use the same one.

Pat:  I agree with you 100% and I passed on all of the comments from my prior question to the client but the requirement is there.  I already have a lot of time and money invested in this project.  I can't walk away.  I need to make this work.  They already have an access application from another vendor that operates in that environment.  I just need to figure out how.

Gustav:
Well, to complete the picture, it is possible to run off one single physical file - you just have to mark the file as read-only.

However, Access will pop a message at every launch, that changes cannot be saved which, of course, is right, but as changes to the data happen in the backend only, it is absolutely possible if you don't write to the application file itself.
Gustav, what about forms bound to linked tables and the other items I mentioned (Do I have to replace my local Access queries?
Can I still use SQL linked tables and views?)
If you can provide any more information about what can't be used or how to approach this I would really appreciate it.
0
PatHartmanCommented:
Sorry but no one who knows anything about Access would ever consent to doing this willingly.

The one thing you CANNOT do in this environment is use temp tables.  Variables and temp vars should be fine since they are loaded into memory and not saved back to disk unless you force the save.  I would do as Gus suggested and run the app as read only.  If they complain about the message, you can change to shared mode and let them get what they deserve.  If you need temp tables, you should make tables in SQL Server that include the current user ID and simply add and delete records as necessary.  This changes your query because you need to keep users from conflicting with each other.

I would recommend a CYA letter telling them that you do not recommend this configuration, nor does Microsoft so they are on their own if corruption occurs.  You KNOW that what the client is asking you to do is unsafe.  It isn't illegal or immoral so go ahead and do it but give them a written warning that you can't be held responsible.  I know it is difficult as a consultant but part of what they are paying you for is your expertise.  Just because they haven't had a problem to date with the other app, doesn't mean there won't be one tomorrow.  It is an accident waiting to happen.

I would politely decline future assignments with these people.  They are too stupid for words.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Just echoing what others have said - Just say no. This is not the right way to do things, and if you already have a client who is dictating application framework and infrastructure you're in for a rough ride. The customer is NOT always right, especially in the IT world.

Run far and fast from this. Consider the money you'll lose as a lesson learned and move forward in your professional life.
0
PatHartmanCommented:
I just had another idea - Offer to fix their other project to operate correctly  If they see that you are willing to do this for free, they may understand how serious you are.  Do it for free if you are feeling generous or charge $200 if you are not.  It will take you about 15 minutes once you understand what needs to be done.
0
Gustav BrockCIOCommented:
.. what about forms bound to linked tables and the other items I mentioned (Do I have to replace my local Access queries?
Can I still use SQL linked tables and views?)

Yes, no changes. It even has the advantage that all temp data are written to true temp files in the user's temp folder.

It's extremely easy to check. Just right-click the file, and mark Read-only under Properties.
0
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
What I usually hear people do in these citrix environments is to create a folder (can even be hidden, if you use a launcher of some sort) for each user, and copy the frontend into that folder, and open THAT. That way they are not using the same file, even if they are using the same shared desktop.
0
mlcktmguyAuthor Commented:
Thanks for the follow up Anders.  That is exactly the way several of my other clients handle this.  I've given the client all of the alternatives not to mention the warnings about corruption and unpredictable behavior

After our meeting yesterday it looks like they may re-evaluate.

I read and appreciate all of the 'run from this' comment but in reality that's not an option.  The reality is that this is a large project and I have a mortgage and multiple college tuition's to cover.   Whatever the client decides I will make it work.
0
PatHartmanCommented:
Glad you got it worked out.  If you had mentioned Citrix in the beginning, I would have posted the batch file I use for Citrix installations that gives the users their own personal copy of the file.  I can't tell you the fights I've had with Citrix people over this.  They truly do no understand Access and that is why they always want to do this.  Once they understand that technically the Access .accde is a document rather than an executable, they come to grips with the issues.  But every Citrix installation I have gone through has involved this specific challenge.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.