Exporting all tables in access database to excel at once

[thinair421]

I have a database with roughly 100 tables in it, and I would like to essentially "back-up" all of the data in the tables by exporting them to excel spreadsheets (one spreadsheet per table). I know I could manually export each table one by one, but this is not really what I was goin for. Anyone have any ideas as to how I could go about exporting multiple tables to .xls files? Thanks in advance.

 

[Trevor G]

I have a database with roughly 100 tables in it, and I would like to essentially "back-up" all of the data in the tables by exporting them to excel spreadsheets (one spreadsheet per table). I know I could manually export each table one by one, but this is not really what I was goin for. Anyone have any ideas as to how I could go about exporting multiple tables to .xls files? Thanks in advance.

If you need to do this ! Then look at MACROS, in there you have options to use TransferSpreadsheet or Also TransferDatabase

You will be able to add multiple arguments for each table.

If you are on a network can't your network administrator backup your database?

 

[ghudson]

Check out my Export All Database Objects Into Text Files code. Create a loop to go through the tables collection and use my code or the transferspreadsheet option to output the tables. I would prefer to backup the entire database since any database object can get corrupted just as easily as the table data.

Check out the code I posted in the Back up On exit thread if you want to backup the entire database file. Somebody else started a new thread Back up and zip code from ghudson based on my backup code.

 

[thinair421]

Thanks for the replies guys... I have sort of rethought this whole process. The reason behind doing this was so that after a year, we could store all of the data in a yearly folder (i.e. Data 2010) and basically restart the program. I am starting to think that maybe just saving a "backup copy" of the database at the end of each year, and then erasing all of the data in the original and using that as the "new yearly database" will be a better option. Good lookin out, and thanks again for the help.

 

[ghudson]

100+ tables makes me question if the data tables structure is properly normalized. Ensure you keep a zipped copy of the database somewhere elese to ensure nobody makes any data changes. Just opening a database changes the modified date property of the database file.

 

[jeniferlee]

Hey
you can easily export all tables of access database to excel sheet in efficient manner without any data loss. You can use Systools Access To Excel software which is designed with perfect technologies and easily export access table to excel sheets without affecting originality of database.