Converting Northwind 2007 to SQL2005 Database

Question

I have recently downloaded the Access 2007 Northwind example database and would now like to convert the database into a SQL 2005 database. I am using the Developer Edition of SQL 2005 and not SQL Express.

I installed the Office 12.0 Access Database Engine OLE DB Provider drivers for working with Access 2007.

So far I have 'created' a new database, gone to 'tasks' > 'import data'.

At this time, the SQL Server Import and Export Wizard dialog shows, I select the 'data source' as Microsoft Access and have browsed to the file which I then select. I click next and now SQL Server displays the following error:

Unrecognized database format Northwind2007.accdb.

Has anyone successfully imported this database into SQL? Also, does anyone know what the differeces are of the .accdb file format and the Access 2003 .mdb format?

Thanks for your input.

Answer 1

Is there a reason you aren't just downloading and installing the Northwind database for SQL 2000? As stated it works fine with SQL 2005, but Microsoft recommends using the Adventure Works database instead.

Answer 2

one of the main differences between Access 2007 and previous Access databases is related to the multiset columns, as explained here. Instead of importing your database from SSMS, why don't you try to export it through the ms-access upgrade wizard? There are also some (not always free?) upgrade tools available on the web.

Answer 3

The problem is that the 'Microsoft Access' option Import Wizard is hard coded to expect a file with an .mdb extension. It is the same problem for SQL Server 2008; opening a file of type .accdb fails with, "There is not editor available for..."

One work around is to use OLE DB. In the wizard's drop down list of data sources, choose 'Microsoft Office 12.0 Access Database Engine OLE DB Provider'. You may need to edit the connection properties to enter Jet OLEDB:Engine Type=5 in order to be able to 'see' functionality specific to the .accdb format. However, quite what SQL Server will make of so-called 'complex' (multivalued) data types I don't know!