I have an MS Access 2010 front-end and SQL Server 2008 back-end. Users all read/write to a single table (tblProductionOrders). We sometimes encounter problems with deadlocks occurring on this table. My admin tells me that indices have been optimized and I have done my best to reduce and streamline INSERT and UPDATE table trigger activity. The problem is that INSERTS and UPDATES issued from Access take a long, long time and sometimes fail due to timeouts. The tblProductionOrders table contains 2700 records and the bulk INSERTS/UPDATES generally involve no more than five or so records. I don't think I'm doing anything exotic ... just adding or changing a few records.
What is the best transport mechanism to drive these INSERT and UPDATE transactions??
Originally, I was using DoCmd.RunSQL to issue the INSERT and UPDATE statements from Access, which as I have said takes a long time (~45 seconds). I have experimented with using a stored procedure on the back-end to INSERT and UPDATE records. I have looked at doing this in two ways: With a DAO query def, which also take a long time and now with an ADO cnn.Execute command, which times out.
What to do??? Please help me!!