Combining multiple tables of similar type

[johnctholen]

Hi everyone,

I have a database which is importing several Excel workbooks, each with multiple worksheets. Every workbook has 20 worksheets, with the same 20 worksheet names. When they are imported they come in as one table for each worksheet, named tblWorkSheetName_X with X starting at 1 and increasing for each worksheet brought in with the same name. So if the worksheet names are A-T I have tblA_1 through tblA_6 and likewise for B - T.

I would like to combine all of the tables which come from similar worksheets into one table (one table per name).

I.e. I want to combine the data in tblA_1 through tblA_6 into a singular tbl_A and likewise for tables B through T. So in the end I will have one table for each worksheet name A-T. Does anybody think they can figure out how to code this successfully? This is beyond me but I would appreciate any help!


Thanks for reading!

 

[Thales750]

Are these spread sheet updated periodically?

 

[johnctholen]

Yes. They are extracts from a larger database that represent different periods of time. So the 6 workbooks might be months January - June for example. So in the future there will be more workbooks, and the time increment might be different. Workbook 1 might not always be just January.

 

[Thales750]

If they always have the same name I would link to the spreadsheet in a folder overwrite the existing ones every time a new one is produces.

Then all you have to do is write an append query that saves all the new records to an existing Access Table.

If they have a new name, than I would change the name and still overwrite the existing linked Excel spreadsheets.

Just remember you will have to close Access before overwriting the spreadsheets.

 

[johnctholen]

I am not sure I understand what you mean.
I already have a way to get the data into Access as separate tables.
What I need is a way to combine the Access tables of similar type. And likely do so using VBA so someone not techincally inclined can perform this.

 

[Thales750]

The reason I mention linking the spreadsheet instead of importing, is that append query you design to insert (append) the rows from the spreadsheet, as records into the database tables ,will continue to work with new spread sheet without having to reimport them.

As far as the queries are concerned they are standard append queries.

You will need to make some tables with the proper structure.

Using a wizard you should be able to make the append queries. Also using wizards you can create buttons that will execute queries.

Remember to add an AutoNumber Primary Key I would also add a time stamp.

 

[johnctholen]

If I link them will the user of my Access DB also have to have the excel files on their computer or some shared drive?

Also the names of the excel files will changes each time. A timestamp is part of the filename.

 

[Thales750]

You can, the back end will contain the access table. There will be no difference between linked and imported except the linked ones can be permanently set up.

How much experience do you have with Access?

 

[Thales750]

Necessary Steps:

Split the Database
Duplicate Front ends on every user's PC
One backend in a networked drive with a drive mapped to it on all the user machines with the same mapped drive Letter.

Link the spreadsheet to a single local machine or to the networked backend. I always link to a local machine, whichever is the machine that is for the user responsible for importing the data.

Run a series of append queries against all of the linked worksheets.

Your code will also be a series of Docnd openquery actions

Let me know which part you follow and which part need examples

 

[Thales750]

Next time you run it, change the name of the spreadsheet to not include the date modifier and overwrite the exsting spreadsheet, open the local machine and run the append query command again.

 

[vbaInet]

Well you'll first need to see if it's possible to merge the sheets within Excel as one. If it is possible, merge them within Excel before importing into Access. One UPDATE query is better than 20. Merging within Excel will be quicker.

 

[Thales750]

I'm not sure that would be in your best interest, changing the Spreadsheet.

The least amount of human intervention the better.

From an automation point of view I would run Allen Browne's "File List" routine. Get the name of the spreadsheet and use VBA to rename them automatically. open the file append database and run the queries on an On Open event.

http://allenbrowne.com/ser-59.html

I would have the link to the spread sheet linked to the very folder they are deposited in from the other database.

 

[vbaInet]

I'm not sure that would be in your best interest, changing the Spreadsheet.

The least amount of human intervention the better.

I don't recall mentioning manually merging the sheets.

 

[Thales750]

How would you go about merging them every time they produce a new one.

 

[vbaInet]

Through automation.

 

[Thales750]

I would stick with the queries It would accomplish the same results with less effort. Always a good path.

 

[Thales750]

Anyway It looks like we don't have John around any more.

John, in the last year I have developed two commercial programs that do exactly what you are trying to do, Post here for further instruction if you are still interested.

 

[vbaInet]

Just because it's easy doesn't mean it's the most efficient way. I never compare which method is best, I consider latency and timed performance. Everything has to be timed.

If you run a query performance frequency timer on the update and do the same for a copy and paste method within Excel, you'll find that uploading a consolidated sheet is quicker than doing the same for multiple sheets. Linked or not, it makes no difference. Both methods still open a recordset through DAO (one through ODBC) all the same.

These are suggestions so it's up to the OP to research the methods proposed and choose whichever way s/he wills.

 

[Thales750]

About Excel Automation

If the program outputting the original Excel spread sheet is adding a row at the top and then a row with headings, how would you automate deleting the first row and then linking the spreadsheet to your database?

 

[Thales750]

This seems to be something I continue to work with , so along the lines of vbaInet's responses to combining Excel Automation, I thought we could explore that a little bit more.

AutoCAD Architecture Schedules export with an extra title, when importing them into Access the main title must be removed.

That's the basis of the previous question, Thanks all.