Update Access 2003 records using Excel 2003 via macro

[kennethqiu]

Hi everyone,

I have a Access2003 database (c:\db\testdb.mdb) contains a table(product) with product numbers (CSPC) and prices (Price) and some other fields.

An Excel2003 file (c:\db\priceUpdate.xls) includes a worksheet (NewPrice). There are two columns (CSPC and Price in the worksheet, and start from row 2).

I want to use the Excel prices to update the Access prices; but I don't know how to to code the macro. Can anyone help me out on this issue?

Thank you in advance.

[romperstomper]

I suspect it may be simpler to set the worksheet up as a linked table in Access and then run an update query joining the two tables.

[Sean Thomas]

try something like this

Please Login or Register  to view this content.

this will add just the one line of data into the access database, so you may need to do a loop

[kennethqiu]

@Sean Thomas:
I got a "compiler error: User-defined type not defined" @ line 4 "Dim Command As ADODB.Command".
What would be the cause?

@romperstomper:
I think your solution is one of the easiest way to solve this issue. But I want to learn how to work it out and update multiple tables as well. Thank you for your respond.

[kennethqiu]

@Sean Thomas:
I solve the compiler error.
I changed the INSERT statement to:
CommandText = _
"UPDATE product" & _
"SET product.price = '" & Sheets("NewPrice").Range("B2").Value & "'" & _
"WHERE product.cspc = '" & Sheets("NewPrice").Range("A2").Value & "';"
It prompts up "Error executing UPDATE statement."

[kennethqiu]

@Sean Thomas:
Problem solved. Just miss a space after the table name.
Thank you very much.