What exactly causes errors on unsplit multiuser? (1 Viewer)

Hi folks,
I'd like to understand what causes errors when multiple users simultanously access an unsplit database.
Is it just that the database file is accessed or when changing records?

I want to make an db and keep it unsplit for now during the design stage. However I also would prefer to store it on the company shared folder for back-up purposes, but I wouldnt be able to stop nosey-nellies opening it while I have it open.

I was thinking....

> Open db and show a switchboard.
> On load of switchboard, check if 'dbopened.txt' file exists in db directory.
> - If yes, show msgbox then close db
> - If no, create (empty) file 'dbopened.txt'
> On application close, delete 'dbopened.txt' file.

Im thinking that because the user only gets to a switchboard-style form and doesnt access any actual records, the db is fairly safe from corruption?

Thanks

Not safe at all. It depends, of course, on what happens behind the scenes.

The problem is that when you have an unsplit database and multiple users from different machines open the "shared" file at the same time, their individual file locks are not visible to each other - and neither are their database object locks. You get what is technically called "destructive interference" in which "the left hand does not know what the right hand is doing."

Remember that it doesn't matter where the database resides on a disk. The only thing that mattes is what is in the memory of the machine running MSACCESS.EXE (the Access executable image.) Because the REAL database is what the running image in memory THINKS it is. Thus, two users might try to insert data to a particular table at the same time, and whichever one writes back the update second is the winner - because the first update gets overwritten.

If you have the issue of "browsers" or interlopers, your issue will be that structural elements aren't safe.

Using a switchboard helps, but the truth is that if you are still developing, you do yourself NO FAVORS AT ALL by making the working copy publicly visible - because it is probably not protected completely. Your public production copy should be split and shared according to guidelines published MANY times in this forum. You can use the SEARCH function on the issue of SPLIT databases. Your comments about having a switchboard front-end are a start on how to handle the production copy, but there are other steps as well. Search the form for "Securing a database" to see many articles.

In the final analysis, your main problem will be lock visibility (or invisibility) leading to "collisions" that probably won't be correctly handled, thus causing the "last-chance" trap handler to come into play and leaving the last update activity unfinished. Aborted updates caused by those collisions will lead to corruption because of the inconsistent data content (and structural content) left behind by half an update.

Splitting into front-end and back-end and distributing the FE means that (a) ALL of your queries, forms, reports, and code segments are separate and their locks are private; and (b) if you use optimistic locking for queries and forms, the potential for interference in the tables is a very small (short time) window.

Note also that opening a report is unlikely to cause problems since reports are based on a SELECT and typically do not update anything. They are mostly passive. But anything else (table, query, form, macro) CAN update data. It is the "CAN update" portion that triggers the kind of locking that leads to interference.

one thing that causes issues is putting temporary settings in local tables.

Jim puts something in a table
Fred puts something else in the table
Jim now reads what Fred put in, rather than what he put in.

You have to be extremely careful whenever you let a user share a front end - split or not, to avoid them interfering with each other.

Users "just viewing" might be tempted to change the column widths then save on close when prompted. That is written to the shared file and trouble starts.

There really aren't any good reasons to let the same file be opened by more than one user.

why do you think if you try to open an excel file or word document, you can't if someone already has it open. Best you can do is open as read only and then either not save, or save as a different file. Reason - because the whole file is loaded to memory - which doesn't happen in Access because others will want to see your updates to data straight away, not when you save the file.

You can mimic the Excel/Word 'way' by ensuring all users go to file>options>client settings and in the advance section change the default open mode from shared to exclusive. Note because this is a client setting this then applies to all access files the user opens. It is not something you have any control over.

Access makes changes just by opening the file - take a note of the file date then open the file. Even if you just open it to the access window and don't open any form, table whatever you will see the file date has changed.

I want to make an db and keep it unsplit for now during the design stage. However I also would prefer to store it on the company shared folder for back-up purposes, but I wouldnt be able to stop nosey-nellies opening it while I have it open.

Click to expand...

Why would others be opening the file? Why would they even know it exists?
Are you telling us that the data management/security practices in your organization allow anyone to access any file(s) in backup to do with as they wish?
It seems to me you have several issues/items/concerns.

There is nothing wrong with designing a database or prototyping a design on a single machine -since you would be the only user. You can show/demonstrate to others your design/intentions/prototype. As others have said, when you have multi users working with your database (regardless of development phase or production) you should have a split Access database for the reasons they have provided/experienced.

Some developers always use a split construct.

There are backups and archival backups (usually offsite) with Security, Risk Management and Data Management principles and policies. Data critical to your organization's business should not be vulnerable to unintended access or modifications or theft or loss....

Perhaps you could tell us more about databases, programming and data management in your organization.

Good luck.

I think a few of you have missed the end of my original post and just answered the Topic Header.

Database loads to a switchboard form first, what does the user want to do in the db.. open the form that adds a new employee or open the form that runs x report? Before ANY records are even close to being accessed / changed, there is my customised check for multi-users...

