Access to SQL Server locking problem

echovue · December 21st, 2006, 03:24 PM

I have an MS Access database which uses SQL Server 2005 as a backend.

I am doing most of my querying with either an ADODB connection, or using the DoCmd.RunSQL

What is happening though is that these queries are timing out - does anyone have any experience with locking problems using either of these, or have any suggestions about how I might resolve this problem?

Thanks

Mike

Mike
EchoVue.com

echovue · December 21st, 2006, 06:23 PM

OK - got it fixed, but here is the kicker...

I put Primary Keys on all the Primary Key fields at the end of last week. The idea was to improve performance!! With 20 users out of work, and sheer desperation, I removed all the keys - It's now faster and actually working.

Mike
EchoVue.com

mmcdonal · December 22nd, 2006, 08:10 AM

Sometimes I don't use PK's or any referential integrity in SQL and do my business rules with the Access interface, at least to prevent SQL Server from throwing weird errors at the users.

Also, I usually create views on the server and then call data from them. If I need to do an update, I use ADO and make them directly back to the effected tables with code.

mmcdonal