No warning when multiple users are editing? (1 Viewer)

[abnorth]

We use access as an inventory, and sometimes one person is adding an item while another is looking up or marking another item as empty. Our database is stored in a shared google drive and we use file stream to open any files. With excel, word, or powerpoint, the real-time presence notifies us if another user is editing a file. Unfortunately, access has no notification that someone else is using the database, so we are having some problems where the file is locked by the first user and the second user's changes aren't being saved. Is there a way to add a warning when someone else is editing? We tried opening the database in exclusive mode so it would warn us with the "file in use" error, but it didn't seem to work as two people were still able to open and edit the database at the same time without a warning, even in exclusive mode.

 

[theDBguy]

Hi. Welcome to AWF!

First of all, using Access stored in Google Drive is a bit risky. You might lose data even if something bad happens with the drive connection during use. In any case, since there is not built-in facility to notify when two users are currently working on the db, you will have to build that into the database yourself. There were some discussions and demoes on this topic available for references. I'll see if I can find a link for you.

 

[Gasman]

We use access as an inventory, and sometimes one person is adding an item while another is looking up or marking another item as empty. Our database is stored in a shared google drive and we use file stream to open any files. With excel, word, or powerpoint, the real-time presence notifies us if another user is editing a file. Unfortunately, access has no notification that someone else is using the database, so we are having some problems where the file is locked by the first user and the second user's changes aren't being saved. Is there a way to add a warning when someone else is editing? We tried opening the database in exclusive mode so it would warn us with the "file in use" error, but it didn't seem to work as two people were still able to open and edit the database at the same time without a warning, even in exclusive mode.

Is the DB even split? From what you are saying, I suspect not?

 

[abnorth]

Hi! Thanks for your replies.

Gasman - No it is not split. We do use forms to edit the data in our tables but don't have FE/BE files (I just learned what those were today in fact). Unfortunately we are a small company that does not have an IT team, we are all just figuring things out as we go along. My coworker that set up the database who actually had a CS degree no longer works here and wasn't able to fully finish setting it up before he left. I've taken it upon myself to try to improve our setup despite it not really being in my job description as a chemist.

theDBguy - We store all our data in google drive because we don't have a server and want everyone to be able to fully access it (no one stores data on their computers that isn't also in the drive). We do perform regular backups to a hard drive so that we don't lose any data, but in the lifetime of the company, there's never been a lost data issue with the setup we have.

A few follow-up questions as I've read more but am still not well-versed:

• If I split the database, will a FE file stored on a local machine successfully update the data in a BE file that is stored on drive?
• What if two separate people edit the same field in their own FE files? How does the BE then appear (does one user's edits still get precedence over another? if so, I am left with the same problem).

 

[theDBguy]

Hi! Thanks for your replies.

Gasman - No it is not split. We do use forms to edit the data in our tables but don't have FE/BE files (I just learned what those were today in fact). Unfortunately we are a small company that does not have an IT team, we are all just figuring things out as we go along. My coworker that set up the database who actually had a CS degree no longer works here and wasn't able to fully finish setting it up before he left. I've taken it upon myself to try to improve our setup despite it not really being in my job description as a chemist.

theDBguy - We store all our data in google drive because we don't have a server and want everyone to be able to fully access it (no one stores data on their computers that isn't also in the drive). We do perform regular backups to a hard drive so that we don't lose any data, but in the lifetime of the company, there's never been a lost data issue with the setup we have.

A few follow-up questions as I've read more but am still not well-versed:

