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?

 

[Digitborn.com]

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]

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

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]

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]

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

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 .

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]

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]

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

That is my point - why bother?

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.

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]

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]

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]

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

Except make a coffee or do the household chores.

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