ADO excel VBA to update data in Access
 

Please help. I need VBA excel code by ADO for following:

I have two columns in Excel and Access table.

In Excel
Column A Column B
1000 $350
1001 $420
1002 $470
1003 $50

In Access
Column A Column B
1000
1001
1002
1003

I want to update Access column B according to data in column A

ADO excel VBA to update data in Access
 

Hi Angus,

See Ole P. Erlandsen at:

Export data from Excel to Access (ADO)
http://www.erlandsendata.no/english/...badacexportado


---
Regards,
Norman


"Angus" wrote in message
...
Please help. I need VBA excel code by ADO for following:

I have two columns in Excel and Access table.

In Excel
Column A Column B
1000 $350
1001 $420
1002 $470
1003 $50

In Access
Column A Column B
1000
1001
1002
1003

I want to update Access column B according to data in column A

ADO excel VBA to update data in Access
 

This is great to add new record in Access.

But what if now I need to update the record in Access, instead of add new.
eg, in excel, i have two columns, column A is a unique key value from Access
and I will update the record in Access according 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 to Access (ADO)
http://www.erlandsendata.no/english/...badacexportado


---
Regards,
Norman


"Angus" wrote in message
...
Please help. I need VBA excel code by ADO for following:

I have two columns in Excel and Access table.

In Excel
Column A Column B
1000 $350
1001 $420
1002 $470
1003 $50

In Access
Column A Column B
1000
1001
1002
1003

I want to update Access column B according to data in column A

ADO excel VBA to update data in Access
 

On Jun 13, 11:33 pm, Angus 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" wrote in message
...
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: FileExternal
DataImport 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
the
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