Update access table to sql table

[Mark]

Hi all
Probably been answered before however i cant get a specific guide through
searches anywhere.

I Have an access table X it has 4million entries. Ive uploaded X to SQL as a
backend and linked it to my access db.
I create a temp table(Y) (only 2 columns) using data from X, do some calcs,
I then wish to update a new field in X lets call it 'Field1'. So when i
create an update query in access and innerjoin X to Y use its 2 primary keys
and try to update Field1 from temp table Y it takes about 30mins then bombs
out.
Anyway way to make this work and quicker?
I read that access loads the stuff into ram then trys to update the X table
in sql but i just dont understand why the query cant do it alot quicker.i
tried passthrough queries but that kept asking where is Y table.
if i append to sql table X it works fine but updating is the problem
any idea?

Mark

 

[Piet Linden]

Hi all
Probably been answered before however i cant get a specific guide through
searches anywhere.

I Have an access table X it has 4million entries. Ive uploaded X to SQL as a
backend and linked it to my access db.
I create a temp table(Y) (only 2 columns) using data from X, do some calcs,
I then wish to update a new field in X lets call it 'Field1'. So when i
create an update query in access and innerjoin X to Y use its 2 primary keys
and try to update Field1 from temp table Y it takes about 30mins then bombs
out.
Anyway way to make this work and quicker?
I read that access loads the stuff into ram then trys to update the X table
in sql but i just dont understand why the query cant do it alot quicker.i
tried passthrough queries but that kept asking where is Y table.
 if i append to sql table X it works fine but updating is the problem
any idea?

Mark

make sure you have indices on both your Access and SQL Server tables
on the join columns and the filter columns. Then you should get
reasonably fast performance.