• If I split the database, will a FE file stored on a local machine successfully update the data in a BE file that is stored on drive?
• What if two separate people edit the same field in their own FE files? How does the BE then appear (does one user's edits still get precedence over another? if so, I am left with the same problem).

Hi. Sharing files through cloud storage is not a problem. In this case, the problem is specifically using an Access database stored in a cloud storage. If you split the database, which is highly recommended, it won't eliminate that problem. You still shouldn't use Google Drive to store the BE file.

In your current non-split configuration, do you know if more than one user can work in the db (I mean updating records) at the same time? Or, are you all somehow making sure that only one person is using the db at one time?

 

[The_Doc_Man]

OK, a couple of comments and a couple of answers.

If I split the database, will a FE file stored on a local machine successfully update the data in a BE file that is stored on drive?

Yes. In fact, once split, that BE file is the only place that SHOULD be updated. In a proper split, there is nothing in the FE to be updated.

What if two separate people edit the same field in their own FE files?

Depends on several factors, but generally the person who edits 2nd is the one whose update hangs around.

Now, as to the shared Google drive, you will not have really good results if your network develops any hiccups at all. But my question is about your network structure. From the way you talk, everyone is on the same network at your office. Is that true? Because if you have everyone on the same in-house network, you can add just one more machine on the network that you can leave running during business hours, one that is set aside for this purpose. You can place the BE on that machine. It does not have to be a server. I.e. not running Server 2012 or some other specific version of Windows destined to be a server. The protocols used by Access DO run peer-to-peer. At worst, you might have some tricks to play to get the file permissions right for the folder holding the BE file.

If you actually have remote users, that can be a serious problem because they can't so easily be on the same network in that case. In that case, you would need to look to RDP or a product like CITRIX to manage that safely.

The reason we are pushing you away from the shared Google drive is that not all of those cloud-like drives permit the protocols that you need for Access. And one last thing - an explanation of something you said in your first post.

With excel, word, or powerpoint, the real-time presence notifies us if another user is editing a file. Unfortunately, access has no notification that someone else is using the database,

This is by design because of the difference in protocols used by the various utility programs. None of Word, Excel, or PowerPoint are designed to be real-time shared, but Access EXPLICITLY is designed to do this. That's why I mentioned protocols earlier. When you open a Word, Excel, or PowerPoint document, edit, and close it, you replaced the ENTIRE file in the process. That is, the file is not selectively updated. It is totally replaced. But with Access, you CAN and often DO replace just a part of the file. AND when you do, the other parts of the file remain as they were, very likely occupying the same disk blocks as they did before you opened, updated, and closed the file. Access uses a protocol to allow that fractional update through the file system whereas Word, Excel, and PowerPoint do not. Therefore, since you are going to totally replace any of those files, you don't need that fractional protocol (called Server Message Block or SMB, a member of the TCP family of protocols).

 

[abnorth]

Hi. Sharing files through cloud storage is not a problem. In this case, the problem is specifically using an Access database stored in a cloud storage. If you split the database, which is highly recommended, it won't eliminate that problem. You still shouldn't use Google Drive to store the BE file.

In your current non-split configuration, do you know if more than one user can work in the db (I mean updating records) at the same time? Or, are you all somehow making sure that only one person is using the db at one time?

In our current configuration, multiple people can open and view the db, but the first person who opens it generates a locked file, and only their changes will be saved. Others can make changes, but those changes won't be saved and there is no indication to those users that their changes won't be saved. This is the basis of our problem, we are trying to find a way to alert us if someone else is editing the db. Mainly, if we add a new entry, we don't want another person simultaneously adding a different new entry in the same field.

 

[Gasman]

I once worked at a site, where they used a wooden flag on a users computer to indicate who was TDS Master, and would ask them to swap the TDS files over.
When we joined, we were in different parts of the building, so that was not going to work.

I created a small command that you HAD to use to become TDS Master. That created a small file, that was checked on startup, and would report 'AbNorth is TDS Master' if you had left yourself logged in. You were meant to login, execute the switch or whatever and log out for the next person.

All that is just to explain you could do pretty much the same thing, though TBH I would not recommend it, but just putting it out there.
You would also need a reset procedure to clear that flag, if Access crashed for some reason for the person in the DB at the time.

Much better to split the DB and have everyone go to that. Does not have to be a server, could just be someone's PC, probably the one with the most memory/power?

However desktops are so cheap nowadays, a dedicated PC would be best.
However as I found out, maximum users on a non server O/S PC is 20.

 

[LarryE]

Here is what I would do if I were in your position with no IT support and no one to help that knows ACCESS:

1. Copy your database and rename it. Make an extra backup copy too.
2. Follow the directions below at 3. to split it into separate front-end and back-end files (this is quite easy. ACCESS does it for you if you click Database Tools>>Access Database). Once this is done, a separate BE file is created in your directory and the tables in the new front-end file are linked to the back-end file. This is how ACCESS allows multiple users at the same time. The front-end file tables are linked to the back-end file data tables. But two different users still cannot edit the same RECORD at the same time even though they are accessing the same data file tables.
3. Open your newly copied file
4. Close all Forms
5. Click Database Tools>> Access Database to begin the splitting process. During the split process you can tell ACCESS to locate the new back-end file to your Google Drive.
6. If not already done, copy the new front-end file to your individual PC and open the original front-end file.
7. Go through the steps indicated to locate the back-end file on Google Drive
8. Open each form and report to make sure they work.
9. Copy the front-end file to another PC and open it. So now two different users are using the same back-end file. IMPORTANT: All users need to have their own new front-end file on their own PC. Copy the new front-end file to each users PC.
10. Test completely. I don't know anything about Google Drive, but you should know shortly if it will work as a back-end file location and if multiple users can access it at the same time.

ACCESS was designed so that multiple users can use it at the same time but you need to do it properly to begin with. If you find you are having problems using Google Drive, then you will probably have no choice but get a network drive or not use it in a multi-user environment.

 

[theDBguy]

In our current configuration, multiple people can open and view the db, but the first person who opens it generates a locked file, and only their changes will be saved. Others can make changes, but those changes won't be saved and there is no indication to those users that their changes won't be saved. This is the basis of our problem, we are trying to find a way to alert us if someone else is editing the db. Mainly, if we add a new entry, we don't want another person simultaneously adding a different new entry in the same field.

We probably have to become familiar with your network setup to fully understand your situation. When you use Google Drive, does that mean everyone's Google folder is placed on a network shared folder where the DB is stored?

As was already mentioned, you can create a batch file or a VB script to start the database, instead of just double-clicking on it or using a shortcut on your Desktop. The script would check for the presence of the lock file and let you know if it's safe to continue or give you an option to bow out.

 

[The_Doc_Man]

In our current configuration, multiple people can open and view the db, but the first person who opens it generates a locked file, and only their changes will be saved. Others can make changes, but those changes won't be saved and there is no indication to those users that their changes won't be saved. This is the basis of our problem, we are trying to find a way to alert us if someone else is editing the db. Mainly, if we add a new entry, we don't want another person simultaneously adding a different new entry in the same field.

The usual way to do this is to have an "activities" table that you update when someone is about to add a record to a table. Your form has to manage the activities table to make an entry BEFORE you actually allow the update process to begin and to remove that entry when the update has been completed. Which then means that before your form updates the activity table, it has to see if someone else has a potentially conflicting update underway, and just refuse to proceed in that case.

Take a look in the "Similar threads' below to see the advice we gave Krayna. Her problem is not that different than yours.

 

[abnorth]

As was already mentioned, you can create a batch file or a VB script to start the database, instead of just double-clicking on it

This seems like the type of solution I am looking for, I just don't know how to go about doing it. I will look into it more!

Take a look in the "Similar threads' below to see the advice we gave Krayna. Her problem is not that different than yours.

I did read Krayna's thread. Unfortunately, most of what was said about how to implement the fix went over my head. Thank you for your explanations though, they were helpful for me to better understand how access works.

I will look into the activities tables idea as well, and also use LarryE's how to for splitting the database.

 

[theDBguy]

This seems like the type of solution I am looking for, I just don't know how to go about doing it. I will look into it more!

Should be easy enough to create, but let us know if you need any help in that area. Good luck!

 

[LarryE]

Ooops...ignore #7 in my response. It's already completed in #5. Sorry.

 

[Pat Hartman]

What if two separate people edit the same field in their own FE files?

The person who saves first succeeds. The person who saves second gets an error message with three confusing option including overwriting the updated record. The users need to be instructed that when they receive this message, they should always choose cancel and perform their edit again. In most applications, this is actually a rare occurrence. Not so with your current cloud situation. You will constantly be conflicting with each other since the save process is a full file replace and that is why sharing can't work with a cloud drive.

ACCESS was designed so that multiple users can use it at the same time but you need to do it properly to begin with. If you find you are having problems using Google Drive, then you will probably have no choice but get a network drive or not use it in a multi-user environment.

Under NO conditions will this work for you even after you split the database properly. As Doc has written multiple times, cloud drives to not support the Windows file sharing protocol that Access relies on.

The method being talked about now won't work either although it is less dangerous than your current set up. I'm pretty sure that changes to the database are not saved until you close. It looks like a copy of the file is kept in memory or maybe on a local drive and updates are saved there temporarily and when you close, the entire file on the cloud drive is replaced. We were just helping someone within the past week or so who thought his FE had become Disconnected from the BE (only his BE was on the cloud drive). Turns out that there was a network interrupt that wasn't apparent to him because the BE he was updating was local and it was only when he went to close the app did it become apparent that his local BE was now out of sync with the one on the cloud drive.

So, the procedure to implement the log file would be to open the app, check the login table to see if anyone is logged in and if no one is, log in yourself. Close the file and reopen it. Then check to see if it is still you logged in. Also EVERY update a form makes should compare your IID with the one in the login table. Remember, computers are fast. Fast but dumb. Two people could open the app, bang, bang. The logged in table is empty. so each user decides to log in. User 2 saves first. User 1 overlays when he saves. Both think they have sole control over the database. That is why the additional check would be required before any update to ensure that you are the one logged in And then one last time before you close the app to prevent overwriting someone else's work.

A lot of work and still not fool proof because you would have to keep closing the app to reopen to ensure you were still the logged in user.

The suggestion to add a separate permanent computer to the LAN to substitute for a server is your best bet. If you have a server, you can put the Access BE there.

Just because your coworker had a degree in computer science didn't mean he knew anything about Access. Too bad he didn't know what he didn't know or he never would have created this set up to begin with. He made two serious errors
1. Not splitting
2. Assuming that Access worked like Word and excel
Who knows how bad the mistakes in the app will turn out to be.

 

[abnorth]

Just because your coworker had a degree in computer science didn't mean he knew anything about Access. Too bad he didn't know what he didn't know or he never would have created this set up to begin with. He made two serious errors
1. Not splitting
2. Assuming that Access worked like Word and excel
Who knows how bad the mistakes in the app will turn out to be.

All my coworker had time to do was to import our previous inventory excel spreadsheet into access before he left the company. It was one of the last things he did, and he told us it was only a half-finished job.

I don't have the authority to move it off of google drive and my boss is currently out of town, so that fix will have to wait. Until then, I successfully split the database! The back end is updating properly from changes made to any of the front end files.

 

[Pat Hartman]

You are in an extremely dangerous situation. You have no idea if one person's updates are clobbering another person's.

Just FYI, any person who develops an Access application for use in a multi-user environment knows enough to start with a split database. It is no more work to have a separate BE than to have all the tables included in the FE. In fact, starting with everything all together just makes an additional step and complete retesting required once the app is split.

For now, your best bet is to make sure that only one user at a time is activly using the database. Use a separate excel file in the same directory on the server to log in and out. Tell people to make sure their phone/email is included so another user can easily contact someone to make sure they didn't forget to log out.

It also wouldn't hurt to keep paper logs so you can check tomorrow to ensure what you entered today is still there.

 

[abnorth]

For now, your best bet is to make sure that only one user at a time is activly using the database.

This was our initial solution when we discovered two people tried to enter different new chemicals into the inventory at the same time. We only have 3 people who use the database, so it's not too cumbersome, just an extra step to check if someone is using it.

 

[gemma-the-husky]

Out of interest, are you in the UK? If so you probably use Sage. I believe Sage has the same issues for the same reasons, when you try to store the Sage data in the cloud, although Sage may actually have a fix.

When multiple users open an excel file together, Excel warns some users (all but the first user) that they only have a read only version. Access is automatically not read only for any user, but the trouble with one drives, drop boxes and so on is that a user is working on a local copy of the drop box, which is then subsequently synchronised with the cloud data. It's this synchronisation that is not possible with Access. True Access just changes part of the file, so multiple users can work together. These cloud storage systems replace the entire file, so that effectively only one user is "active" at any time. They can't stitch multiple changes back together. Furthermore, if one user changes data other users won't see those changes until the data is synchronized, which compounds the problem.

I think this is the case with Sage, but I may be wrong. However this is certainly the reason that Access can't be used in this way. And the thing is, if you try to use it this way, you will have no idea which changes have actually been incorporated into the data file.

Sorry, but I hope this helps. If I am wrong, perhaps a better informed FM can give more information.

As far as I am aware, you will need to use Access one user at a time, and be sure that changes are synchronised before a different user can take over. So it's a little bit worse even than making sure that on other user is active. It's also making sure that changes have been synchronised. For big databases, this will not be instant.

 

[Pat Hartman]

This was our initial solution when we discovered two people tried to enter different new chemicals into the inventory at the same time. We only have 3 people who use the database, so it's not too cumbersome, just an extra step to check if someone is using it.

But, you're OK with PersonA overwritine PersonB's changes??????????????????

You have no charter to create an actual solution that solves the problem safely so you are discounting a manual method that you need for the interim until you have a real fix??? I don't get it.

The two best solutions require Citrix or Remote Desktop, both of which you can host on your own server but since you don't have a server or anyone to set this up, it is too big a project. You can use a third party but that cost ~ $40 per month per user last time I checked. As long as everyone is on the same LAN, you can just add a plain old PC running Windows and load the BE on that. You'll have to set up your own backup or you risk loosing all your data but that is something you can actually do yourself with limited IT skills. Another solution you might be able to implement is to use SharePoint. I don't recommend SharePoint but as a last resort, you can give it a shot as long as you will never have more than about 5,000 records in any table. You would import the tables into ShrePoint as lists and then have the FE link to the SharePoint lists. The advantage of that is that the users can actually work remotely and don't have to be tethered to a LAN. The only other solution is to rewrite the app not using Access at all so you can use it in a browser.

I see that you're new here. You can wait for a miracle and meanwhile risk loosing data but I can assure you, there is no miracle coming. Access simply cannot work safely using a cloud folder, nor can it ever be made to. PERIOD.