We have people in the field collecting data about buildings into multi-tab spreadsheets. (between 10 and 20 tabs total). We
then want to copy this data into Access. I might try to do this a bit differently if given a choice, but I have no option
but to have data collected in Excel and for Access to the the ultimate repository for it all.
Thus far I have put the worksheets into a layout that Access can recognize and created the tables in Access that will match
the worksheets in Excel. The data is reasonably well normalized among the Excel Worksheets. (This is a too long story that
i will wpare you reading).
My plan is to use append queries to copy the data from the worksheets into the Access tables. To do this, I believe I need
to either import the worksheets into Access as tables or to link the worksheets.
With the option comes the quandary. There seems to be goods and bads of both options and I would love to hear from people
who have done both or either of these methods. I have used both in the past for more limited scope tasks.
At first I liked linking. Once I have all of the Excel tabs linked, I can run the append queries. Then replace that
spreadsheet with the next one, update the links and run the queries again. But then come the details.
1. I don't want people to have to manually update over a dozen links with every new spreadsheet coming in from the
2. Using the Import Data process in Access 2007, when setting up links, does not allow you to save the process to re-run it
if you want to. Thus apparently requiring updating existing links. (That is, if I run the first spreadsheet with links, run
my append queries, and then want to go to the next workbook, I can rename the first workbook (to break the links), then give
my second workbook the same file name as when I set up the links and update the links so they now show the data from the
second workbook. Then run the append queries again to get this second set of data into Access. And continue with however
many workbooks we receive. The total number of these will be in the dozens.
3. So my problems with linking seem to involve the repeated renaming of spreadsheets and updating of many links to update
the data in the linked tables in Access.
So I looked at importing worksheets as tables. Importing, not linking. Again, details.
1. In this case I can set a name for each import and presumably set up a macro that would run each of the imports, one
after another. Is this true? I have not used macros in Access. Will they run these imports?
2. It would seem that after I run the append queries, I can set up a macro, or series of macros that will then delete out
these imported tables, setting the stage to import them again from the next workbook to be processed.
3. So my problems here are whether I can use a macro to automate these processes (importing and later deleting the tables
that are imported from Excel) and, again, the repeated naming and renaming of workbooks before each new running of the
And I just bet that there are issues that will arise that I have not thought about. I would surely appreciate all helpful
comments about the two processes envisioned above and any other issues I may be missing.
Thank you in advance.