This shouldn't happen, surely?

[Tay]

I've an unsplit db on a network which is being used by several people. When the db went live, I did split it, however, diabolically slow network speeds meant that after 5 minutes, there was no guarantee that a form would load... More often than not, Access crashed. So, I reverted to an unsplit db, knowing the day would come when I'd have to reconsider...

Today, I entered some data into a form, while a colleague did the same on his machine. I (stupidly?) thought that this would not be a problem; both of our new records would save. When my colleague entered his records, he sent an email notification, via this database, to a manager. The manager received the email and I obtained the reference numbers for these records, which populate the email title. However, when I later looked at the db, I saw that those reference numbers had actually been assigned to the records that I saved, not those of my colleague.

I don't quite understand how this happened. I presume that somehow my records overwrote those of my colleague, but for it to happen twice seems like more than coincidence. Has something else caused this to occur?

Whatever the cause, presumably the only way to avoid this is to split the db? And if so, how do I get users to use the new front end? As our IS department will not maintain the database (no budget, so they want no involvement), I don't see how I'll be able to install it on each user's machine. I'd hoped that I could let them use one front end, which will reside in a shared network drive. Does anyone know of any inherent problems with this approach?

 

[pbaldy]

With an unsplit app, you both had the same form open. I'm not surprised your entries conflicted with each other. More info and a method to update front ends here:

http://granite.ab.ca/access/splitapp/index.htm

 

[gemma-the-husky]

slow loading forms, are probably caused by a database design/windows issue - not by network speeds

 

[Tay]

Thanks for your replies. I'll start with a question. With a split db, as I understand it, I've two options. I can either use a FE which all users access in a shared network folder, or I install FE's on individual users' machines. If I've got this wrong, please tell me.

Presuming that it is a feasible approach to have a shared FE, then

slow loading forms, are probably caused by a database design/windows issue - not by network speeds

I'm probably being ignorant here, but if the unsplit db that we've been using resides in the same folder on the network that the newly split db is on, why should there be such a massive difference in performance? Is it because splitting means more network traffic between BE and FE?

As I knew the need to split was inevitable, I had read various online comments re: performance after splitting and took action to minimise impact, particularly with regard to forms recordsources. I also found excellent info here: http://www.access-programmers.co.uk/forums/showthread.php?t=34895 .

The link you pointed me to, pbaldy, was useful, thank you. Although this offers a way to distribute FE's to users, this involves money. I work for the NHS; there is no money available. We're not allowed to save anything at all on our machines, everything has to be accessed via the network. A conversation with an IS bod a while back informed me that local installation will not be allowed as there is no money for this.

I'm really unsure what to do. In a past life, I created a db, split it, and found very little difference in performance. What I've currently got is not acceptable, particularly if the db crashes while opening. I do strongly suspect a flaky network is the culprit as most documents on shared drives take an age to load; Excel spreadsheets (heavy on calculations) can easily take 5 minutes to load.

 

[RuralGuy]

The only reliable option in a separate FE in each users machine or separate FE's on a server. You do *not* want separate users in the same FE. The best MultiUser site I've found is: http://www.accessmvp.com/TWickerath/articles/multiuser.htm

 

[gemma-the-husky]

eg if you all share the same front end, and any procedure messes with some internal tables - then these change for all users.

slow loading may be because

a) you need to keep a permanaent connection to the back end and
b) sharing violation notification.

does it work ok with 1 user?

try this for hints - an oldie but goodie

http://support.microsoft.com/kb/889588

 

[Tay]

Thanks RG and Dave; there's an awful lot of info in those links and I expect there's quite a lot of optimisation I can do.

I see the problems now with users accessing one, shared FE on the network. I'm stumped as to what I can do to get around this. While I've not tried to do it, all employees are 'not allowed' to install anything locally. If I try to do it, either IS will somehow know that I have, or, I'll be in trouble as I'm sure we had to sign something to say we comply by IS' T&Cs... I'll look at work tomorrow, but I have a nasty feeling that it is specified that we mustn't install anything locally.

I'll see what tomorrow brings, but I'm not hopeful.

 

[RuralGuy]

I understand and good luck.

 

[Galaxiom]

I run databses from in the user's application data folder because they can write a copy of the front end there.
The path is returned in VBA with Environ(%APPDATA%).

Of course one would not want to make that a Trusted Location. Consequently I use mde files with a digital signature.

 

[gemma-the-husky]

