Record Locking Time out errors in MS SQL / Access front-end

Hi Experts!! SQL Server 2008 R2 - Access 2003 Front-end (I know, I know) ... I just picked up a client... many, many, users... an application in production and in need of some major reconstruction and upgrading --- I am looking for advice.

They are getting locked out daily -- Record Locking  and Time out errors -- as I'm very new to their situation I wanted to get some guidance on best practices. I checked... all the SQL tables have a timestamp field and all bit fields are set to not allow nulls with a default value of 0. Huge number of records... too many indexes... Want to work as efficiently as possible to resolve this issue before I tackle some of the other issues -- and before I tell them they need to upgrade to a newer version. of Office/Access.

I wrote some SQL queries (to utilize the sp_lock) but since most users are gone, all have GRANT access -- will run that when they get hit tomorrow...

As always -- thanks in advance for any direction.

Best, Eileen
Eileen MurphyIndependent Application DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John Gates, CISSPSecurity ProfessionalCommented:
Make the access file that is front ending should be compiled as an mde this will help with operations in multi user environment minimizing record locks.

-D-
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Sorry, but compiling as a MDE will do anything for record operations unless the queries make use of UDF's.  A MDE is nothing more than a MDB with the source code stripped out.  It will be always in a compiled state therefore, but you can achieve the same thing with a MDB; just make sure it 's compiled.

With compiled VBA code, the code will run faster, but that's not going to do anything for back end database operations.

As far as immediate solutions, look at the indexes first.  Too many indexes can be just as bad as too few, and your probably right to focus in on that as the source of the problem.   Index operations can cause a lot of concurrency issues.

Look at the major tables in the app first, then branch out from there.   Look for the longest running operations as well and focus on them first.

Also a spot check of their SQL Server would be a good idea; make sure it has adequate CPU and memory.   Server memory should be no more than 80% utilized under full load.

Long term: look at pushing as much of the work server side as possible.  Use of views, pass-through queries, and triggers will vastly improve the situation.

Also look out for queries that are pulling more data than they should.   So many apps have "SELECT * FROM..." which can kill you.

Also look for any Domain functions (i.e. Dlookup()) inside queries.  Those as well as using UDF's will guarantee poor performance (they are not optimizeable by the query parser and will never get handed off to SQL).

Last, nothing wrong with A2003 and SQL Server.  With proper development you can get more than adequate performance.  Upgrading them to a latter version of Access is not really going to help in that regard.

Jim.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John Gates, CISSPSecurity ProfessionalCommented:
I used to use access as a front end and got fed up and started programming my own front ends with .net will never go back to access again... Access in a multiuser environment is death..
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<< Access in a multiuser environment is death..>>

 Depends.....Access is tool like any other.  Use it as intended with good development practices and it works.  Use it in ways it was never designed to be used  (like using JET for the data store over a WAN) and you'll have no ends of problems.

 And Access FE with a SQL Server back end is very multi-user friendly.  Even with JET, 20-30 users in a read /write situation is quite achievable.

Jim.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Better go for a top down analysis.
What's the specification of the server? And the OS and SQL Server versions?
Can you identify the long running queries?
all the SQL tables have a timestamp field and all bit fields are set to not allow nulls with a default value of 0. Huge number of records... too many indexes...
Hope that there aren't indexes on bit fields.
Eileen MurphyIndependent Application DeveloperAuthor Commented:
Thanks Jim!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.