Linking MS Excel and MS Access

[Lou_Reed]

I am looking for a basic document on linking MS Access 2010 with MS Excel 2010. I need to learn how to do this, and I have found many


docs on the internet, but none of them are what I would call a getting started document.

Also, i would like to know if this is even a good idea. By that I mean some documents that I have found on the internet simply try to discourage doing this at all.

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

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[June7]

No, Access cannot do everything Excel can do and do it better and vice versa. They are different tools for different purposes.

A simple Excel sheet can be linked by Access with the External Data utility or VBA write to Excel file. Excel can set links to tables or queries or VBA can establish a connection to Access to open a recordset. I have used all of these methods. However, I also completely removed Excel from a db design when I inherited the db to complete development. It was using Excel as the user interface for data entry and reporting. It was a cumbersome, frustrating, and totally unnecessary arrangement. On the other hand, I have an Excel file that pulls data from that same db as an interface for transferring the data to another proprietary program.

[John_G]

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

I strongly disagree with that. The two programs are totally different, for meeting different needs. Excel is a spreadsheet, allowing all the rows, columns and cells to relate to each other at the same time. It has the concept of ranges (regions, if you like), horizontal, vertical, or rectangular. Excel also relies heavily on calculations, and is good at dealing with "what if" scenarios. Access does none of these easily, if at all.

Access is a database, defining and enforcing (optionally) relationships between data entities (tables). It can handle multi-level structures easily, and with proper design, eliminates data redundancy.

I could go on, but you get the idea. Making a blanket statement that one is better than the other is foolish. Which one you select depends almost entirely on the data you will be working with.

Yes, it is very easy to make the wrong choice. My own work experience is that people will often use Excel when they really shouldn't, simply because that is the one they knmow something about and they can't be bothered (or are "afraid") to look into Access as a better tool.