Accessing Task Manager Information through VBA 1

[MikeTruscott]

Hi everyone,

Any help on this would be greatly appreciated.

Basically I've written an Excel 'tool' which takes some spreadsheets, exports them to access, lets access to it's thing, retrieves the results from access and produces to pretty little charts!

One day it works and the next it doesn't. Sometimes when it crashes I close all of the windows only to find that ACCESS.EXE is still using 97% of the memory! Sometimes excel won't re-open and sometimes I have to shut down my machine and restart it!

Basically I'm interested in keeping a log of memory useage in the task manager and I was wondering if there was a way of accessing it whilst the code is running to grab the information I need?

Can you access the task manager in the same way that you would open access or powerpoint from the code?

In case it's useful.....At the start there can be as many as 12 spreadsheets imported to access, with each one containing around 20000 lines. The access part of the tool uses around 30 tables and maybe 100 queries. At the end the excel spreadsheet which holds the code will have around 25 charts in it and a further 20 sheets with data on it.

Again.....any help really appreciated.

Cheers

Mike

 

[sugarflux]

Instead of error trapping to deal with the code, i would suggest looking at the reason the errors are occurring

It is probably due to the sheer volume of data you are dealing with but the way in which it is running. I, myself, deal with around 27 million records per day so i know that it is possible to achieve such things.

I would recommend first that you put your code in access and then port the information you need to excel instead of vice versa.

Instead of using static queries, try using SQL which will vastly speed up your process and produce less space errors. Using SQL, you can also give yourself a progress bar to visibly show what stage of the process the code is falling over.

Earlier versions of access (Pre-2000 i think) when access reaches approx 1 gig, it tends to fall over. Could this be one of your problems? If so, measure the size of your database at intervals in the code and if you are reaching a high amount of space, have the code open another DB, compact your original DB, then re-open the original DB and carry on where you left off...

Also, Excel becomes very slow and clunky when reaching the sizes you are talking about. Is there any way that you could store the sheets in separate work books (i.e. you will have a much friendlier application if you can access 20 workbooks with one sheet in each, rather than 20 sheets in one workbook).

Hoep this helps,
sugarflux

 

[MikeTruscott]

Hi Sugarflux...

Thanks for replying to my post.

First, a little bit more background to my problem. The 'tool' has ran with much more data in it than I'm currently trying to use. The only difference is that we've recently had our machines upgraded to XP, and this is the first time that I have had to put this much data into it since the upgrade.

So the tool has worked perfectly well in the past.

What's happening at the moment is that when I try to import 8 excel files to access (one at a time), it works. When I try to import 9 it fails (on the last file) on the line..

docmd.transferspreadsheet.......

Right, now to your reply.

At present I'm not good enough on Access to be able to code the curreent project in it. I've got loads of forms in Excel as well so I don't know how I would deal with them in Access.

Have no idea how SQL works although I've been meaning to find out.

I've thought about compacting the database but whenever I try to do it it doesn't let me. I've been trying to do it in an open database.....Are you saying that the database needs to be closed, compacted from ANOTHER database, and then reopened again? I really think this could help.

20 workbooks with one sheet would be friendlier but is not a road I want to go down if I can help it.

Thanks again for your help. I will have a think about what you have said. Hopefully the compacting will help because the other solutions are bigger jobs and I need to sort it out asap!

Cheers

Mike

 

[MikeTruscott]

Hurrah!

Have just added some code which opens another (dummy)database and then compacts the main one.

The compacted database then has a different name so I had to do a bit more work to then overwrite the original file, but now it works!

So basically my problem was that the Access file was getting to big for the memory to handle any copy and paste functions associated with it.

It's therefore possible that I might stumble across this problem again when I have to add even more data to it, but hopefully it will scrape through.

Thanks Sugarflux!