Linking MS Access and MS Excel

[Lou_Reed]

I am working on linking MS Access and MS Excel. At first glance it seems intimidating.



The are so many ways to do it!

I am not sure which is applicable to which case. By that I mean when to use what method in what situation.

I am looking to see if there is a Microsoft document for this or for that matter any other documents regardless of origin.

Please give me a reference on this.

Right now I have discovered many how-to papers for the different ways of llinking, but none that tell
me why you would want to do it one way and not the other.

Any help appreciated. Thanks in advance.

Respectfully,


Lou Reed

[John_G]

Which is linking to which?

I'll address how to link an Excel spreadsheet to an MS Access database.

It depends on what you want to do with the Excel data. If you just want to read/view it, it is relatively easy - you use the linked table manager to create a link to the spreadsheet, after which the spreadsheet is treated as a table in Access.

If you want to update (edit/add/delete) data in the spreadsheet, then it is not trivial, requires a good knowledge of VBA, and a good knowledge of how Excel works when referring spreadsheet data.

[Lou_Reed]

I appreciate your timely reply. This brings me to another question: why link MS Access 2010 and MS Excel in the first place? I have no legacy Excel spreadsheets.

All of my work on this has been in MS Access 2010. Why complicate it MS Excel? There sees to be no compelling reason to link it to MS Excel.

It just seems that there is nothing in MS Excel that you cannot do in MS Access and do it probably better.

Any thoughts on this?

Respectfully,

Lou Reed

[John_G]

... there is nothing in MS Excel that you cannot do in MS Access and do it probably better.

Someone else posted that too, almost verbatim.

And, IMO the statement is false.

MS Access and Excel are different products, with completely different philosophies of data handling. Which of the two is used depends on the requirements of the application to be implemented.

Excel is designed for calculations between groups of cells, vertically (columns) horizontally (rows) or both. Excel can store formulae in cells, defining how the cells' values are determined.
Excel can easily deal with "what if" scenarios, where the effect if changing critical parameters is immediately apparent.

Access cannot do most of these things. Access deals with "records" - rows - of data one at a time - it can only "see" data of the record it is on. Access has queries, to view, append, update or delete rows; Excel does not. Access stores data in tables - Excel does not. Access is largely form-based, Excel is not (though there is a forms capability - sort of).

Anyway, the upshot of all this is that neither one of these can be said to be better than the other - it depends entirely on what your needs are. However, choosing the wrong one for a project can lead to "problems" to put it mildly.

My advice would be that if you don't have any Excel spreadsheets (legacy or otherwise), then don't concern yourself with it at this point.