Access vs Excel as a DB

[Digitborn.com]

Hello,

There are many examples and aspects to compare these 2 products but I just want to point one little difference which is quite crucial and interesting.

Generally if you use small amount of data - 1 Worksheet / 5000 rows / 20 columns you can use Excel without bothering about the execution time, queries and work fast and convenient with it.

The point on Excel is that in 1 Column/Row you can differently Format the data(cells). For example - format as Number or Hour the cells in Column B depending on the data in other columns. That saves you from making 2 Columns - one for Numbers and another one for Hours. This helps you to save 1 of the columns when the data structure in other column is the same.

In Access (and generally all SQL DBs) this is not possible.

I'm interesting in your thoughts about it?

 

[Andrew Fergus]

I think you are trying to oversimplify things and misunderstanding what we are saying. Access and Excel are two totally different concepts, they just have a lot of overlapping as far as what you can accomplish with each.

There are things which Excel can do which Access can't, and there are things that Excel can do that Access can't. There are things that both Excel and Access can do, but Excel does better, and there are things which both can do but Access does better.

Quite simply, Excel is a spreadsheet program, Access is a relational database. With some effort, you can manipulate Excel to work as a database. And you can manipulate Access to perform spreadsheet tasks (i.e. calculations, etc.).

Also, you do not need to be a professional or expert to use Access for some simple tasks. Access is great at comparing two sets of data and returning matches (or unmatched records). There are Wizards which will walk you right through it. I have taught people with very little Access knowledge how to do this. To fully design a complete database in Access, though, you should have a good understanding of relational databases, rules of normalization, and Access.

If (when) you have a good understanding of both programs, then which program you use is not determined by which one you are more comfortable with, but rather which program completes the task you want to accomplish most efficiently and easily.

I agree 100% and couldn't have put it better myself, except for this one sentence :
"There are things which Excel can do which Access can't, and there are things that Excel can do that Access can't."

I think it should have read:
"There are things which Access can do which Excel can't, and there are things that Excel can do that Access can't."

In answer to your 3 questions 'Digitborn', I believe the answers are No, No and No. It depends on the task at hand. Excel is awesome at some tasks but then Access can be too - sometimes those tasks overlap, and sometimes they don't. You don't need to be a pro to use Access and Excel isn't a user-friendly concept of Access, it is a completely different tool.

I suppose it is a bit like comparing a hammer to a hand saw - both have their advantages and if you try hard enough you can use both tools for both tasks, but some tool/task combinations will make a lot more mess and be a lot more frustrating! Think of Access and Excel as 2 different tools for 2 different tasks and you won't experience the frustration many people do - especially when using Access for the wrong tasks.

Cheers and good luck!
Andrew

 

[Joe4]

I agree 100% and couldn't have put it better myself, except for this one sentence :
"There are things which Excel can do which Access can't, and there are things that Excel can do that Access can't."

Thanks Andrew. An obvious typo on my part. My hands don't work as fast as my brain does. I edited my original response.

Maybe I can hire you to be my proof-reader!

 

[Digitborn.com]

Thanks guys. I understand well what you said and it's useful for me at this stage to -reorder the info in my brain correctly I totally believe it's just like you said it.

The thing is that I'm still not quite sure where exactly are the "result" differences. I'm known with SQL, queries, relational db, progamming at some level, very good known of Excel as a user, VBA at an average level and Access at a very small level.

Maybe I can add to your comments that probably you should be at least at average level (let's say average professional) for Excel, Access, VBA, SQL to make the specific differences from Excel to Access. I said it because with my knowledge now, what I can imagine both Excel and Access can do it with some efforts along with VBA code. And the only "result" differences I see is speed, different methods doing the same thing - the same final result. And I cannot estimate which one is better and why is that.

That's why i'd like to comment such a general topic.

 

[xld]

I also agree about the bad practice (and as Joe mentioned earlier you can get what you want from Access using queries).

But I'm curious. In what context do you say 'Access doesn't hold a candle to Excel'? I default to Access over Excel for db jobs almost all of the time. But if it is a traditional spreadsheet task I am presented with, then Excel is the right tool for that job.

On what basis do you make this claim?

Understand that the following comments apply to Access 2003 and earlier. I understand that Access 2007 is a big step (whether it is forward remains to be seen, is excel 2007 a big step forward?), so I need to get familiar with that first.

Also, don't forget Access is really two tools. I don't see the actual database thingy as Access, mdb files driven by Jet. I am referring to that Access that does forms and reports. IMO that Access is a toy, far too difficult to work with, and cannot be called a professional deployment or development tool. Why anyone would use Access forms and reports when they could plug Excel into an mdb file is beyond my comprehension.

Personally, even the mdb is not ideal, and I would use SQL Server over it any day, although it is sometimes useful to have a free, license unrestricted database to use (although with SQL Server 2005 now, who needs it?).

I agree fully with the comments about relational data, but you don't need the Access tool to create that, or to load a database so. You can even do it with Excel tables, remember you can query Excel workbooks using ADO and SQL.

 

[Richard Schollar]

... the actual database thingy...

That a technical term Bob?

Merry Christmas to you & yours by the way!

 

[Digitborn.com]

Xld, I agree, I just wanted to say something like that too..You can query Excel workbooks using ADO and SQL, you can do the same Forms with VBA through Excel like these with Access or I'm worng..

In fact i'm reading http://www.workplacelife.com/2006/04/27/access-vs-excel-when-to-use-access/ now.

 

[Digitborn.com]

So, I just transformed my question/comments/thoughts to:

What can Access do which Excel with additional VBA code can't do? I can see now only:

1. In some cases Access can do faster operations with relational DB.

 

[Andrew Fergus]

I respectfully disagree xld. I think this is the part of the reason for differing opinions:

I don't see the actual database thingy as Access, mdb files driven by Jet. I am referring to that Access that does forms and reports.

Each to their own but I'm sticking with my 'hand tool' analogy - for instance, why would you want to build the realtional database aspect from scratch in Excel when the underlying engine already exists in Access?

Andrew

 

[Andrew Fergus]

Xld, I agree, I just wanted to say something like that too..You can query Excel workbooks using ADO and SQL, you can do the same Forms with VBA through Excel like these with Access or I'm worng..

In fact i'm reading http://www.workplacelife.com/2006/04/27/access-vs-excel-when-to-use-access/ now.

I had a quick read of that article and it appears you missed the 3 main points on the very first page. That summed up some of the differences quite nicely.

Andrew

 

[Joe4]

IMO that Access is a toy, far too difficult to work with, and cannot be called a professional deployment or development tool. Why anyone would use Access forms and reports when they could plug Excel into an mdb file is beyond my comprehension.

I totally disagree with this statement. If Access seems too difficult to work with, than I would probably say that is because you don't know how to work with it. Like I said before, I used to have that opinion too until I actually learned it. Everything is hard until you know how to do it.

I am quite proficient in Excel and VBA for Excel, and Access and VBA for Access, and let me tell you, I think it is much easier to create Access forms than Excel forms, especially if your data is already housed in Access. Creating Excel forms for data housed in Access seems like an unnecessary complication to me.

I have written about 50 Access applications which we use in production today, many that are automated. So I would refer to Access as an essential tool for us, not a "toy". It has helped us increase our productivity and efficiency many times over (and much more so than when I only did things in Excel).