7 Replies - 13869 Views - Last Post: 18 February 2014 - 06:20 AM

#1 depricated  Icon User is offline

  • DLN-000

Reputation: 717
  • View blog
  • Posts: 2,434
  • Joined: 13-September 08

Handling Concurrent Users on a single Record

Posted 18 March 2013 - 09:36 AM

Right now we're working on figuring out a good method for handling concurrent users. Our application has previously been used only by our team, but we're expanding to include another department within the app.

Since it has been only my time, only one person would be accessing any given record at a time. Very rarely were two people at once. Now, we're expecting 2-3 people to regularly be accessing specific records. I'd like to find a way to manage data between concurrent users.

Right now what I'm looking at doing is setting a 'delta flag' in a table, which would be periodically checked - and when the flag is true it would cause the client to requery the data from the db, then copy it to a local table to check against and confirm that that change has been accounted for. This seems REALLY cumbersome and all around like a bad way to handle it, like it will require far more reads/writes than is reasonable.

I've searched around on google for standard ways to handle this but I'm not really finding anything except for the built in conflict resolution. I'd like users A B and C to be able ot see changes as user D puts them in, essentially.

Can anyone point me in a good direction for this?

Is This A Good Question/Topic? 0
  • +

Replies To: Handling Concurrent Users on a single Record

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9206
  • View blog
  • Posts: 34,589
  • Joined: 12-June 08

Re: Handling Concurrent Users on a single Record

Posted 18 March 2013 - 09:58 AM

Sounds like you need to migrate this to MSSQL. You can run 'MSSQL express' for free, but the fact of the matter is MS Access doesn't play well with multiple concurrent users. Time to take this out of the pet project bin and fully implement a database for it.
Was This Post Helpful? 1
  • +
  • -

#3 depricated  Icon User is offline

  • DLN-000

Reputation: 717
  • View blog
  • Posts: 2,434
  • Joined: 13-September 08

Re: Handling Concurrent Users on a single Record

Posted 18 March 2013 - 10:07 AM

Ya, that's what I've been working on on the side. Unfortunately, we're running this in production for our team to manage data.

I've convinced my boss to migrate to MSSQL and .NET, but right now as we take on more responsibility as a team he insists we continue building in the existing system (VBA/Access). Actual implementation in .NET probably won't get started til later this year.
Was This Post Helpful? 0
  • +
  • -

#4 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5832
  • View blog
  • Posts: 12,684
  • Joined: 16-October 07

Re: Handling Concurrent Users on a single Record

Posted 18 March 2013 - 10:09 AM

MS Access is a toy. It offers single user access. There is NO concurrency in MS Access. One user, one lock file, that's it. It was never meant to support multiple users. As a development environment ( forms, etc ) it's tragic.

Your best bet is to get a real database. SQL Server would be ideal. However, SQL Server Express, with it's hard cap of 10GB size, is probably enough for anything coming from MS Access. Really, you can use any database, as long as you can get an OleDB or ODBC connect to it.

Take all the tables in MS Access and migrate them to your actual multiuser database. Then drop all those tables and create database links to your database. That's it. You now have an MS Access database that more than one person can actually use at a time.

Hope this helps.

( Sorry if this sounds harsh. Friends don't let friends develop in MS Access. )

This post has been edited by baavgai: 18 March 2013 - 10:09 AM

Was This Post Helpful? 2
  • +
  • -

#5 depricated  Icon User is offline

  • DLN-000

Reputation: 717
  • View blog
  • Posts: 2,434
  • Joined: 13-September 08

Re: Handling Concurrent Users on a single Record

Posted 18 March 2013 - 10:14 AM

Ya :(/> That's what I was afraid of. I appreciate the feedback, I didn't think it was particularly harsh. I hang my head sometimes when I have to admit that I'm primarily writing in Access.

Right now I'm digging for a workaround. I'll see what I can manage. I guess it's just a kludge at this point til we get moving with a real db.

This post has been edited by depricated: 18 March 2013 - 10:14 AM

Was This Post Helpful? 0
  • +
  • -

#6 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5832
  • View blog
  • Posts: 12,684
  • Joined: 16-October 07

Re: Handling Concurrent Users on a single Record

Posted 18 March 2013 - 11:49 AM

I have users that love Access. I'd have to rip the thing from their cold dead hands. It is a tool that enables regular users to, essentially, develop applications. Like all such tools, it has limitations that you spend most of your time working around. I have all users trained; if it's Access, it's not my problem. ;) However, if a user must share data, then a real RDBMS can be arranged.

MS Access comes in two pieces: the UI and the actual data store, the tables. You can't easily escape the UI if you've invested in it, but you can escape the tables. A SQL Express install will work on a single machine or can be shared across a network. You can do that today, right now.

Good luck.
Was This Post Helpful? 1
  • +
  • -

#7 depricated  Icon User is offline

  • DLN-000

Reputation: 717
  • View blog
  • Posts: 2,434
  • Joined: 13-September 08

Re: Handling Concurrent Users on a single Record

Posted 18 March 2013 - 12:49 PM

View Postbaavgai, on 18 March 2013 - 12:49 PM, said:

You can do that today, right now.

Good luck.

Wish it was that easy. Corporate environment means heaps of red tape. It took us 2 months to get a simple WSDL set up on an already existing web service. "Within the year" is fast-paced. Q_Q

On the bright side I've got the FE somewhat built in vb.net and set up so that when we switch to SQL it's just one class to rewrite.

What we've decided to do is not tie the form to the table - instead it'll lock sections of the form based on user roles and allow the priveleged user (who has dibs so to speak) to update the table, while others will refresh at regular intervals to display the information as it's updated. It feels messy to me, but I've never ever said "this can't be done" and I'm not about to.

But this does help push my case to hurry the switch to MSSQL (we have a corporate license already for it).
Was This Post Helpful? 0
  • +
  • -

#8 rohanxtreme  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 18-February 14

Re: Handling Concurrent Users on a single Record

Posted 18 February 2014 - 06:20 AM

Hi,
Thanks for starting the thread.
I know this thread has been closed for a while, But I would like to get your views on a small issue.
I'm fairly new to the world of access having worked years in MS-SQL so please excuse me if I miss something obvious.

I have inherited a access 2013 database which I believe is migrated from 2003 and this DB has been split.
The users of this DB are complaining that concurrent updates to same records to the DB is corrupting the backend database. They also claim that such updates did throw error in 2003 but they never corrupted the database.

As far as I understand irrespective of version Access backend db will update the data in tables when any front end db sends the data, the data might get updated incorrectly or even a error will be thrown. But how can such minor data update corrupt the backend db.

Any thought/views or links will be highly appreciated.

Thanks in advance..

This post has been edited by andrewsw: 18 February 2014 - 06:58 AM
Reason for edit:: Removed previous quote

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1