Access vs Excel

  • Thread starter Thread starter Jaz
  • Start date Start date
J

Jaz

I wanted to get the opinion of both the excel and access group on this
subject.

We currently have an ERP but is lacking in some aspects. In order to get
around the shortcomings, I have an user that is very proficient with excel.
He has made spreadsheets that access the information out of our ERP and
mimics some of it's functions.

He is using excel to store information and has created interfaces for users
to enter in data.

My question is, should we continue using excel to do this. We have hundreds
of excel files that get created with information everywhere. OR should we
start using access to manage this.

Any thoughts on this subject?

Thanks,
Jasper
 
I am an Access developer and I spend probably 80% of my development time
converting Excel spreadsheets into fully featured Access databases. From my
view, it seems risky to leverage your data management with Excel as your
database and reporting tool. In some of the most elaborate Excel
spreadsheets I have seen, a well thought form(s) for data entry beats the
look of a spreadsheet format. Make a mistake in Excel, and sometimes you may
just corrupt all your data, rendering the spreadsheet unusable, what are your
options at that point?? There are many other reasons for implementing Access
vs. Excel, just as there may also be many reasons for investigating SQL
Server as your database solution vs. Access.
 
I have beeen in a similar position ona smaller scale where my sales
reports were dependent upon data captured into Excel from our main
accouting system. With the passage of thime, though, I realised that
the size of the database was too unwieldy for Excel. Now all my data
is in Access, I have links to the accouting system's SQL tables as
well, and i can produce pivot table reports etc in Excel from the
Access queries I have written. This is much beeter than using Excel
alone.

Regards

Chris
 
As soon as I see "hundreds of spreadsheets" this is telling me that you need
a database. If you make a change on one of the spreadhseets you probably
have to go do it on all of the others, whereas in Access you just change it
once, then select which data to apply it to.
 
I would say the answer is right there in your question: " We have hundreds
of excel files that get created with information everywhere."

The key words being "hundreds" and "everywhere".
 
I agree with both posters. I too work with Access and have converted many
spreadsheets to Access. Excel is excellent and I am taking nothing away from
it as it is a very powerful application. however, for storing data Access is
more flexible. Access is designed for this sort of thing and will be much
more efficient. Retrieving the data will be more simplistic because of the
variety of way way to format your reports and the various way to pull your
data from one form. In otherwords, as your users are inputting data, when
it's time to pull a report you can have a form set up with all your reports
and each report can pull in a variety of ways wil a click of button. Also,
depending on how much information (fields) you have, on a spreadsheet you are
running across it to view information at the end. In access, everything is
placed on a form an you can view each record on the full screen one at a time
instead of trying to follow one record across the spreadsheet on hope your on
the same row without having to use a ruler (it's just a small thing but
important).
 
hi, jasper !
I wanted to get the opinion of both the excel and access group on this subject.
We currently have an ERP but is lacking in some aspects.
In order to get around the shortcomings, I have an user that is very proficient with excel.
He has made spreadsheets that access the information out of our ERP and mimics some of it's functions.
He is using excel to store information and has created interfaces for users to enter in data.
My question is, should we continue using excel to do this.
We have hundreds of excel files that get created with information everywhere.
OR should we start using access to manage this.
Any thoughts on this subject?
Click to expand...

since your post is in an excel n-g, let me express my point of view...

- "hundreds of spreadsheet" means (for me) your are working hardly (but you can do it in an more intelligent manner)

- access (or data bases) requires "well-design" from the beginning (so, you should use this "requirement" for excel)

- tips ?
RDBMS
Building and using a relational database in Excel (with a little help from MS Query)
http://www.tushar-mehta.com/excel/newsgroups/rdbms_in_excel/index.html

very interesting paper:
http://www.fmsinc.com/tpapers/genaccess/DBOD.asp

hth,
hector.
 
IMO the correct answer is depends... If hundreds of files is a problem then a
database might just be the way to go. However I have seen instances where
this was not a problem. For example creating Journal Entries. The spreadsheet
extracted data from ERP tables and used that to generate month end accruals.
Ultimately there were hundreds of these spreadsheets. Since they were
properly organized there was no issue. The nice part was that the files were
easily transfered to auditors when questions arose.

Of course I have also seen just the opposite.

In general I like spreadsheets more for doing analysis than as an integral
part of the system. Hooking an XL pivot table back to source files (no longer
restricted by the 65K limit) or with MS Query to extract and analyze data...
 
Back
Top