This is great to add new record inAccess.
But what if now I need to update the record inAccess, instead of add new.
eg, in excel, i have two columns, column A is a unique key value fromAccess
and I will update the record inAccessaccording to column A, and column B is
what I want to update.
Column A Column B
123 May
124 June
125 June
and the program will lookup the key in table, if it is 123 then update
"May", if 124 then update "June"...so on.
Try this (all steps are in Access):
1. Make a table that holds the data from the Excel spreadsheet. You
can either import or link to it using menu choices: File>External
Data>Import or ...Link. (Link is quicker as it doesn't need to define
fields.)
2. Create a new query, go to SQL view and paste the following SQL text
there:
UPDATE AccessTable INNER JOIN ExcelTable ON [ExcelTable].[ColumnA] =
[AccessTable].[ColumnA] SET [AccessTable].[ColumnB] = [ExcelTable].
[ColumnA];
3. Change the names of the 2 tables (AccessTable and ExcelTable) and 2
fields (ColumnA and ColumnB) as needed to fit your names.
4. Run the query.
5. Open the AccessTable. ColumnB should contain all the stuff in
ColumnB from the Excel table according to ColumnA.
6. If an error results or the results are not what you want, it may be
because the the data types are different. Just change the 2 fields so
they match and try again. From your example, it looks like you can go
with all Numbers.
Dave
