Upsizing to SQL Server 2005

[doco]

I have Access 2007 running on an XP Pro machine. I have a SQL Server 2008 running on a MS Server 2008 R2 Box. However the databases are SQL 2005.

When using the SQL Server Upsizing Wizard I get the following error message:

[DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist or access denied

The Upsizing Wizard does not work with the version of Microsoft SQL Server to which you Access project is connected. ...

HUH?

I have property permissions, DSN and the server indeed exists.

TIA

 

[bparkinson]

I think you need to upgrade the database to SQL 2008, or downgrade the Access version.

 

[doco]

I think you need to upgrade the database to SQL 2008, or downgrade the Access version.

Downgrade the Access version, you mean like go back to 2003? Hmmm...

We have 2008 but running 2005 database with it. I don't have a choice about that...

 

[bparkinson]

Downgrade the Access version, you mean like go back to 2003? Hmmm...

We have 2008 but running 2005 database with it. I don't have a choice about that...

All is not lost. Rather than downgrade your Access version, just don't use the upsizing wizard. Instead, import your Access tables using SQL Server Management Studio (Right click the database, then Tasks, then Import Data). You'll get a wizard that will let you import all your Access tables, structure and data. Then you'll have to link them manually, but that's easy. I think this will work, unless your version of Access also cannot link already existing SQL Server tables.

If this is an ongoing thing, importing data and/or structure from Access or elsewhere, build a SQL Server Integration Services package and have SQL run it as needed.