Refreshing Data Daily From a Linked Excel Spreadsheet

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
 
P

Phil Smith

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
 
K

KARL DEWEY

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.
 
B

Brad

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top