Access Query Not Responding

[sunharepal]

Hi
I have a msaccess table acctbl and linked table from Oracle oratbl. I am running an update statement on acctbl and access is not responding and finally I have to cancel it out



Records in tables are :
oratbl - 6469806
acctbl - 3542

Update Statement
UPDATE acctbl INNER JOIN oratbl ON acctbl.BookID = oratbl.Book_ID SET acctbl.Book_Price = oratbl.Book_Price;

Can some one let me know how can we correct it.

Thanks,

Sun

[NTC]

6M records may be the issue when in Access03 - though theoretically it shouldn't.

One should try the join in just a regular Select query, not an update query - - and see if that works ok. Depending on what you find may suggest the best way to implement an update.

Hope this helps.

[sunharepal]

Select query is also taking to NOT RESPONDING status. Is there any alternative way we can achieve UPDATE

[NTC]

well one would guess that xp with 512k ram trying to join a 6m table just doesn't have the power....

that's not to say that it couldn't be the network, or that one simply isn't waiting long enough (some queries in the mainframe world take hours to run...)

one could continue to experiment by importing that table - rather than linking to it....plus attempting to trim its size by filtering out obviously not needed records in making a new record set.

[sunharepal]

Yes I have done small tweaking in the query to convert it to LEFT JOIN for update statement

Also, Used subquery to restrict the size of 6 M records to match the records of the table that needs to be updated in the local access table.

This let me run the update statement fine.

Thanks,