Is it time to move to access?

[Dave B.]

I think we have pushed Excel too far. There is an ongoing issue with excel
being rather slow, but it is only slow for certain people on certain files.
The IT dept and Planning have been head butting about this for some time, and
I am wanting some 3rd party perspective. Our head of planning has been
writing increasingly large excel sheets. Several in the 80-120mb area, with
multiple worksheets numbering in the 50-60 thoughsand + area, with an average
of 10-14 fomulae per worksheet, and each formula is extended the entire
heighth of the worksheet and the formulae themselfs are quite sizeable. This
is combined with many large pivot tables and complicated lookups and offset
lookups going to other files of comparable size and complexity. My question
is this, should this be migrated over to access? I figure it should be since
Access is designed for this kind of data base volume, and as far as I can
tell Excel seems to have reached and passed what it can process effeciently.
He frequelty have upwards of a 1/2 gig open in excel files at once, from 4-6
files, and when he is on and working, everyone else on that terminal (Citrix)
server is feelig the strain (and said server is new and top of the line) and
everyone in that yard sharing the T1 is also feeling the effects.
What do you guys think?

 

[dlw]

Did you say 50 to 60 THOUSAND sheets? And your head of PLANNING is doing all
that? You should change his title to Head of Spreadsheet Empire. But with
something like that, the word Empire seems inadequate.

 

[JLatham]

If the numbers you gave are even close to right, it sounds like the time has
probably passed to move to something more robust. But to say that that
something else is Access would actually require an analysis of the data on
the sheets themselves. If many (most) of the sheets could be grouped into
similar categories with similar information/formulas/processes within those
groups of sheets, then I'd say "yes, it's time to move this to a relational
database that's more appropriate for not only the volume but also more
appropriate to handling repeating data groups".

You start talking the numbers you did with regards to Excel in both terms of
# of worksheets and size of the files, then I start to cringe.

 

[Dave B.]

Im sorry, I got a little twisted when I wrote that. 50-60 thousand ROWs, and
an average of 50-70 columns, and probably 15ish (average, but as high as 45
on some) worksheets per file. Usually 5-10 files like this opened at once,
all tied together via pivot tables and various types of LARGE lookups (and by
large, I mean bare minimum of 3 lines of text, usually more like 10-12).
Also he has various marcos that are tying usually 10-12 files together as
well, and the print out of his average macros, printed for trouble shooting
purposes, was 17 or 18 pages, some other are ALOT bigger. I personally have
writen some fairly heavy excel stuff, but what he has makes me shudder. He
is realistically sitting on upwards of 35gb of raw excel data spread across
75-80 files.

 

[Dave B.]

Im sorry, I got a little twisted up, 50-60 thousand Rows, an average of 40-60
columns and probably an average of 15ish worksheet per file. It is still
ALOT of data. There are multiple lookups and pivot tables that tie 10-12 of
these files (many of which this size) together, he realistically has 30-35gb
of data in Excel across 90-95 files, it's simply horrendous. Hats off to
him, he has done some brilliant work for us in Excel, but he has pushed Excel
WAY too hard. I have written some pretty heavy duty excel lookups and
formulas, but his make me shudder. Frequently his "basic" lookups and
formulas are 3-5 lines in the formula bar, his heavy ones are WAY bigger.
There are some Macros written to "simplify" thi, when one of them (not the
biggest one by far) was printed for trouble shooting, it was 17 or 18 PAGES
of macro script, for about 10 of these files, that were all the horrid size I
was mentioning. Several files over 100meg in straight Excel.

Is it a safe assumption that is you are at the 100mg area of an excel file,
you should probably be moving forward to something else just from that alone.
Sorry for the confusion, please, a second opinion if you would?

 

[Dave B.]

Please only read the second reply,sorry to double post...... it showed it
didnt post the first reply I made.

 

[JLatham]

double post - no trouble, sometimes the system does that to us all.

As for the 100MB limit you've 'set' - again it depends on where that 100MB
is coming from. If these files contain unrelated data on lots of sheets,
then it may not be realistic. But he may want to move those files to his
local hard drive with regular backups made to the server/share. He'll get
better performance, especially with his linked data, and the network load
will be greatly reduced. That in turn lets other people get on with their
own lives better and be more productive to the company as a whole.

My gut feeling of the whole thing is that he probably would be better served
using a database of some type. Access might handle the load since he's
probably the only user, although something a bit more robust as perhaps SQL
Server if you have it around, or the free SQL Server Express Edition (
http://www.microsoft.com/sql/editions/express/default.mspx ) with Access as
the front end for it (for easy interface development).