update data from Excel

[Guest]

(apologies for the cross-post in access.devtoolkits)

I copied some data out of Excel for cleanup, and found that you can't just
paste the data back in. I just need to write over the data in my Access
table with the corrected field in my Excel spreadsheet. My spreadsheet has
the same ID field as my Access table, so if I knew what I was doing it ought
to be straightforward.

I've tried playing with update and append queries, but when I click on Query
type, there are no options available (either to update or append), so I
haven't been able to use these.

How can I approach this?

Thanks, Amanda

 

[Naresh Nichani]

Amanda:

One idea can be to create a linked table in Access which links to the Excel
spreadsheeet.

In the Access database window activate the "Tables" tab. Here right click in
a blank area of the tables window and click "Link Tables...". In Files of
Type select "Microsoft Excel" and link in the sheeet you want to import.

In the Access database window activate the queries tab and select the linked
table you created now. Click Query | Append Query and append the data to a
new table.

Regards,

Naresh Nichani
Microsoft Access MVP

 

[Guest]

I have no problem getting the Excel spreadsheet to link.

But I have no option available in Queries to Append- when I click on the
drop down menu, it's just an empty grey box.

Why would the Append option not be available?

 

[Guest]

I have solved my problem.

I still cannot see options to append and update, but I did change the
deisgn of the query to SQL view and created the update command in SQL. This
worked perfectly.

- Amanda