Access to Excel - Automatic Updating

[Guest]

I have a unique challenge - I have developed a database in Access 2003 for
use in maintaining records, adding new records, generating reports, etc.
However, another user needs to be able to see the records that fit a certain
criteria (but not update/change them) in Excel, as he is running a Mac and
doesn't have Access.

How can I have an Excel spreadsheet that is constantly updated with the
newest information from Access without having to constantly export the data
by hand? My knowledge of visual basic is very, very limited, but I could
probably figure out the syntax if I had some idea of exactly what I wanted to
do in order to get the automatically updated Excel file.

 

[Guest]

You can design a query in Excel which extracts data from an Access db. Once
the query is designed, one of the toolbars you can have on the screen is the
"External data" tool bar. It has a red exclamation mark icon on it--this
refreshes the data.

As to whether the Mac version of XL can use such a query--I don't see why
not. Alternatively, you can publish the results of an Access query to the
web and have your Mac user access the data that way.

Dave

 

[aaron.kempf]

spit on mac people

1 person out of 100 shouldn't require HALF of the development
resources.
there is NO REASON to use a mac for anything.

Not graphics; not sounds-- NO REASON

 

[Ron2006]

My suggestion is to seriously look into the solution supplied by Dave F

You can update a spreadsheet, directly BUT you will encounter a
conflict between Access and the user trying to get into the spreadsheet
at the same time.

The best solution would be for the user to be responsible for the
acquisition of the most recent data. When he opens the spread sheet
with this type of query it will as if he wants to refresh the data. (He
should say yes)

If he keeps it open for any length of time, then he needs to remember
to refresh the data.

Even if you tried to update the spreadsheet, while he had it open,
Access/Excell on your side would complain and NOT let you do it.

Ron

 

[Guest]

Well, I've done this kind of thing before, so if there are specific questions
you have about how to do it, let me know.

Dave