This post covers generating an Excel spreadsheet from an Access Database but using a SQL Server tool. It's crazy but great and easy!
First up open the Import and Export Data application, note this has to be the x86 edition so if you are running an x64 machine, which I think most dev's are nowadays, ensure you choose the correct edition.
This will give you the welcome screen.
Next up the destination, well again Excel is what we want so highlight in the list and provide a path to save too. You can also choose the version of Excel you want the file format to be, lots of options which is great.
Now upon clicking Next here two things may happen, you might proceed to the data copy screen or your might get an error. On my machine I got an error however on others I did not. The error was: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data)
|Copy or Query|
Any how, with all that sorted you will be at the copy or query screen, if you simply want all your data out of your database in the form it is stored you could use the copy table option. However I wanted to pull data from several tables and output it into a specific format so I chose the query option.
|What a query|
It is worth noting here that I believe there is a bug with this window, if you browse and load a file I was finding that only half of my query was in the window, however if I copy pasted it worked fine.
The next window asks you to confirm the tables and views to copy, when you are using a custom query there isn't a lot more you can do on this screen so click next again.
This next screen, Run Package, gives you the option to run the export now or to save it as a package that can be reused or used later. However you can only do this on SQL Server Standard, Enterprise, Developer or Evaluation. If you have SQL Server Express, Web, or Workgroup you can only run it immediately.
Cracking stuff, if you haven't before you should take some time looking at the SQL Import and Export tool and DTS more fully, it allows you to import and export data from a vast array of sources into just as many again. I have used it to take old Access DB's and create SQL ones and managing data migrations, it's a tool that should be in every developers belt. We don't need to continually write console or windows form apps for managing data we already have a tool that will do the majority of use cases, we just have to use it and write the queries!
I hope this helps, I know it saved me a ton of time :)