Now don't laugh too loudly but I really do have an access database with 648 tables in it - all with identical structure and different table names (obviously)
The story so far:
I had 200 or so old xls files with multiple sheets and wanted to get these in to access. Had the code to get single worksheet spreadsheets in but not multiple worksheet ones. I ended up using SQL server 2000 DTS to import the spreadsheets in to a SQL database. Hence the now 648 tables.
I've imported the tables in to access (as thats what my users are familiar with) and now need one big table - not hundreds of small ones.
After reading on this forum I'm beginning to think that my only option is to perform 648 append queries in to a master table and then manually get rid of the odd null row etc. This is a hell of a job though and as I can see myself having to do this again in the future any ideas on semi/automation would be VERY VERY much appreciated.
The story so far:
I had 200 or so old xls files with multiple sheets and wanted to get these in to access. Had the code to get single worksheet spreadsheets in but not multiple worksheet ones. I ended up using SQL server 2000 DTS to import the spreadsheets in to a SQL database. Hence the now 648 tables.
I've imported the tables in to access (as thats what my users are familiar with) and now need one big table - not hundreds of small ones.
After reading on this forum I'm beginning to think that my only option is to perform 648 append queries in to a master table and then manually get rid of the odd null row etc. This is a hell of a job though and as I can see myself having to do this again in the future any ideas on semi/automation would be VERY VERY much appreciated.
