Results 1 to 13 of 13
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Excel vs Access (2000+)

    Hi all,

    I will be teaching a class on using some of Excel's data-base/data-analysis like features: sorting, filtering, pivot tables, etc. I would like to say a few words at the beginning about how Excel compares to Access (or other DB program) in this regard. This is still a beginner's type class but exploring these type of features.

    Another way to look at the question is what types of DBs would it really not matter which you used and where would it make a big difference in terms of flexibility, usability, etc. that you picked one over the other.

    So far, I have the following thoughts about the comparison (specifically vs Access since I haven't used other DB pgms in years). Not sure about accuracy of all of them. Would appreciate feedback on below plus other items to add. Of course, there's the issue of which app one has or which one is more comfortable using. But let's leave those issues out - assume one has both and is equally comfortable using either (or has neither and knows neither and has to buy/learn one).

    1. Excel is limited, at least on 1 worksheet, as to how many records can be stored. Similarly for the number of fields. I'm sure DB programs have limits but they're bigger.

    2. I think it would be difficult (impossible?) to create a truly relational DB in Excel.

    3. I've seen Jan Karel's Query Manager but haven't had a chance to try it. I assume it provides some capabilities similar to creating queries in Access.

    4. I've never played with Excel's DB functions much. So I'm not sure what these provide over Access (or over Excel's other built-in functions that might be able to do the same, at least in certain cases).

    5. I believe that it would be easier to create a nicely formatted report in Access (even if not using the Report Wizard). Also the reports are more flexible. I know that Excel has a Subtotals capability that might be useful in these types of applications. Opinions?

    6. I believe that it would be easier to create a form in Access (even if not using the Form wizard). Also the forms are more flexible. For example, can one create VBA for validation of the form created by Excel's Data | Form item (I know you can also create a form in Excel from VBA)? Is there any way to save the form so it can be used the next time you open Excel (w/o having to revisit the Data | Form menu)?

    7. I know you can filter in both. But if you wanted to save an Access filter, I guess you'd create a query for it. Not sure how you could do that in Excel.

    8. other items?

    Appreciate feedback. Hope I'm not biasing this by posting on the Excel board. If you do answer, I'd appreciate if you could also indicate if you use Excel as well as a DB pgm (or is your answer biased by what you use).

    TIA

    Fred

  2. #2
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Re: Excel vs Access (2000+)

    I used and developed in Excel exclusively for 11 years before branching into Access and other databases.

    Forms in Excel are easier to program than Access but are not as flexible. Relational databases are technically possible in Excel but not a great idea. (I have one that has been converted to SQL Server).

    Access is much better at allowing one to write a criteria query and quickly return information. Excel is better if complex calculations need to be performed.

    Access is the only way to go if you have more than a couple thousand rows. Excel bogs down long before Access does.

    Excel subtotals are superior but the possibilities in Access and the formatting of the reports rocks.
    Alan

  3. #3
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel vs Access (2000+)

    Access is inherently multi-user (using a split front-end/back-end)
    Excel definitely isn't (despite what MS might say!)
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel vs Access (2000+)

    My query manager is only fit to EDIT queries previously defined using data, get external data. And it eases adding paramters to queries and changing the source database's location.

    A (oversimplified) rule of thumb:

    - lots of calc, some data: Excel
    - Lots of data, little calc: Access.

    I agree the multiuser capabilities of Access are a strong point. Forget about Excel with that respect. NEVER share a workbook <g>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel vs Access (2000+)

    Another point: Excel is much better at charts than Access. Even though Access has basically the same chart engine, it is a PITA to work with. If you have lots of data, store them in Access, and use Excel to create charts based on the data.

  6. #6
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Paragould, Arkansas, USA
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel vs Access (2000+)

    Fred,
    I agree with all the comments so far. For my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>, Here's a picture of Access' Learning Curve: <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>. Here's the picture that best describes Excel: <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>.

    The "engine" that runs Excel is the ease of entering formulas in cells. For most people, these spreadsheets, the lists they make, and Excel's charting facility are all anyone could ask for. I have tried using Excels Dxxxx functions only once, then switched to Access in frustration, as the project really was a database intensive thing.

    Access is great for making lists and filtering with queries, but the query concept takes a bit of getting used to. Finally, Access really hums only when you understand the SQL language, and VBA processing.

    Errol

  7. #7
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel vs Access (2000+)

    Fred - I agree with all of the comments so far, particularly the one that presents a spectrum from lots of storage / little processing (Access or other true DB preferred) to little storage / lots of processing (Excel preferred). One thing I like about Access is that it is (reasonably) easy to use Excel as a front end, but let Access do all the heavy lifting for storage. You can still use Excel's calculation and graphic capabilities by linking or exporting the data (or summaries) back to Excel. One example - I had to categorize the hourly temperature and humidity readings for a given weather station by month and time of day over a 20 year period.
    With 20 years * 365 days * 24 hours I had 175K observations (even ignoring leap years!) - much more than would be comfortable in Excel since we needed one row per observation (although the information was provided to us in Excel, it was not in a useful format). In addition, we were given separate files for temperature and humidity readings - and both files had some missing (or invalid) data due to instrument failure. It would have been a nightmare to try and correlate the dates and times of observations and assemble the data in Excel - but it was simple to write a VBA routine to put the data in a form where it could be exported to Access, match observation dates with a simple relation, partition the temperature and humidity observations (essentially into a grid: temp -20 to +40 in 5 degree increments and humidity from 0% to 100% in 10% increments), and then count the instances of each temperature and humidity combination. This reduced the data list from 175K observations to ~2,500; a number well within Excel's ability to manage in a pivot table. My original intent was to use an Access query as the data source for a pivot table (which would have been a nice illustration of my point, btw), but some of the manipulating we had to do in Access for the Partition function (won't accept negative numbers, like sub-zero temperatures) made it easier to simply export the query results as an excel sheet and then undo some of the fiddling. The point was, though, that the graphing and pivot-table work was MUCH easier in Excel, while the data matching and assembly was easier in Access.

    To address some of your specific comments:

    <UL><LI> As noted, it is possible (but an incredible PITA) to create a relational DB in Excel - and without huge amounts of user coaching, it will be incredibly fragile (that is, it will be fragile in any event, but with on the spot hand-holding it might survive until your task is completed). Some one else has noted that it will not be truly multi-user; the best we were ever able to arrange was sequential access when we tried it. It worked, after a fashion - we were in adjacent offices all within shouting distance and we could just let one another know that we had saved and exited from a file kept on a common drive on our server. Even at that, the file became very slow as the number of data items increased. We required multiple user access because we were collecting evaluations of proposals by about half a dozen people (or about 80 competing proposals). In fact, it was the deficiencies of that Excel approach that got me working in Access in the first place.
    <LI> The Excel DB functions are limited and unwieldy, but perhaps that is just my lack of comfort and familiarity with them. I prefer using array functions instead - they just seem more intuitive to me (probably not to other people <img src=/S/wink.gif border=0 alt=wink width=15 height=15>) - again, that may be due to familiarity.
    <LI> It is possible to produce a nicely formatted report in Excel - it is just not possible to produce a nicely formatted report from an Excel "database!" I suppose if you really wanted to do it, you could - but the effort required to -say- list all records meeting a given condition(s), in a decent format, and including subtotals / grand totals would be outrageous, and the output would be of middling quality, at best. In Access (or any real database) it is only one step above trivial
    <LI> You are right that repeated operations (like filtering, selecting given records, reporting) have better persistence and are (far) easier to maintain in Access than in Excel[/list]

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Excel vs Access (2000+)

    Dean and all the others,

    Thanks for all your inputs on the topic. I think I have enough to get me thru a few minutes when I do the class (about 3-4 wks from now).

    If anyone else has any inputs, pls provide them - the thread isn't closed by any means.

    Note to Moderators: Would it be possible to post my original post to the Access and maybe VBA boards to see if other opinions surface?

    Fred

  9. #9
    2 Star Lounger
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel vs Access (2000+)

    Is there an easy way to do this? I've been trying to link data from an Access query with no success.

    Thanks,
    Eric

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel vs Access (2000+)

    Eric,

    You already posted a separate question about that.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel vs Access (2000+)

    Fred,

    If you like you can post in the Access forum (and the VB/VBA forum if you like) with a link to this thread and a request to reply here. I or another mod will then lock that thread, to avoid separate threads developing.

  12. #12
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Excel vs Access (2000+)

    I think the issue comes down to data integrity. Relational databases have tools built-in to help ensure the validity of the data. For example, you can enforce Referential Integrity between tables. You can establish validation rules for the table and for individual fields. These rules can't be circumvented by the user, intentionally or otherwise.

    Excel just doesn't afford these protections.

    I believe you should view Excel as a data analysis tool, not as a database tool. Store and maintain your data in Access, then Export information to Excel to take advantage of Excel's analysis capabilities. Depending on the situation, frequently a simple query can join the information in multiple Access tables into a simple row and column format the Excel likes.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  13. #13
    New Lounger
    Join Date
    May 2001
    Location
    New Jersey, New Jersey, USA
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel vs Access (2000+)

    Not just data integrity, but if you are writing an application for other people to use, Access gives much more control to ensure that the users don't do things they shouldn't. Excel is a much more flexible, but much more open environment - it is difficult to constrain users so that they only do what you expect them to, and much easier for users to mess up an application. On the other hand, knowledgeable users can more easily modify an Excel database for their own purposes..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •