Exchanging dynamic data between MS-Access to Excel

Question

First, I am not a programmer.  My experience is as a Business Analyst, but have been able to teach myself enough to be able to develop some simple Access applications on a voluntary basis for some local users.

My operating environment is Windows XP (SP3), and I am using Access 2000 and Excel 2000.

The application I am currently developing is a cemetery management system for a local church.  One of the user requirements is a visual display of the entire churchyard.  Each gravesite must be uniquely identified.  Gravesites can have one of three possible status conditions.  The actual status data resides in an Access table, which can be user updated as conditions require (eg. when there is a burial, changing the status from "unoccupied" to "occupied".)

My design strategy has been to create the display in Excel, and use Excel's Conditional Formatting feature, with different colours to show the current status of the gravesite.  I want Excel to be able to dynamically react to status changes whenever they are recorded in the Access table.  As the display is updated in Excel, I then want to be able to return the updated display back into an Access form for use by the users.

I'm thinking that DDE would be the principal tool to make this all happen.  But I've never used DDE before, and am not sure how to go about achieving my goal. 

I'm trying to arrange matters so that the entire process is triggered by a single Access macro which would appear (as a clickable control) on the user's main menu.  Once the user clicks this, then everything else should happen in the background without additional user intervention.  My specific questions are:

1. How to send updated status data from Access to Excel, and have the data appear in a permanent Excel spreadsheet?  I've already tried this a couple of times, but my problem is that a new spreadsheet is created each time.  I don't want that, if I can help it.
2. How to manipulate the data once it's been brought into Excel so that each individual gravesite on the display shows the correct new status?
3. How to bring the updated display back into Access?

I know this is a bit wordy, and I apologize for that.  I've scoured the onboard 'helps' in both Access and Excel, and have found tantalizing references to various aspects of thsi problem, but no clear cut description of how to make it all work.

I would be grateful for any ideas from more experienced Access developers. 

Many thanks in advance.

"Haligon"

Answer 1

Okay, a few things to start with.

1. Microsoft lost a lawsuit many years ago and that affected the ability of updating Access through linking Excel.  Access 2003 is one of those which is affected by this (I am not sure if later versions like 2013 can do it but I don't think it can).
2. There is a problem with keeping it dynamic and visible.  Because you would have to send the data from Access to Excel the file needs to be closed for it to be able to open and update. 

So, the real solution is going to mean that you need to do it ALL from within Access (at least that's my determination).