Error migrating from Access 2007 database to 64-bit SQL Server 2008

Question

I am using 64-bit Vista and I need to migrate an access database, in Office 2007 to SQL Server.

I don't want to have to write an SSIS package to do this, but that may be my only option.

Is there any other way to do this? I have about 330M of data in Access that needs to be moved, so I can write a webapp to replace this database.

Update: The error is: The upsizing Wizard does not work with the version of Microsoft SQL Server to which your Access project is connected. See the Microsoft Office Update Web site for the latest information and downloads.

Answer 1

In Access, use the upsizing wizard under tools menu...

Edit, checked to see if it's still there: it has issues with SQL Server 2008. Do you get the same error? (yes you do, says your edit)

Alternative: SSMS/Migration for Microsoft Access

Answer 2

The SQL Server 2008 Import/Export tool has worked great for me for migrating tables as well as data. You may need to install the SQL Management Studio to get the import tool also (it'll show up in your Start menu). It supports moving tables/data around from various sources, and I'm guessing that Access 2007 is supported.

That said, the SSMS/Migration for Microsoft Access tool may work the best overall.

Best of luck.

Answer 3

See if creating a Linked Table inside Access DB (which points to your destination table in SQL Server) helps.

OR

See if you can use SQL Server functions (such as OPENROWSET) to open the Access tables.

Answer 4

When all else fails, you could try creating an ODBC DSN for your SQL Server and then export the tables to that DSN. It won't get all the datatypes right, but it might be good enough to get things started if nothing else works.

I think it's pretty amazing that you can export a Jet/ACE table to an ODBC data source, to be honest.