Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2011
    Posts
    33

    Unanswered: SQL record locking issue, with Access F/E

    Hello,

    I created a ms access database which my boss liked so much it grew from 20 users to 1000 users. Because of this we engaged IT to have the backend migrated to SQL.

    The database works fine, up until we start to maximize the load, then it pretty much becomes unusable. Users are presented with the following error almost everytime when trying to submit a record.

    Code:
    Error 3155 ODBC--insert on a linked table 'tblMain' failed. [Microsoft][ODBC SQL Server Driver]Query timeout expired (#0)
    IT are saying the coding in the frontend is too complex and don't won't a bar of it. However I don't believe it is the coding as it as simple as it gets
    Code:
    DoCmd.GoToRecord , , acNewRec
    After some googling I think the issue is with record locking. So can someone point me in the right direction on how to fix? I don't have access to the backend and IT won't help, so can I code something in the front end?

    Or where do you look in SQL to see how it is setup? Maybe if I know this I will be able to get somewhere with IT (sorry I've never used SQL).

    Many thanks for your time.

  2. #2
    Join Date
    Feb 2013
    Location
    perth australia
    Posts
    24
    ask IT to grant you 'view server state' permissions if they can't help you. you need to confirm if locking is the issue via dynamic mgmt view sys.dm_tran_locks (Transact-SQL) or using Activity Monitor within SQL Mgmt Studio Activity Monitor (Locks by Object Page)

  3. #3
    Join Date
    Sep 2011
    Posts
    33
    Hmmmm, no dice. lol

    But thanks for that link, by the looks of it I want the locks to be RID, so I have asked them to confirm it has been configured this way. Hopefully that's the issue and it's an easy fix.

    Thanks for the reply.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Microsoft Access is the "data dirt bike" in Microsoft's line of data handling tools. It is quick, nimble, and has more than enough power for one or two users. Access can be used other ways, and it can be a great "groupware" product for small (2 users) to medium (10 users) groups of users without a lot of effort or change from the single user version. Moving a Microsoft Access application to large (20 or more users) or enterprise (100 or more users) scale is difficult to impossible.

    I would re-write a Microsoft Access application that had 1000+ users using tools that were built for that kind of task. The dot net tools and SQL Server can scale up well from a dozen to many thousands of users.

    You are correct the locking and blocking is almost certainly your problem. You've probably found the code where the symptom occurs, but it may not be the actual source of the problem. I would guess that one or more settings in your front end codes need to be adjusted, and the section of code (function) that contains the line you identified may also need to be re-written to work well with multiple users.

    I'm not saying that you can't mitigate or fix this problem using Microsoft Access. I would not try to support a thousand users using a Microsoft Access application because I've tried to do that in the past and have always gotten to the point where I needed to upgrade to different tools to support that scale of users.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Sep 2011
    Posts
    33
    Thanks Pat. Yes, when researching this issue I've found that MS access not being suitable has be mentioned quite a bit. My department is in the process of acquiring SharePoint 2013, the plan is to convert to that once in place - my understanding is SharePoint will be a much better solution.

    But knowing how our IT area works, I don't expect it anytime soon. That's why I need a fix for my locking issue. When you say the code needs to be re-written to work with multiple users, what can I do there? Can you point me in the right direction. I've tried to search but probably putting in the wrong questions.

    Many thanks.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is really a question for the Microsoft Access gurus. I use Microsoft Access, but I've never successfully scaled an application beyond 85 users.

    I'm moving this thread to the Microsoft Access forum. It may be a couple of hours, but you'll definitely get better answers there than the ones that I can give you.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Sep 2011
    Posts
    33
    Thanks Pat, appreciate it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •