Is Access the right choice?

[ShaunC]

Hi All

I am in a situation I had not given any consideration to.

I have developed a spreadsheet which tracks stocks on the Australian Stock Exchange through Yahoo Finance. I ran the VBA code in full for the first time yesterday. It had been running for somewhere in the order of 10hrs, querying Yahoo for data and setting up the worksheets, when I stopped the code and saved the file. At this point the spreadsheet has 874 (of about 2300) worksheets and the file is about 330mb.

I continued the code and at midnight last night stopped the code and tried to save, at this stage there were 990 worksheets. I tried to save and the error message I received was something in the order of "there are not enough resources to save the file, close so applications and retry".

The issue being that Excel was the only application running as everything was turned off (including programs running in background). This would suggest that my PC is the limitation here, however looking at the windows system analysis items, when I tried to save, the CPU went 100%, ram still had about 9gb free and everything else was seemed fine.

System is an Intel i960, 12 gb ram, primary drive 1 tb with just under 900gb free, 2 nVidia 9500GT cards in sli (not sure if relevant).

So, either my PC cannot handle the amount of data coming in or Excel cannot. Either way, I need to try something. It would seem logical to store the data in a database and give excel access to the database to chart the data.

My query is, am I likely to run into the same problem if I use Access to do what I currently have Excel doing? I took me and another guy about fourth months to complete the spreadsheet on and off.

Cheers

Shaun

[Norie]

Shaun

I suppose it really depends on how much data there actually is, how it's structured, how you are going to process it etc

Access is comfortable with, of the top of my head, 2GB of data

I've worked with over 2 million records, just using it as a data store, pulling what's needed into Excel to actually do the 'real' work.

No forms, reports.

One thing that would probably have me running to Access is having to deal with that many worksheets if Excel.

Mind you I don't what data you are working with.:)

PS Are you sure it's an i960 processor?

[ShaunC]

I suppose it really depends on how much data there actually is, how it's structured, how you are going to process it etc
The data...25 -30 columns wide and upto 3000 rows in 2325 odd worksheets.


I've worked with over 2 million records, just using it as a data store, pulling what's needed into Excel to actually do the 'real' work.
This is what I am thinking I need to move to. Get Excell to pull the data out of Access then run the calcs and do the work.


One thing that would probably have me running to Access is having to deal with that many worksheets if Excel.
I agree...now. I have never really used Access, so Access never came to mind


Mind you I don't what data you are working with.:)
It is just ASX share data (imported from Yahoo finance) with calculations, nothing too exciting.


PS Are you sure it's an i960 processor?
My bad, Intel Core i7 960. Yes, I'm sure.

Would it be a difficult transition to "port" the current vba code in to access. I assume where I have referenced to worksheets, I would need to reference a table. Currently the updating of data is automated, does access have this ability? I am sure it would but I just don't know.

Cheers

Shaun