Two Simultaneous Users of one database

[Benvolio1282]

Hello all,

I'm here because I have a problem that I've been wrestling with for awhile, and I can't seem to find a definitive answer by just looking up existing information.



Where I work, we use a desktop database that is stored in a OneDrive for Business folder, so it works like a network folder for our two computers. We're trying to reconfigure the database so that two users can be in it at the same time and be uploading/changing records without causing the others records to not take hold. I've tried splitting the database, and putting the backend on the OneDrive and having the front end on the desktop, but that didn't work either, because whoever the first person is that gets into the database would be the person whose records took hold if both users tried using it at the same time. The only way that I can see this as a usable option though is by creating a batch file, where each I think it would create a new file every time I made changes to the database. I don't even understand that though, so I'd have to research that.

Can anyone give me any helpful advice on how to make an existing database useable by two people at the same time? I'm open to SharePoint too, but I'm not educated on that either.

Thank you guys so much!

[June7]

Each user should run their own copy of frontend. However, even if they both open the same frontend file, I would think still be able to access the backend simultaneously. Unless the backend is somehow set to prevent.

I have never used OneDrive so don't know if that can make a difference.

[ranman256]

Split db IS the answer. I do this for about 5 users. RARELY does anyone lockout anyone else. (special circs)
If you are locking others out , then you need to rethink the entry method.
Instead of letting the user type in directly into the table, use an entry form.
This would allow user to enter a record on his table, in his desktop app db.
Then after all data is in, click SAVE , this would fire an append query to add the data to the main table on the server.

Same with editing...tho it would be an UPDATE query.

[Benvolio1282]

That's how we've been entering information, using forms. One thing I have noticed is that whenever we start using the database in where there is a backend and a frontend, whenever I save, a new backend is constructed, like a copy. Does that help?

Edit: I just got a message on the computer where I logged in second after I tried to save the file, where it stated 'OneDrive could not merge the files, so we created the copy "Database_be-Computer B" so you wouldn't lose any work.', where "Database_be" is the back end of the data base and "Computer B" is the name of the computer where I edited the record second.

[ranman256]

IF you mean you have a db called database.accdb
then when it opens, then a file called database.lccdb appears,
thats OK. thats the locking file for the accdb.

BUT if you mean another .accdb is created then something is wrong.
unless someone wrote code to make backups. dont use the backup db.

If you have an entry form that enters data locally on the PC , and NOT on the network, then you should not get any errors.
Each user must have their own table and form. NOT shared on the network BE.

[Benvolio1282]

Yes, it's creating a whole copy of the database. It's creating a locking file while is open, which goes away once the database is closed, but if I try to enter records then another backend is usually created, with the name of the computer in front of it.

One thing that is true is that when using the OneDrive, in the top toolbar it says "Computer A > OneDrive > Databases > Database1", where Database1 is the name of the database. But then on the other computer it says ""Computer B > OneDrive > Databases > Database1". Could it be that I need the database to be on a true network folder in order for it to sync properly?

And what do you mean by "Each user must have their own table and form"? Do you mean individual forms in the database or individual copies of the database on our own computers? Because we have the latter.

[CJ_London]

I haven't used onenote, but I suspect that two people can't work on the same file at the same time. to get round this, each user has created for them their own personal instance - and when they close the file that personal instance replaces the original file.

This is the situation with Excel and Word even on a server, only the first user to open the file can edit it, any latecomers only have read only access.

I think you will need to conclude that OneDrive is not the place to put your backend - unless there is a setting for multiuser access rights somewhere - perhaps as a paid option.

Putting the backend on Azure seems to be the favourite option for simplicity based on threads in this and other forums. There is no setup charge/contract and usage costs seem quite resonable - see this link

http://azure.microsoft.com/en-us/pri.../sql-database/

[Benvolio1282]

That seems like the best option, I'll try that and let you know how it goes.

Thank you guys!

[June7]

I think what ranman alluded to was a process to synchronize user data entry/edit into a local frontend (table and form) with backend saved on OneDrive. Each user would have their own copy of frontend with table(s) for data entry/edit and then code would upload to the 'master' on OneDrive. That introduces a whole new can of worms associated with PK/FK relationships.

[ssanfu]

Found this on Wikipedia; It is referring to OneDrive, but.............

Integration with Microsoft Office

Users of recent versions of Microsoft Office (for Microsoft Windows or OS X) can use the desktop applications to simultaneously edit the same section of documents stored on OneDrive. Changes are synchronized when users save the document, and where conflicts occur, the saving user can choose which version to keep.[51] This allows for collaborative real-time editing with multiple users. Users can also use several different desktop and web programs to edit the same document.

Microsoft OneNote users can sync one or more of their notebooks using OneDrive. Once a notebook is selected for sharing, OneDrive copies the notebook from the user's computer to OneDrive, and that online copy then becomes the original for all future changes. The originating copy remains on the user's hard drive but is no longer updated by OneNote. Users can switch back to an offline-only version of the notebook by manually changing its location in OneNote, but unpredictable results may occur, including the OneNote application crashing and loss of notebook data under certain conditions. Under such circumstances, re-sharing the Notebook to OneDrive may result in recovery of the lost data.

(The bolding and colors is my doing.)

Now think about using Access with OneDrive.
I would definitely NOT have OneDrive be the "network drive" for my BE Access file. (at least for multi-user dBs)