Update query

[dcarr]

I am trying to create a query that updates a series of values in a table from one value to another. In the criteria field I have specified the value -1 and in the update to field I have specified "A". When I run the query however, the values do not get updated to the update value? I get an error message about lack of memory and the values do not get updated. I have inserted more RAM into the machine and this does not resolve the problem. I am attempting to update 100,000 rows???? Any ideas about how I could resolve this issue? Thanks

 

[namliam]

Have you tried rebooting? That somethimes help. I cant really help you on the how or why....

If all else fails you might try doing a loop with DAO or ADO to update your recordset....

Regards

The Mailman

 

[dcarr]

I have tried the reboot, shut-down, etc... but nothing seems to work!!! Thanks

 

[Brianwarnock]

Could Virtual Memory size be an issue here? have you tried a temporary increase in virtual memory? I presume the updates are held in memory until all are completed successfully and then written to disk.

Brian

 

[dcarr]

how do you increase the virtual memory??? on a pc

 

[Brianwarnock]

The detail hasto how to change the virtual memory depends on the system, click Start and help and search for virtual memory for details, do you have any more experienced IT guys/girls to help you?

Brian

 

[dcarr]

I have increased the size of the VMS to the maximum allowed but this does still not resolve the problem. I have also inserted an extra 512 mb of RAM so my machine now has 768 mb of RAM installed but I am still unable to update the records in the table??? Any other ideas? Thanks

 

[namliam]

Run some VBA to loop thru you recordset and change the values....

I can see why you wouldnt want to do this but its a solution to your emidiat problem... Can then resolve the memory problem later...

Regards

 

[dcarr]

I created a macro and pasted my SQL into the macro. The data has now been updated. Thanks for the advice.

 

[Brianwarnock]

well done

Glad you solved your problem

Brian

 

[namliam]

? can run the same SQL from a macro but not from a normal query?!



Strange... Another M$ Feature?

Regards

 

[dcarr]

Bizarre I know. I could not understand it as I used the same SQL as was in the initial query???? Hey, but at least it worked!!