carmenlisa
Technical User
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 field.
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 process.
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.
Robert
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 field.
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 process.
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.
Robert
