Refreshing Data Daily From a Linked Excel Spreadsheet

  • Thread starter Thread starter Brad
  • Start date Start date
B

Brad

We have an Access 2007 application that pulls certain fields from a linked
Excel spreadsheet into an internal Access table on a daily basis.

The current process is to delete all of the rows in the internal Access
table and then use a query to append the selected data from the linked Excel
spreadsheet into the internal table.

Is there a better way to do this?

Can this be done in one step? (delete old data and refresh the table with
new data)

The field names in the Excel spreadsheet are different than the field names
in the internal table (and in the VBA code). The Append Query works nicely
for making this transition, but we have wondered if there is a better way to
handle this.

Thanks,

Brad
 
Simple question: What could be improved about it? Does it take a long
time to run? Does it cause some other headaches?

If it ain't broke, don't fix it. Unless you are having issues you need
to address, I would leave it be.

However, if you are completely emptying that table each time you could
create a copy of the table (empty) for use as a template. Then instead
of running a query to delete the data in the table:

DeleteObject to delete the table and all of it's data quickly and easily.

CopyObject to copy your template table to recreate your original table.

Then run your extract/append routine as normal.

Phil
 
You could add a calulated field in your append query like this --
Import_Date: Date()
Wha-la it is done and have a history trail to boot.
Just include criteria for Import_Date in all of your data handling processes.
 
Karl,

Thanks for your help.

I think that I will use the technique you suggested on one of the tables
that we are importing.

However, there is one external table (Product info) that we simply want to
refresh all of the rows each day. Currently we use one query to delete all
old rows and a second query to append all new rows. Perhaps this is the best
way to do this, I was just wondering if there was a way to do this in one
step instead of two.

Thanks again,
Brad
 
Back
Top