The trouble is, IT guys like to keep close possession of stuff (possibly with good reason), and regard access as lightweight (which it isn't)

A lot of inventions in history have happened from people not quite following the official company line. No doubt you get Word and Excel. They should exclude Access from the restricted installation list.

 

[Galaxiom]

Although in some terms a front end could be considered an application, it is not really quite the installation of a program in the usual sense. It is a document just like an Excel spreadsheet or a Word document. The program is the Access runtime.

If the Access front end is a program then so is any other Office document that contains VBA code. Even Acrobat files have code in them but nobody calls a pdf an "installation".

That would be the line I would use anyway.

 

[Tay]

Thanks for your replies.

Re: installing the FE locally on users' machines. While I get that this isn't really an 'installation' as such, we're not allowed anything; not even one measly Word doc to be stored on our machines. There would be a lot of machines to install on, so I'd suspect that IS would notice. My manager advised me against local installation. She'd rather risk corruption from an unsplit db than subject users to horrendous loading times.

In light of this, I'll return to the problem that prompted me to post here. I created a couple of new records at the same time as a colleague. He was notified of the reference numbers (autonumbers). However, the database shows these ref's are for the records I created, not him.

Was this caused by me not setting record locking? Would my new records overwrite those of my colleague? I'm just trying to work out what I'm up against. If I can simply employ record locking to stop this happening again (and I'll risk corruption), then this is my best approach. But, if this problem occurred via other means (perhaps corruption?), then sorting the record locking situation won't help me.

 

[gemma-the-husky]

its probably caused by the coding in your database. if two of you are using the same database together, you might be interfering with each others data, and producing this effect. Depends what mechanism you are using. its like an excel worksheet. With excel, when one opens a sheet, the second user gets a "read only message". There is no read only in access. Everyone can affect the data simultaneousely. therefore your design has to take this into account.

thats why galaxiom suggested an alternative location for each user to store an individual copy of the database (post 9)

 

[Galaxiom]

How about this for a work around the stupid bureaucrats?

Copy the front end to the user's Application Data folder when they want to use the database and delete it when they logoff. This way it is never "stored" on the PC.

Although AutoFEUpdater is a slick tool it is quite simple to write a script to perform these actions without all the bells and whistles.

If you use Roaming Profiles on your domain with profiles deleted at logoff policy then it is even simpler. The FE becomes part of their profile and is never "stored" on the PC as such.

If you didn't want it in the profile you could use the Local Settings folder. This is not part of the profile and would save you having to use a delete script.

Local Settings path is available with:
Environ("USERPROFILE") & "\LOCALS~1"

 

[The_Doc_Man]

If you cannot even keep a local document on your assigned PCs then your original design concept was screwed from the word GO. (Or, if your wife is Cajun like mine,... GEAUX!)

So HOW do you keep separate, non-collaborative documents? Do you have individual folders on a share? Or is it a free-for-all? I cannot envision a work environment that doesn't allow private, local documents.

The essence of your direct problem as described in this thread is that you MUST keep the BE separate from the FE for a variety of reasons. But if you cannot do that, then it is time to abandon this project. You categorically will not get this working reasonably without SOME degree of separation of FE from BE and FE copy from other FE copies.

Why? File and record locking. You lock everything you open, but if in a private FE database, your FE locks would be local to your machine. Nobody else can lock your private FE. Two users can open the same form - because they are in different Jet Engine workspaces.

An FE shared by many from a single folder might as well not be split because THAT is where you are going to have your socks eaten. It is the locks associated with the FE that get nasty, assuming you programmed the BE to optimistic locking or no locks for Read-Only uses.

I see no solution for you until you convince, by assassination if necessary, the IT group to allow some kind of local documents.

My incredulity comes because I see a violation of basic security principles here and that is what astounds me more than anything else. What I see is a bunch of LAZY IT guys. (There, I said it.)

If you have no local files and everything has to be on a share, then essentially, that networking security job could be done by a little old man with a leaky bladder and a need to hit the bathroom 5 or 6 times a day. If everyone is in the same share and no one has private files, you have NO security. No access control lists. No differentiation of permissions. Someone needs to take a wet rubber chicken to those guys. You may show them this reply. To the IT guys: Have you guys NEVER heard of separation of duties as a security principle? The environment described here is a melting-pot, not a layered or other kind of separation.

 

[Galaxiom]

DocMan is perhaps a little OTT with the security comments as it is quite possible and not uncommon to have a separate network share for each user in addition to "shared shares".

However the comments about the IT controllers still applies. Since your manager would settle for a "solution" that involved corruption rather than take on the IT department indicates you must surely be working in a governement organisation.

Consequently, even assassinating them is not going to work.
Have you ever considered looking for a different job? I would.

 

[Tay]

I tried to post yesterday, but the forum appeared to be having problems.

If you cannot even keep a local document on your assigned PCs then your original design concept was screwed from the word GO.
.......
I see no solution for you until you convince, by assassination if necessary, the IT group to allow some kind of local documents.
.

I'll give you some background to this. And yes, Galaxiom, I work for the National Health Service. Hence my tale of woe...

I was asked to create a new db soon after I applied for this job in May. A few months later, our IS group was outsourced. Staff, systems, computers and even telephones. It is no longer NHS property. And this is the cause of the problems. Beforehand, if you had good reason, things could be (and were) installed on people's machines. Now that IS is essentially a commercial enterprise, they won't allow a mere mortal like me to install FE's; they'd have to do it themselves. And if they do it, they want to be paid to do so. They'd also want to maintain the db and thus demand more money for that. As there is no budget available to pay them to do this, we can't proceed. Had I started the db earlier, before the outsourcing happened, it would have been included in their contract, and I'd have a nicely split db working just fine (unless they chose to dabble).

"So HOW do you keep separate, non-collaborative documents? Do you have individual folders on a share? Or is it a free-for-all? I cannot envision a work environment that doesn't allow private, local documents. "

Before I think about going down the workaround route suggested by Galaxiom, a thought struck me (prompted by The_Doc_Man's post). While we mustn't store anything locally, we do have our own 'drive' each on the network. Therefore, could I not arrange for users to store their own FE on this drive, and then link it to the shared drive?

The bureaucracy that exists following the outsourcing is appalling. One of my colleagues has been battling with IS (and has lost) to download the freeware app Audacity. Our patch of the NHS has a few call centres, and all calls are recorded. For customer service improvements in call quality, my colleague wanted to store snippets of calls in another db which could then be accessed by the training managers. Audacity would do the job nicely, and would reduce disk space as we can only currently save the entire call that we're interested in. IS have stood firm and will not permit just one user to download and install this application (yet the city council has it installed on its public-access pc's in libraries). Meanwhiile, all network users get sent emails from IS asking us to deleted anything not necessary from our inboxes and personal network drives as they're running low on server disk space.

 

[Galaxiom]

I have seen this kind of thing and I think your situation is even worse than originally contemplated.

My wife is a school teacher. She asked me to volunteer a little time to check out issues they were having with their network. I sorted out a couple of hardware issues and then started on trying to find why it took twenty minutes for users to log on.

The users have roaming profiles from a domain controller in the Education Department datacentre in Sydney and the school is in a small village in rural area connected by an Internet service shared by all users and their library database system which runs on Terminal Services. The server shares are also in Sydney. Opening and saving documents is also a slow and painful painful process.

The department promotes the system for its ability to support a user to log on and access their documents anywhere in the state. However what might work reasonably well in a Metropolitan school with fibreoptic Internet services doesn't go so well out here.

It is highly likely your NHS managers elected to do something similar with everything stored on servers in the IS datacentre. They would have been wowed by the IS promotions of how they would handle security, backups etc on giant server clusters with multiple redundancy resulting in incredible reliability. They would have thrown around the word "cloud" a lot and explain how the whole thing would be transparent to the users with every PC identical but providing a consistent environment that travelled with the user whereever they logged on.

The back end of your split database is probably on the other end of an internet connection which would explain why everything takes so long to load.

A situation with remotely stored Word or Excel documents is annoying but managable. But Acccess cannot work well like this. The connection to the back end must be fast and reliable or corruption is going to be a problem. The latency involved would make the conflicts in the shared unsplit database the norm rather than the exception.

The backend needs to be local. It could be on one of the PCs but there is no way the IS it going to cooperate with that. There would be firewalling problems as the system would be designed to reliably keep anyone out of the other PCs on the network.

The alternatives are to construct your database as a web application or run the front ends on the remote server and operate them via Terminal Services. Either way will involve paying vast sums of money to IS.

Looking for that new job is definitely the best solution to this problem. You will have nothing but grief in this NHS quagmire.

 

[gemma-the-husky]

and this thread could probably be usefully forwarded to 10 Downing Street - except no-one there would bother at all, I expect.

 

[Galaxiom]

Bureaucrats love outsourcing because it fixes costs. Outsourcing the hardware and support would have been seen as a perfect solution. No pesky budgets for hardware, accounting depreciation for computer assets, support and IT management salaries etc.

Unfortunately those making the decisions have no idea the practical differences between hardware configurations and what that means to services running on them.

One large company in Austrailia got rid of their own IT department and outsourced their entire IT support. The managers applauded themselves until they reviewed the actual costs of the contract and discovered support calls were costing them several hundred dollars a time.

Of course every call was documented in a quality assured system revealing that many of them were quite trivial and could have been dealt with internally in a thirty second call to their own IT staff if they still had them.

Probably the one thing that should be outsourced never will be. The managers' decision making roles.