Updating Multiple Access Tables with Single Excel File

  • Thread starter Thread starter Ken
  • Start date Start date
K

Ken

Help!

I have a MS Access database made up of a single table. The data updates for
this database comes in the form of a single Excel spreadsheet, which is
imported into the database table.

I need to break-out this single database table, into multiple tables to
better structure the database and reduce the need to input the same data
repeatedly.

What I need to know is, after I break this database up into multiple tables,
will I still be able to update the data in these tables, from the same
single excel file?

If the answer is yes, how would this be accomplished?

Thank you for your help.

//Ken
 
yes, but... you will have to do more work of course. the question is on
which side of the fence to do it.

1. write vba code in excel to take the data out of the sheet and push it
into the database into the proper tables... i don't like this for a variety
of reasons... mostly that it could be lots of work and any change to the
access tables would require changes to the excel code.
2. leave the existing table format in access as a temporary place for new
incoming data to go, then use vba and/or some update queries to extract the
data from that one table and move it into the new data structures. this is
a bit better because it leaves the working interface in place and doesn't
change the excel stuff while providing you the flexibility to reorganize the
data as needed in access.

as a variation on #2 you could have the queries in access go read the excel
sheet directly and import into the new tables, though this would again lock
the structure of the excel sheets together with the code/queries in access.

another variation would be to create a new database with just the new tables
and link a table to the old one to run queries against to populate the new
structure. this is possibly the cleanest way to do it since it leaves the
excel, the current access file, and all their interfaces intact while
creating a new access database with the desired table structures.
 
Ken said:
Help!

I have a MS Access database made up of a single table. The data updates
for
this database comes in the form of a single Excel spreadsheet, which is
imported into the database table.

I need to break-out this single database table, into multiple tables to
better structure the database and reduce the need to input the same data
repeatedly.

What I need to know is, after I break this database up into multiple
tables,
will I still be able to update the data in these tables, from the same
single excel file?

If the answer is yes, how would this be accomplished?

Thank you for your help.

//Ken
Click to expand...
 
Back
Top