Front End / Back End DB

[Jerry8989]

Hello everyone,
My question is about front end/back end databases.
If I keep my front end on a shared network drive and allow multiple users(10-15) open the front end from this network drive will this cause me any issues or will access open an independent copy of the front end for each person?
Will there be any locking issues with the data?
The reason for this is that I can't send the front end to people because new people will need to use it on and off and for updates I can't send everyone the front end.

thank you
Jerry

 

[georgedwilkinson]

Everybody needs their own front end. Bob Larson has a free tool that can help you manage it: http://www.btabdevelopment.com/main/MyFreeAccessTools/tabid/78/Default.aspx

 

[pbaldy]

You run a serious risk of corruption if multiple people open the same copy of the file concurrently. Each user having a copy on their PC is the ideal solution. A workaround would be to give each user a separate copy on the server. That would be less than ideal performance wise, but should avoid the corruption issue.

 

[Jerry8989]

George,
Why doesn't it work the way I described?
I'm trying to find the best way to describe it to my lead. He wants one copy that everyone opens.

Thanks for your reply

 

[pbaldy]

Microsoft recommends it:

http://msdn.microsoft.com/en-us/library/aa167840(office.11).aspx

along with

http://www.granite.ab.ca/access/splitapp/why.htm

http://www.members.shaw.ca/AlbertKallal/Articles/split/

 

[Jerry8989]

Paul & George,
Thanks to both of your for your replies

Jerry

 

[georgedwilkinson]

No problem. I hope you get the lead on board. Let us know if you need more help.

 

[boblarson]

...describe it to my lead. He wants one copy that everyone opens.

Well, one thing - if he does not let it happen where everyone gets their own copy of the frontend, it will become crystal clear as you keep having to replace the frontend because it has corrupted and nobody can do their work. At least if everyone has their own copy and one goes bad, everyone but the one person can still work. So, financially, having "down time" may also be something to bring up as having it on each computer is a good precaution against losing dollars against the bottom line.

 

[Galaxiom]

Fixing code and structure relatively easy but overcoming a boss's preconceptions can be a struggle. I remember one poster here a while back who had a information mangler who insisted on the entire database being a huge single table.

The poor guy was trying to find a solution to adding fields automatically and other atrocities. Of course nobody was interested in contibuting to a shemozzle like that except to bag the whole idea.

One way to drive home the potential problem is to "manifest" an error with the predicted symptoms.. You wouldn't get away with it too soon after speaking to the boss but a preemptive strike could be plausible. You would want anyone important in IT admin on side our you could get rolled.

Make sure you keep good backups of your front end.

 

[Banana]

One thing I also would want to drive home is to schedule a meeting or at least communicate in written form to not only the lead but also his supervisor warning that the requisite to provide only one copy will increase risk of corruption & data loss, require additional maintenance. Then attach a rough estimate of how much money it will cost not just in your own time to fix the issues, but also the lost productivity of everyone who has to sit idly while you're fixing the problem.

Then proceed to follow your lead's directions precisely. If he persist down the path, it's not your fault and when the shit hit fans... you can just point to that communique.

Money talks.

 

[Galaxiom]

One thing I also would want to drive home is to schedule a meeting or at least communicate in written form to not only the lead but also his supervisor

While this might be a reasonable action to keep all the stakeholders informed, going over the head of your lead is often a very quick way to make an enemy who will then proceed undermine you at every turn.

 

[Banana]

As first step, that would be a bad idea, definitely. My impression was that the lead in question already was warned of the problems but was being obstinate. If my impression is mistaken, then yes, a more tactful approach is necessary.

Even so, mentioning the cost usually gets somebody's attention far more effectively than "it's how we are supposed to do it" idealism.

 

[Jerry8989]

I will be speaking with him on monday. I think I can hopefully show him the best practices site and show that the FE gets corrupt and then nobody can use it. If after that he still insists then I'm going to go with the flow and if it doesn't work he will eventually make me change it or move it to Windows Forms with c#. Thanks again to everyone for your suggestions.

Jerry

 

[gemma-the-husky]

although it is not desirable, if you dont make use of local tables you may be able to get away with it. Suck it and see

in a lot of my larger databases, i use local tables to store temporary processing results - eg a temp table for the current order that is being input

now if you have a database like that, and allow multiple users in the same copy at the same time, one user may delete another users temporary tables, to create their own .... causing some strange results.

We actually found a (windows) utility that prevents multiple users running the database by putting a lock on the folderi in which the database exists - thereby preventing any possibility of this happening.

 

[Jerry8989]

Gemma,
I have all my tables in a back end DB. The only thing on the front end is the forms and queries. Will the front end still become corrupt in this scenario? I think it is cleaner to have everyone have their own copy. I will create a version table on the front end to keep track of which version each user has. This way when I have the db's shared it will lock the row that one user is in so that the other users can't change it.

 

[boblarson]

Gemma,
I have all my tables in a back end DB. The only thing on the front end is the forms and queries. Will the front end still become corrupt in this scenario?

If you still have multiple users using it from a network location, then yes it is likely going to corrupt. Access is touchy when it comes to running a file over a network. If any network disruption occurs, no matter how briefly, it can hose the file permanently.

I will create a version table on the front end to keep track of which version each user has. This way when I have the db's shared it will lock the row that one user is in so that the other users can't change it.

Unless you find that you have many "collisions" of users trying to save the same data at the exact same time, you really don't need to worry about it. Access uses optimistic record locking (unless you tell it otherwise) and it will lock the record just in time to do an update and then release it. If you do use record-level locking then it is locked whenever someone is on that record regardless of whether they are changing anything or not. For most applications, no record locking is a good selection and just rely on Access to do it when it needs to update a record.