linking excel spreadsheet to access database

[pat scanlon]

Can I enter information into an excel spreadsheet that
would automatically update a linked Access Database and if
so, how, specifically can it be done?

If this can't be done, can information entered into an
Access database automatically be updated in a linked Excel
spreadsheet??? If so, how, specifically can that be done?

 

[Nikos Yannacopoulos]

Pat,

Access "reads" form linked Excel files just fine, and exports to Excel files
fine, but doesn't "write" to linked ones well.

That is to say, if you use an Excel sheet to enter data and that sheet is
linked as a table in an Access database, the db will treat it as a table, so
it will be up to date at all times. Alternatively, you can use the linked
ssheet to append to an Access table (rather than use the ssheet as your
primary table), in which case you will have to run an append query in Access
to bring it up to date. Both work very well, the choice depends on the
circumstances (does the sshett contain all data at all times, or just
incremental data?).

On the flip side, if you use Access for data entry, you can export data to
Excel (I recomment the TransferSpreadsheet action / method), through which
you can overwrite an existing Excel file or a specific worksheet in it, or
create anew file or a new sheet in an existing one, whatever suits your
needs. This works fine as well.
In theory, you can "write" from Access to a linked Excel spreadsheet, but in
practice this results to the Excel file getting corrupted most of the time,
and is strongly advised against.

HTH,
Nikos