Can you use Access as an interface to Excel?

[Guest]

Pardon if this question has been answered elsewhere. I've been browsing
through the various posts, but I didn't find an exact answer.

Basically, I would like to use an Access form to enter data for an Excel
spreadsheet. Originally, I had planned to link the Excel spreadsheet to
Access and do the data entry within Access on an Access form, but I've read
that any updates done in Access will not be reflected in the spreadsheet. Is
this still valid for Access 2003? If so, what (if any) is the best work
around or am I creating more work than necessary?

Thanks in advance for your advise!

 

[Guest]

First, why do you need something in Excel format? Second, your question has
several answers depnding on how you do things. The short answer is "yes." The
longer answer is that you can create a query in Access and "export" the
results of that query into an XLS file. That, in effect, creates a new
spreadsheet from scratch, using current data. Another answer is that you can
use Access as a "dynamic data source" for your spreadhseet, in which case
data changes would be reflected in the spreadsheet every time you re-opened
it.

But again, since you can re-create a spreadsheet file from a query, I really
don't see the point of using a static spreadsheet. As a matter of fact,
unless your doing something like accounting, I don't see the reason for a
spreadsheet at all.

 

[Guest]

I totally agree, but there are others updating the spreadsheet too who do not
have a license for Access (we're sharing the spreadsheet on a network and
doing our own entries). This also is an accounting project. I'm just trying
to streamline my entries to the spreadsheet.

Per your comments, it sounds like the "dynamic data source" is my best
option.? If so, how do you set that up?

Thank you for your advise!

PS The "TransferSpreadsheet" option seems to complex, but it could be I
don't get how to use it.

 

[Guest]

How do you use "dynamic data" -- can you direct me to a "how to"?

Thanks!

 

[CatherineDiane]

Did you ever get an answer to this "dynamic data source" option?? I am
working on something very similar and would love to know how to achieve this!

Thanks!!

 

[John W. Vinson]

Did you ever get an answer to this "dynamic data source" option?? I am
working on something very similar and would love to know how to achieve this!

Excel can use Access as a source of data, and you can link to an Excel
spreadsheet from Access - but due to Microsoft's losing a lawsuit, you cannot
*update* an Excel spreadsheet from Access.

Also, Excel (unlike Access) does not - to my knowledge - support multiple
concurrent updating users. If person A is updating a spreadsheet, person B
will not be able to read it.

John W. Vinson [MVP]