Dynamic Connection between Excel Spreadsheets and Access Database

[Guest]

All,
I have a very important question. I have a database that I would like to
update monthly based on changes made in Excel Spreadsheets. There are around
12 different Excel Spreadsheets, one for each Partnership. In the Partnership
spreadsheets there are multiple worksheets (one for each month). The first
worksheet gets updated by the month you pick and it summarizes the monthly
distribution. The only bad thing is that you have to go through and pick a
new investor to print out a report. What I want is to build a report in
Access and have it be run every month and print out a report for each
investor. The thing that I need is to connect the Access Database with the
Excel Spreadsheet. Where each record in the Access Database would reference a
a field in the Excel Spreadsheet, and be appended to a table. Is there anyway
to make a connection in the Access database? Or is this just something that
would be easier to solve by copying over the values by hand into Access. I
just wanted to leave out the Human error aspect.
Thanks

 

[Guest]

You can link to an Excel spreadsheet as it it were table provided it has
contiguous rows and columns and column header names. So to produce a report
based on the spreadsheet data:
Link to the worksheet.
run the report.
Drop the link to the spreadsheet using the DeleteObject method.