CODEWITHSUNDEEP
sql-serverms-accessodbc
masgo
masgo

Reputation: 480

MS Access UPDATE query very slow

I have a very simple UPDATE query in Access which should update the underlying SQL Server. For whatever reason, Access does not pass the query to server but handles it by itself, i.e., it does an update query for each row. Since the table is huge, this runs forever.

This is the query as generated with the editor.

UPDATE dbo_myTable
SET dbo_myTable.myColumn = 'A'
WHERE dbo_myTable.myOtherColumn = 123;

If I run the same query as pure SQL it takes only seconds - as expected.

UPDATE dbo.myTable
SET dbo.myTable.myColumn = 'A'
WHERE dbo.myTable.myOtherColumn = 123;

The problem is not the 'A' value. If I change it from 'A' to Null the problem remains.

Background:

My actual update query is more complicated and involves joins and multiple conditions. While debugging the speed issues I could break it down to the above simple query which is already slow.

I used the SQL Server Profiler to confirm my guess that access does a query for each row instead of passing the whole query to the SQL Server.

Related:

I had a similar question a while ago: Force MS Access to send full query to SQL server . While the problem is the same - Access not passing the whole query - the solution has to be different because here there are really no special commands whatsoever here.

Upvotes: 2

Views: 1957

Answers (2)

Gustav
Gustav

Reputation: 55816

Perhaps you can fool Access to call a bulk update:

Sql = "UPDATE dbo.myTable SET dbo.myTable.myColumn = 'A' WHERE dbo.myTable.myOtherColumn = 123;"
CurrentDb.Execute Sql, dbQSPTBulk

The above dbqQSPTBulk is supposed to be used with dbQSQLPassthough but you don’t have to.

If you use above, then only one update command is sent.

Credit: Albert Kallal

Upvotes: 1

Erik A
Erik A

Reputation: 32642

The syntax for update queries in Access is significantly different from that of SQL server, especially regarding joins. They can't be handed off to SQL server.

One of the main differences is that in Access, an update query write locks all included tables by default and can write to all of them, while in SQL server, you have a separate FROM section, and the query only write locks and writes to a single table.

Instead, use a passthrough query to execute an update query on SQL server if performance is an issue.

Upvotes: 3

Related Questions