> Open db and show a switchboard.
> On load of switchboard, check if 'dbopened.txt' file exists in db directory.
> - If yes, show msgbox then close db
> - If no, create (empty) file 'dbopened.txt'
> On application close, delete 'dbopened.txt' file.

Click to expand...

Its the very early stages of db opening that i want to run this check and handle it.

jdraw said:

Why would others be opening the file? Why would they even know it exists?
Are you telling us that the data management/security practices in your organization allow anyone to access any file(s) in backup to do with as they wish?

Click to expand...

Imagine a small-ish team of say 15 people, all using just 1 subfolder of a much larger shared drive, a folder that anyone outside the department doesnt have access to. Now organisation security isnt such a big deal, is it

So this db is only for use within this dept and once its made, will be split to fe/be. But for now during design stage, its unsplit. Probably two people will never be opening it at the same time, but I'd like to create that safeguard nonetheless, just in case.

I don't think you have addressed this, or don't appreciate the potential consequences

I wouldnt be able to stop nosey-nellies opening it while I have it open.

Click to expand...

Yes, I understand a small group -- but a department in an org whose backups are generally accessible????

your suggestion is still prone to problems - the file opened by two people still runs the risk of corruption. The other user could just remove or rename the text file. And don't forget corruption comes in two forms - corruption to the data and corruption to the code.

Why don't you just password protect it and don't publish the password? Once the development is complete, you can split the db, and remove the password from the front end when you distribute it (tho I recommend password protecting the back end as a matter of course anyway)

Your suggestion is the same as one I made where they wanted users to share via dropbox, however that is a different situation where it is a copy that is created when the file is opened - akin to the excel/word situation.

I think a few of you have missed the end of my original post and just answered the Topic Header.

Click to expand...

No, we didn't. If you feel the need to protect it in the first place, and are worried about the consequences of having this in a shared area, just understand that STUFF HAPPENS. Your "trusted" cadre of co-workers will look at this thing you are building in your shared area and, being curious, will want to "lift up the hood" to look inside. The ONLY way for them to look inside is to OPEN it, since only Access (MSACCESS.EXE) can interpret an Access database container file.

The MOMENT they open it, they trigger certain locking mechanisms that, because they are on non-shared workstations, will not know about multiple users. If you reach the point where someone has gotten into your code to see if they should be in the database, they are ALREADY IN IT! That code doesn't run in a vacuum and Access will potentially update some internal properties.

The reason you split the FE and BE is because you can make the FE do most of the work locally to decide whether the user should be where they are. A split-and-distributed FE has private locks that are not shared and thus cannot cause access conflicts, so if you did a good job of splitting, your FE infrastructure is all that will be exposed initially. However, if there are linked tables, those tables are open and ready to work.

You can minimize the window for corruption, but if you have nosey nellies in your group, you CANNOT guarantee that corruption won't occur even in the case where Nellie has something open to read it and then you start to modify it in that one-size-fits-all copy. The fact that someone else has it open for reading means that your edit session triggers a second thread of modifications EVEN IF Nosey Nellie closes without saving afterwards. The potential version divergence occurs just by having two inspections occurring at once, and Access CANNOT TELL that Nellie was planning to close without saving. THAT is the situation that leads to corruption and you can't avoid that.

CJ_London said:

your suggestion is still prone to problems - the file opened by two people still runs the risk of corruption. The other user could just remove or rename the text file. And don't forget corruption comes in two forms - corruption to the data and corruption to the code.

Click to expand...

Thank you kindly CJ, thats the information I was looking for.

I understand the situation of the custom temp file being manually deleted/renamed but still hoped the first switchboard form would be safe.

you are arguing from the position of

'I have a petrol car - but I'm only want to add a little bit of diesel'

Running front and back ends is easier for development. In theory your back end should not need much changing and if required you can just update this out of hours when nobody will have it open.

Most updates are done in the front end and you can change the one on your machine and test it properly at leisure before distributing an updated version to all users.

CJ_London said:

you are arguing from the position of

'I have a petrol car - but I'm only want to add a little bit of diesel'

Click to expand...

Its the other way round that buggers up your engine.
I only did it the once ....

ridders said:

Its the other way round that buggers up your engine.
I only did it the once ....

Click to expand...

Same here, when the guy came to pump it out I asked him whether he covered the whole country and he said he only worked locally, he stated, and I quote, 'you'd be surprised how many idiots do this'.

ridders said:

Its the other way round that buggers up your engine.
I only did it the once ....

Click to expand...

With older diesel cars/lorries (ones with the industrial spec engines, practically bulletproof) you can get away with a reasonably high percentage of petrol. In fact years ago this was the recommended way of frost Proofing your diesel. If you expected severe frosts then you would add petrol to the diesel to prevent the diesel from waxing in the fuel Lines. Mind you I do understand that the modern diesel engine is "Wimpy" in comparison to the old technology...

I did it well over 20 years ago with Ford Escort.
Less than quarter of a tank but car still came to a halt and still needed to get it all flushed out.

Got the same comment as Tieval from the bloke who came out to my car. Must have been the same guy!!!