Replace a specific table with the Excel file by code.

[Lin]

Hello,
I have a table name 'Product_tbl'in my Price DB.
Each month I will get the update Price list in excel. Is
there anyway I can replace the 'Product_tbl' by new excel
file by using a code.
I have a button named "Update" on my form. What I
would like to do is when I click that button replace
the 'Product_tbl' with downloaded excel file.

Any help, it is really appreciated.

 

[Gary Miller]

Lin,

There are a variety of methods for this. Probably the
easiest way if the spreadsheet is always consistent and
amenable to being linked directly to Access is to link
Access to the spreadsheet. The name of the new spreadsheet
updates would need to be changed to match the one that you
are linked to so the linked one should probably be named
something generic. If you delete the old one and replace it
with the new one with the same name, the link should hold as
long as they are structurally identical. For this to work
without linking to a named range, generally the spreadsheet
will just want to hold the products and prices and not have
headers and misc. rows that would mess Access up.

If this works for you, you should then be able to create a
Delete query to dump the old records and an Append query to
add the new updated records into your Product table. You can
then just run the queries through the code on your button.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________