From excel to access

B

bagnallc

Hi all,

Some advice please

Im currently setting up a database using a large amount of info (about
5 years worth of daily records) stored in excel, once its transferred
to Access i will then be breaking it down into tables (fifth form) and
running all sorts of queries etc.

Each future day though i need to transfer that days data into the same
Access database and tables etc so it is always up to date.

The data has to come from excel as i run a series of macros containing
formulaes etc there before it is correctly broken down.

What is the best way to set it up to transfer the info from excel over
to access each day?

Many thanks if anyone can help

Chris
 
S

Steve Schapel

Chris,

In very general terms, the concept here would be to use the
TransferSpreadsheet method to import the daily Excel data into an Access
table, sort of a temporary "holding" table if you will, and then use a
VBA procedure to run a series of Append Queries, and Update Queries, as
applicable, to move this imported data into the normalised structure of
the main tables. Will that work?
 
N

nanne

Hi

I use the following method, which is working well.
The fieldnames in Access are exact the same as the fieldnames in Excel. they
are in the first row of the spreadsheet.
Then I use GET EXTERNAL DATE-> Import
and select the Access-table to update.
very easy
success

Nanne
..
 
S

Steve Schapel

Nanne,

I would suspect Chris was really asking about a way to automate this
process.
 
Top