Excel vs Access

[shaggi]

Hello people,
I have a question, which is not strictly a VBA question, but I figure one of you will be able to answer. I have a very large excel file, which deals with deliveries to my employers and exceeds 12000 rows (by about 15 columns). As I have to interrogate this information to review supplier performance, progress can sometimes be slow.
The problem is that I believe Access may be better at handling larger volumes of data, but we have a senior manager who believes Access slows down our server (we work on NT Build 4 at the moment), is there any truth in this, as I don't believe there is, also, do I have the correct forum, and if possible, is there a way of compressing my excel spreadsheet without zipping it...(I know there is more than one question involved here, but you guys know your stuff!!!!)

 

[calculus]

I'm not a great resource on Access slowing down the server, but I can't imagine that it would. Access is pretty fast, and you're using it in a relatively small way.

For what you're trying to do I would suggest Access. You're just trying to use Excel as a database, which works, but your data is getting to the unmanagable size.

calculus

 

[shaggi]

Many thanks Calculas,
you have confirmed what I thought, but any ideas on "shrinking" Excel a little in case I can't get my point across... I have tended to remove some of the formula I have (and pasting values) which seems to do the trick, but each file has a kind of summary sheet which draws a lot from the calculation tree cos it looks through everything from the full list of records, should I be doing it with VB instead of formulas

cheers
Adrian

 

[mpastore]

I can't see access slowing down your server any more than Excel would. Both are essentially just files that are accessd from workstations over the network. Advantages of MS Access:

- multiple people can edit file at the same time
- no 65000 row limit
- much better input and query capability

Primary disadvantage is that you may have a learning curve to surmount in designing good table structures (most Excel people create 1st normal form tables that mimic spredsheet layouts). This will cause you to create overly complex reports until you become more proficient in relational table design. If none of what I said makes sense, you probably fall into this category. There are plenty of good primers on database design out there. Start with the northwind database that come with MS Access.

 

[xlbo]

Use Access - the only reason it would possible slow the server any more than excel is if you had a publec database that is continually accessed and updated by multiple users.
If the mgr still won't give in there are other options - like holding the data on one file and querying it with another, using pivot tables / Data>Get External Data or VBA. On the subject of appropriate forums, may I just point you in the direction of the (at least) 3 Access forums located on this site:
Access Other Topics
Access Forms
Access Modules (VBA coding)
There's some peeps ther ethat REALLY know their access

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[shaggi]

Many thanks (once again) XLBO (and you other guys)thanks for pointing me in the right direction

 

[SteveBausch]

Are you currently serving the Excel?

Meet the guy halfway, scrounge up some older system and dedicate it to Access.

Actually, the skeptic should allow a short-term test to see if anyone can truly perceive the difference.