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.

 

[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.

 

[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.

 

[abnorth]

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.

No, we are continuing to use that solution to ensure only one person is using it at once. As I said, it was our initial solution in lieu of another option and we are doing that until we can properly fix it. All I meant by my message was that we had already enacted your suggestion.