2

There is a table with trigger in our SQL database. (sql server 2014). When updating record from Microsoft SQL Server Management Studio update takes like 1/10 sec. When the record is changed in MS ACCESS (in the form) it takes like 5-6 seconds to update. There is a trigger on that table, but the same operation directly from MSS Management Studio is fast, so it is not a problem with a trigger itself. THe trigger has to insert 1 record to 15 milion records table. I am looking for reasons why the same operation from ACCESS and SQL management studio can take 40 times longer. Any suggestions? Or links to known issues in that topic?

EDIT: It's ACCESS 2003. It's subform bound to form by ID field. I am edditing simple integer column of record. I use normal connection with my sql server, it's adp project and we just use typical connection for it. I tried to do update from VBA (the same project) but from simple module -> the same effect. So it does not metter if it is eddited from form or update command is send from other module to that table. It still takes long.

Whencesoever
  • 2,218
  • 15
  • 26
  • Why go through MS Access to update a SQL Server table? No doubt, the links from Access to SQL Server are slowing everything down. – Gordon Linoff Mar 02 '16 at 15:16
  • Adding a `timestamp` column may help. See http://stackoverflow.com/questions/13993301/editing-record-issues-in-access-sql-write-conflict/13993410#13993410 and http://stackoverflow.com/questions/2858318/will-existing-dao-code-work-against-a-sql-server/2861581#2861581 – Andre Mar 02 '16 at 15:45
  • Please [edit] your question with more details. Are you updating an ODBC linked table? How are you applying the update: via a bound form? ... an UPDATE query? Is the table into which the trigger inserts the new row also connected to Access as an ODBC linked table? Is that table open in a form or Recordset when the update is applied to the other table? Also, have you considered using SQL Profiler to see what Access is sending to the SQL Server when the update takes place? – Gord Thompson Mar 02 '16 at 16:18
  • i just eddited my question with more details – Whencesoever Mar 03 '16 at 07:20

1 Answers1

0

To narrow things down I would still suggest to disable the trigger and see if the issue persists.

f10
  • 21
  • 3