best way to update table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David3553 - 30 Nov 2009 17:44 GMT

Hi,

I need to update a table every week with information I download from our
financial systems. I need to delete the old data and replace it with newly
downloaded data; because I need to capture changes to records, I can't just
append the current week's data to my table. I download the data to an Excel
file then I import the Excel file into my access table, replacing the
existing table.

What's the best way to refresh all of the data in my table while keeping the
table structure (field lengths, names, etc) in tact?

Reply to this Message

Jeff Boyce - 30 Nov 2009 17:54 GMT

You are asking a "how" question ... you've apparently decided the way you
need to accomplish something. You may get more use of the newsgroup and the
experience of folks here if you describe what that something is...

If you create a query that deletes all the records from the table, then
create another query that appends all the (Excel) records into that table,
you have not changed the table structure.

BUT!!! Excel-structured data is rarely well-normalized. So what, you ask?
So Access is a relational database and is optimized to work with
well-normalized data, not 'sheet data.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

Signature

Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

> Hi,
>
[quoted text clipped - 10 lines]
> the
> table structure (field lengths, names, etc) in tact?

Reply to this Message

Steve - 30 Nov 2009 20:11 GMT

It's not quite clear what you are doing! If you need to capture changes to
records, how do you do that if you delete all the old data in the table?

Secondly, deleting all the old data in a table and filling the table with
new data is highly discouraged! I bet if you look at the size of your
database file you will find that it has bloated significantly! A much better
way to replace existing data is to create an empty duplicate of your table
then when you download data to the Excel file:
1. Programatically create a copy of your empty duplicate table
2. Programatically import the Excel data into the copy of your empty
duplicate table
3. Programatically delete your existing table
4. Programatically rename the table in 2. to the name you need for the
database

Steve
santus@penn.com

> Hi,
>
[quoted text clipped - 10 lines]
> the
> table structure (field lengths, names, etc) in tact?

Reply to this Message