Update Query Referencing Linked Table - Way Too Slow

[vmed]

Hello all, I'm after some advice on a query that updates a locally stored table with information from a linked SQL table.

I import some sales information into a table within access. I also have a employee table stored in SQL linked in this access database. In my imported sales data I link sales_id against the SQL reference table to obtain employee ID and update this local table - this is where it is causing issues.

I used to store the reference table locally and experienced no issues, after moving to SQL it's taking several minutes to run when running an update query. There are ~150k sales records stored locally and ~500 in the reference table.

If I change it to a make table query using the same link/criteria/fields it runs straight away.

I have checked indexes etc and can only assume that the difference is in how the two queries are executed - but I'm not sure how as both are joining on the same field in the same way.

Any advice would be appreciated.

Update

UPDATE sales 
INNER JOIN employees ON sales.sales_id = employees.sales_id 
SET sales.employee_id = [employees]![EmployeeID] 
WHERE (((sales.sales_date) Between [employees]![effectiveuntil] And [employees]![effectivefrom]));

Make Table

SELECT sales.sales_date, employees.EmployeeID, sales.sales_id, sales.customer_number 
INTO temp_test
FROM sales 
INNER JOIN employees ON sales.sales_id = employees.sales_id
WHERE (((sales.sales_date) Between [employees]![effectiveuntil] And [employees]![effectivefrom]));

 

[AccessMSSQL]

So Sales is your local table and employee is your SQL Table? How are you executing these queries? In the query window or are you using VBA and an ADODB connection?

 

[vmed]

Many thanks for the reply - indeed sales is the local table and the only one updated in the query.

The query is a simple saved query run in the query window - although it is called via vba when working fine (through docmd.openquery...). Both the update query (slow) and make table (quick) were created using the query designer and saved down.