On Jun 13, 11:33 pm, Angus <A...@discussions.microsoft.com> wrote:
> 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.
>
> "Norman Jones" wrote:
> > Hi Angus,
>
> > See Ole P. Erlandsen at:
>
> > Export data from Excel toAccess(ADO)
> > http://www.erlandsendata.no/english/...badacexportado
>
> > ---
> > Regards,
> > Norman
>
> > "Angus" <A...@discussions.microsoft.com> wrote in message
> >news:8CDD098B-93A5-4BA4-AB99-(E-Mail Removed)...
> > > Please help. I need VBA excel code by ADO for following:
>
> > > I have two columns in Excel andAccesstable.
>
> > > In Excel
> > > Column A Column B
> > > 1000 $350
> > > 1001 $420
> > > 1002 $470
> > > 1003 $50
>
> > > InAccess
> > > Column A Column B
> > > 1000
> > > 1001
> > > 1002
> > > 1003
>
> > > I want to updateAccesscolumn B according to data in column A
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
Similar Threads
