Access not responding

[Avipatel]

Hi

Does anyone know how to overcome MS Access not responding error. I am trying to change a Data Type on Access 2003 but the data is large and stops responding. There are about 550 000 records in one of my tables and when i try to change the data type after importing the data, it crashes.

I am using a pentium 4 with 1GB Memory

Thanks

 

[The_Doc_Man]

It is possible that you need to increase the size of your swap file to a very large number. Access tries to expand into a fairly large virtual memory space when doing a dataype change on large tables. The size of virtual memory is governed by the size of your swap file. If the swap file gets full, you should eventually get an "out of memory" error. Another thing to consider is that if you are actually swapping or paging to any degree of significance, there's an old saying: "When Windows swaps, the world stops." This is because the internal process that does swapping is higher in priority than any user process such as Access.

Also, if you import your data, it MIGHT be better if you did the conversion differently. For instance, normally you import to a new table and then "massage" it. But in your case, it might be better to define the new, empty table and import "on the fly" by appending the imported data to the existing table. This might not work right if the data source requires extensive filtration, but if the import is fairly clean, that data change MIGHT make it in a single operation. No promises, though.

 

[Avipatel]

Access Not Responding

Thanks

I have increased the SWAP File but still no luck with the freezing. I also tried a new table but that did not solve the issue. Thanks for that advice though.

Avi

 

[The_Doc_Man]

One last thought. If you can do it, bring up your system task monitor in a small window and bring your database up in a way that doesn't hide the monitoring display. Start your work in Access and see if, at the point of lockup, some task has gotten really big or has started to consume immense amounts of CPU time.

Try this again with the performance monitor instead of the task list. See which resources suddenly get depleted. Come back with the results. One of us might catch a hint from your findings.

 

[Avipatel]

Thanks

I looked into this but i dont understand why the CPU usage goes down to 0% and MS Access is not Responding. This happens after about 1 min. I also tried increasing the Virtual memory size but that did not help. I am running out of ideas.