Do I need Access ? Won't Excel Suffice

Hey Bonar,

I'm doing the same thing myself at the moment.

I wouldn't call it naive, it depends on your application. If it's databasing, then Excel is fine if your needs are simple. However, Excel was not designed to be a database tool or handle large volumes of data (it also does not have the capability to index that data, as Access does). You can make it do otherwise with a lot of programming work, but why not use that time to work with a program that's done it already?

Put simply, Excel is a spreadsheet application whereas Access is a database application.

I hope that helps,
Harlan

Plusses: Access provides some excellent control over data, will not allow you to enter invalid data or the wrong kind of data, can enforce constraints without any need for vba, and is easier to design for other users to use. It can handle more data, of course.

Minusses: You will need to learn at least some basic SQL and database theory if you really want to understand Access. Most books on Access start with a chapter on database theory, and it is important to know at least what a primary key is and how table relationships are defined. If you create databases without keys, you are not creating an effective database. I suppose creating reports with Access can be a bit of work, too - I try to not get too fancy. This is also something that takes time to learn.

A final thought is that you need to be careful of users getting at the table data - typical Excel users might not realize that databases make changes "immediately" (there is no "close without saving changes" when you close Access) - so you have to think about who uses the data and how will they work with it. Of course with Excel, you also have to worry about who might use your spreadsheets improperly too.

I'd suggest you add Access to your toolbox - a little SQL can be a very powerful tool. Its worth the price of a class or other training, to do it right. In my opinion, using Access for data storage and Excel for data analysis and reporting can be a powerful combination.

Regards.

To follow up on Alexander's comments, Excel coupled with Access is a powerful combination.

Too often people try to use Excel as a database and end up with unwieldy data structures or bloated files that have performance issues.

Access can do a hell of a lot and the nice thing about it is that you don't need to know a whole lot about it to make it start working for you.

Access can quickly take large datasets and give you small chunk of information that you can then work on in Excel. For instance, I download huge data files from both Oracle and AS400 databases each week (700k+ records). It would be impossible to work with that data in Excel, but in Access I have automated queries that give me usable recordsets in Excel.

I'd definitely recommend learning it.

HTH,

Following up no the excellent comments so far, also consider your audience. Who will be using this, do they have Access installed and have they ever used Access? Also consider statements such as "a lot of data" (in the context of "when working with lots of data") is a relative thing. As Smitty pointed out 700k+ records is a lot of data. Anything under 1k rows use Excel, IMO anything over 20k records use Access, and anything in between depends on what the data is being used for. But when I say "relative" it is relative to the complexity of the data - there are instances where complex data is held in a database but there might only be hundreds of records as opposed to thousands. Search this forum for 'database normalisation' or 'database normalization' - that might help with the database theory mentioned in a previous post.

Andrew