Zuzana,
On e thing I can tell you right off is you will need to use some VBA
code to do the job. Now what that might look like, is subject to the
answers to a few questions. To begin with:
* Your 150 spreadsheets eventually end up - or will - in one table,
which makes sense. Do they all have the same format, or are there
differences?
* Do the spreadsheets coming in periodically contain new records only,
or will there be updates to existing records as well?
Depending on the answers to these two, there may be more questions.
In theory, you could have used any of a number of schemes to import,
like (a) use TransferSpreadsheet to import in a temp table, then append
to the main table - one at a time, or (b) link and append one at a time,
either directly in the main table or through an intermediate, or (c)
import each in a separate Access table, or ... you opted for (c), which
is the last thing I would have done. Is there a good reason for this?
Assuming all the spreadsheets have the same format and are stored in one
folder, my approach would be to use a VBA loop to link each spreadsheet
in the folder in turn, import and move on to the next, rather than have
150 links or, even worse, 150 tables. How does that sound?
Also, once consolidated in an overall table, I suppose you would need
some way to know which spreadsheet each record came from (site code?
city name? sales rep name?....). Is there a column already in the
spreadsheets to that end, or should that be derived from the file name?
In the latter case, are the files named systematically?
Nikos
