# Access vs Excel as a DB



## Digitborn.com (Dec 18, 2007)

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?


----------



## Joe4 (Dec 18, 2007)

> 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


What you are describing is the inherent nature of how relational databases work.  Databases are set up with distinctive records and fields, where a field has to follow the given format it has been assigned.  You generally have repeating records of the same fields.  While this may seem limiting, it is actually very powerful and allows you to run queries on your data while maintaining the integrity of your data, database, and results.

Excel is a spreadsheet program, not really a database, so there is no "implied" formatting as you move down a column, as there is no inherent relationship between different rows of data, just what you set it up to be.

So Excel and Access often "overlap" in what they can do, they actually serve different purposes, and the key is recognizing which one serves your current purpose better.

By the way, regarding your initial comment/example, as long as you aren't trying to do then use the result of your number/time field in another calculation, you may be able to get what you want in Access by formatting the field as a String, and then using the IIF and FORMAT functions on your calculation to return the value how you want it displayed.


----------



## xld (Dec 18, 2007)

Exactly, what is described is a weakness of Excel as a database IMO.

Try importing a CSV file with different data types into Excel and see what happens. Moral, don't try and save space/columns/formats in such a way, it is not worth it in the longerterm.


----------



## Joe4 (Dec 18, 2007)

> Try importing a CSV file with different data types into Excel and see what happens. Moral, don't try and save space/columns/formats in such a way, it is not worth it in the longerterm.


Excel and Access both seem to have problems when they try to "guess" or "assume" how data being imported is formatted.

Typically, when importing CSV data into Excel, instead of letting Excel try to figure it out (which is does poorly), I always use Data | Get External Data | Import Text File.  This invokes the Import Wizard where I can at least tell Excel how each field should be formatted.

Likewise, if you have ever tried to import Excel data into Access, it also has problems (especially if you have some null fields).  What's worse, is Access usually doesn't tell you what the problem is.  It just says there is an error trying to import the file and aborts the import process.  On more than one occassion, I have converted my Excel data to a text file so I can import it into Access.

In general, I find that Microsoft products have trouble when they have to "think too much" or "guess what you are trying to do".  It is for this reason that I despise Microsoft Word and their "Autoformatting".


----------



## xld (Dec 18, 2007)

