Moving data from a retiring MS SQL server to MS Access

The data in the MS SQL DB is about 10 years old and has been in read only mode for about 6 months.  The time has come for use to take the DB server off line.

The question I considered was what to do with the DB and my customer, who still thinks they need access to it - read access.

I thought initially, can I simply back the databases (there are actually two on the server) up and give to my customer and they could access them from their SQL Server Management Studio.

We installed this software for them so they could remote into their new system db (3rd party commercial vendor) - but my colleague Gary said "no James, not unless they have the DB engine installed on their computer.  Studio won't have that."  After a bit more conversation I realized Gary was correct - they do not have the DB engine installed - why would they?  I missed that in my own thinking.  I thought they have studio - they are accessing this other MS SQL DB server.  But - that other DB server has a DB engine installed.

Moving on.

My other colleague Mike said you could export the DBs to a data file or even to MS access. The data file was the lower hanging fruit - so I did that.  I exported each DB to its own spreadsheet.  Each table from the DB exported to a tab in the spreadsheet.

That worked pretty well.  Simple too.  From SQL Server 2008 Management Studio - I simply chose the DB to export and chose Tasks - Export Data and followed the wizard - choosing the source DB and then XLS as the output format.

When my customer saw this, he was marginally happy, but predictably he asked if we could export the data to Access.  Probably MJ suggested it.

Back to SQL Server 2008 Management Studio i went - following the same steps but this time choosing MS Access as the destination.

A little more exporting to MS access.  I tried creating a new empty access DB to export to, but the wizard would not see it?  Odd.  I ended up choosing an existing DB that was old and worthless to me and killings its content and then exporting the new to it.

The other thing the export lost was the table relationships - which could really be problematic if you did not have knowledge of or ability to re-create those in access.

Once the export was successful, I was able to open the Access DB, go to Database Tools - Relationships - and re-establish the table relationships.