Re: Update access table to sql table
Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance
On Jun 13, 10:09 pm, Mark <M...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
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.
.
Relevant Pages
- Update access table to sql table
... Probably been answered before however i cant get a specific guide through ... 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 ... in sql but i just dont understand why the query cant do it alot quicker.i ...
(microsoft.public.access.queries) - Re: Please explain this update query?!?
... that update everytime a payment is made against an invoice. ... >> This update query works fine as it updates the values that I want it to, ... >> however, I seem to having a problem with multiple transactions, ie. ... > The way to get summations is thru SELECT queries that use the SQL ...
(microsoft.public.access.queries) - Re: simple query with distinct keyword
... it for amost and hour and a half and I cant make it work. ... I am a bit of a newb to SQL, so pardon the 'basicness' of my ... Sux being a total newb. ... I don't care which of the 'dup' records ...
(comp.databases.ms-access) - Re: Please explain this update query?!?
... I have a prob with an update query and I hope someone can explain it to me. ... Apparently when I run the update query, it updates the invoice with the first Receipt transaction, but it seems to ignore the second transaction? ... Therefore when I do a reconciliation, I'm shown that Invoice101 still has $100 outstanding, when it fact it has already been paid. ... The way to get summations is thru SELECT queries that use the SQL ...
(microsoft.public.access.queries) - Re: simple query with distinct keyword
... I am a bit of a newb to SQL, so pardon the 'basicness' of my question ... but I still cant seem to get what Im after. ... being a total newb. ... I don't care which of the 'dup' records gets ...
(comp.databases.ms-access) |
|