My point was not that Access is better than Excel (in this or any other point, it doesn't hold a candle to Excel), but that trying to cram more than one information type into a column is bad practice - period.


----------



## Joe4 (Dec 18, 2007)

> My point was not that Access is better than Excel (in this or any other point, it doesn't hold a candle to Excel), but that trying to cram more than one information type into a column is bad practice - period.


Agreed.  Sorry to go off on a tangent.  I was just going off on one of my pet peeves with Microsoft products -- that their attempts to make their programs "more user friendly" for non-technical people often have adverse affects and cause unnecessary frustration for us programmers.


----------



## Andrew Fergus (Dec 19, 2007)

xld said:


> My point was not that Access is better than Excel (in this or any other point, it doesn't hold a candle to Excel), but that trying to cram more than one information type into a column is bad practice - period.


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?

Andrew


----------



## Joe4 (Dec 19, 2007)

Like Andrew, I was agreeing with the statement about mutliple data types in one column.  Like Andrew, I also do disagree with the statement that Access does not hold a candle to Excel (I should have read everything a little more carefully, I totally overlooked that statement).

I was quite proficient in Excel and VBA before I ever started dabbling in Access.  Access was the great "unknown" and I was a little intimidated by it, so I avoided it whenever I could.  However, once I learned it and became comfortable with it, it is know usually my program of choice, and I use it much more than Excel now.  I find it much more powerful than Excel, especially when dealing with relational data.

So whenever I see someone making disparaging remarks about Access, I know from experience that most likely the problem is that they are just not too proficient in it and not comfortable using it.


----------



## Digitborn.com (Dec 19, 2007)

So, I can get out these conclusions:

1. Access can do everything Excel can do
2. More powerful, real DB, just for professionals
3. Excel is the user-friendly conception for Access

These 3 points include working with data - add/edit/delete/analyze & working with userforms(controls) - VBA. 

The Charts, PivotTables and other little analyze tools are things which we're not talking about here. You can always import a table from Access (through txt file if you want) in Excel and do a chart or analyze with Excel tools additionally if it's user-friendly with the stuff you employ or people you work.

Am I close to find the "hot water"


----------



## Joe4 (Dec 19, 2007)

> 1. Access can do everything Excel can do
> 2. More powerful, real DB, just for professionals
> 3. Excel is the user-friendly conception for Access


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 Access can do that Excel 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.


----------



## Digitborn.com (Dec 18, 2007)

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 (Dec 19, 2007)

jm14 said:


> 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.
> 
> ...



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 (Dec 19, 2007)

> 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 (Dec 19, 2007)

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 (Dec 19, 2007)

Andrew Fergus said:


> 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 (Dec 19, 2007)

xld said:


> ... the actual database thingy...



That a technical term Bob? 

Merry Christmas to you & yours by the way!


----------



## Digitborn.com (Dec 19, 2007)

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 (Dec 19, 2007)

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 (Dec 19, 2007)

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


xld said:


> 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 (Dec 19, 2007)

Digitborn.com said:


> 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 (Dec 19, 2007)

> 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).


----------



## Digitborn.com (Dec 18, 2007)

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?


----------



## Digitborn.com (Dec 19, 2007)

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."

Is it better to say:

"There are things which Access can do easier than Excel and there are things that Excel can do easier than Access."


----------



## Joe4 (Dec 20, 2007)

> 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."
> 
> Is it better to say:
> ...



I did say that too... in the sentence immediately following the one you quoted from me.

I believe both statements are true.  I am sure that if one looked hard enough that they could find things that each does that the other does not.


----------



## xld (Dec 20, 2007)

RichardSchollar said:


> That a technical term Bob?
> 
> Merry Christmas to you & yours by the way!



You know me Richard, I am stickler for correct terminology .

Wish a happy christmas to Mrs Schollar and the babes from me, and looking forward to seeing/talking to you again in 2008.


----------



## xld (Dec 20, 2007)

jm14 said:


> ...  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.



Maybe a form is somewhat quicker in Access, once you have gotten used to the many idiosyncracies therein, but it will never be as quick as using a worksheet based form in Excel, with the rich presentation and validation facilitis builtin.


----------



## xld (Dec 20, 2007)

Andrew Fergus said:


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



No problem with that, it's what makes life interesting .



Andrew Fergus said:


> 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?



Once you have written a wrapper class, building the DB is trivial. As with everything, a good relational DB depends upon good design, not the tool that you use to implement it. I happen to think that the Access realtional map thingy (another technical term just for you Richard ) is an attempt, but so poor that I never use it, and it realluy only helps if you front-end the database with Access, which as you may have gathered, I try to avoid doing.

As an aside, I built a system once with an SQL-Server DB, and one of my colleagues used Access as a front-end to that DB. Now you may think that is a good idea, I was astounded (we also had enterprise scale query tools besides Excel).


----------



## Digitborn.com (Dec 20, 2007)

Does it means that once you build the good relational DB for your purposes, the good analytical tool with VBA and Excel you don't need some of these good advantages from Access. Once you have the tools, the modules, the pieces of code which you just copy/paste/reorder you can do anything you like with the same result with Excel and Access. My problem begins that I cannot read data from closed Excel workbooks and I found this good solution - http://www.mrexcel.com/forum/showthread.php?t=294183 but only for Access file. I just want to do this thing without opening, open/close the file NatFun2006_ok.xls:

This userform is on file Module1.xls:

Private Sub UserForm_Initialize()
With Workbooks("NatFun2006_ok.xls").Worksheets("ParFun")
For Each thing In .Range("C2", .Range("C65536").End(xlUp)).SpecialCells(xlCellTypeConstants)
If thing.Value <> "" Then
ComboBox1P1.AddItem thing.Value
End If
Next
End With
End Sub


----------



## Andrew Fergus (Dec 20, 2007)

xld said:


> Once you have written a wrapper class, building the DB is trivial.


That is my point - why bother?



xld said:


> As with everything, a good relational DB depends upon good design, not the tool that you use to implement it..


 Agreed, but to a point.  If you had a db in Excel and wanted to present the same data with half a dozen different views of the world, then there were would be an extraordinary amount of shagging around in Excel to get these 6 different reports to work (plus the inevitable requests for changes) versus the ease of dynamic reporting within Access.



xld said:


> I happen to think that the Access realtional map thingy (another technical term just for you Richard ) is an attempt, but so poor that I never use it, and it realluy only helps if you front-end the database with Access, which as you may have gathered, I try to avoid doing.


Once again I think the relevant point from your statement is "I never use it" so to mangle an old saying I believe it is a case of "unfamiliarity breeds contempt".  I reckon a little more time spent with Access would result in a different opinion.

Cheers and good luck!
Andrew


----------



## SydneyGeek (Dec 21, 2007)

DigitBorn, 

I get the impression that you dislike Access and that colours your opinion of it. As several have already pointed out in this thread Access and Excel are very different tools for different jobs. Using one to the exclusion of the other is a dumb idea, because they work together well. 

Access (and other databases) will help you to organise complex problems (like a front-end system to run bookings, invoicing and other parts of your business). 
Excel lets you build analytical and forecasting models and gives you a ton of flexibility for adjusting your calculations in a way that no database will easily do. But, throw a lot of data at Excel and it will gum up; try to let more than one user into a workbook and you're stepping on dangerous ground. And I would far rather set up a form with security, validation and flexible data entry options in Access. 

Granted, there are areas where the two overlap. And Excel is easier to learn how to use. But don't write off Access because you don't understand it. You'll miss out on a lot of power.

Denis


----------



## Digitborn.com (Dec 23, 2007)

I think I got some more of the general picture. Maybe I should learn Access to get the little details which are advantages for Access. The last few days I read and tested options where I can use ADO to Query Data from a Closed Excel Workbook:
http://www.beyondtechnology.com/geeks023.shtml

Using UserForm parameters in ADO SQL Queries:
http://www.ozgrid.com/forum/showthread.php?t=45789

Populate a comboxbox in a userform with an Access-recordset:
http://www.ozgrid.com/forum/showthread.php?t=45789

Retrieve data from Access to Exel by executing stored questions in so called Access-databases (per se MS Jet Database):
http://www.ozgrid.com/forum/showthread.php?t=20107

...and some other small things. Maybe I won't get out of my impression:

1) you can do anything you want with Excel and VBA. 
2) Access is not a bonus except for speed when you need to use many records at the same time. Access is just another method which represents a real relational DB. 
3) Excel is a spreadsheet where you can simulate a real relational DB. But still the important thing is to be very professional with Excel and VBA or with the same efforts and brain choose another option - learn Excel and Access + VBA at more average level and again to do your work.

This is the battle for me at the moment. what to do!? throw in excel + vba more and more or spend some more time with access + vba?!


----------



## Andrew Fergus (Dec 23, 2007)

Digitborn.com said:


> 1) you can do anything you want with Excel and VBA.


Except make a coffee or do the household chores.


Digitborn.com said:


> This is the battle for me at the moment. what to do!? throw in excel + vba more and more or spend some more time with access + vba?!


Both.  If you can do both that is a real bonus for you and your employer.  Don't forget the Access VBA is almost identical to Excel VBA so if you learn one then you have already made a huge start on the other.  Another thing is that you can't be expected to learn 90%+ of the functionality of an application and be expected to remember it instantly all of the time.  What helps is that you know a certain function or method exists, and you know how and where to find help on whatever problem you face.  So IMO it is a matter of familiarity with the application and the help files / internet help forums - that can take you a long way.  But like any practical discipline : practice makes perfect!

Cheers and good luck!
Andrew


----------



## Digitborn.com (Dec 18, 2007)

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?


----------



## Digitborn.com (Dec 24, 2007)

The coffee thing...this is a real problem. I should declare the cup, the coffeee and the oven as Objects and do some Methods on them  I wonder when Microsoft finally make available all Excel and VBA options to the wanted users


----------

