Import Information from Excel

[Guest]

Here's hopefully a simple question:

I have created a master database that I will be sending individual reports
to obviously indviduals. The individual is to update the spread sheet that I
send them and then email back to me in the excel format. Problem - how do I
then take the excel spreadsheet and update my master document to reflect the
changes/updates? these are the steps I am currently taking:

Get external data - Import Spreadsheet Wizard - Check First Row Contains
Headings - store data in an existing table - Import Table to: Master then
finish

Message received: Microsoft Access was unable to apend all the data to the
table. The contents of fields in 0 record(s) were deleted, and 11 record(s)
were lost due to key violations, etc. click Yes or No

Any idea how I can get the table to automatically update? Or do I need to
build relationships between account managers to the master table and then
when I send them the file and update I just conitnually replace table that
exist?

Thanks

Debbie

 

[Arvin Meyer]

Instead of importing, you can use Excel just like an Access table and link
to it. To avoid conflicts, I'd add a date/time field in Excel and write a
query to append those records that are later than a specific time. If
existing records are edited, a second date/time field can identify them so
that they can be updated rather than appended.

If you do the appends in code, or through a macro, you can set the warnings
to false and quietly ignore the possible few errors. Don't forget to turn
the warnings back on again.

To link to an Excel "table" choose File ... Get External Data then change
the type in the dialog box from MDB to XLS (Excel) and find the file to
link, then write your query.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access