I’ve done quite a bit of MDB upsizing to SQL Server in the past. And it works just like a snap - you go to Tools -> Database Utilities -> Upsizing Wizard, choose your destination server, couple of clicks, bang - you are done! So, when I needed to import a MS Access 2007 database into SQL 2008 last night I didn’t expect to spend more that 5 minutes… And ended up spending over an hour… So, what’s the big deal?
The trouble is that not that in Access 2007 the route is Database Tools -> Move Data - SQL Server. Nope, that would be too easy, wouldn’t it? The challenge is that the wizard connecting to SQL 2008 give this error message:
Searching for “The Upsizing Wizard only works with Microsoft SQL Server (Versions 6.50 SP5 or higher). Please log in to a SQL Server data source.” error led me to MS KB838594 article which was no help at all as the workaround they posted is for Access 2007 in general not when uploading to SQL 2008 in particular.
After some research I’ve come to realize that Access 2007 Upsizing Wizard is not aware of the SQL Server version 2008 is treating it as “pre- v6.5”. Bummer!
The solution I found is to use SQL Server Migration Assistant for Access - download version for SQL 2008 here.
The Migration Assistant tool is a better choice anyway - in addition to exporting Access tables to SQL, it allows to choose which tables you want and saves your configuration for future synchronizations. Sweet!
Office 2007 image credit - Access-Freak.com