Clever Geek Handbook

Does anyone know the magic for connecting Access 2007 to SQL Server 2008?

I never use Access 2007 - until today.

I want to connect to an existing SQL Server 2008 database. I've tried using:

  • ODBC option with external data - but get DSN error
  • Upgrade wizard with ODBC - get an error
  • SNAC Upgrade Wizard - Get ODBC error. So it seems like there is no starter :-)

I did a few searches and only found those with the same problem. About making a few more ... but hoping one of you has an OTTO answer. Thank.

+1


source to share


4 answers


The Upgrade Wizard in Access 2007 is unaware of SQL Server 2008 and therefore will not connect. You must use the SQL Server Migration Assistant for Access that is provided with SQL Server 2008.



+4


source


What error occurs when trying to reference an ODBC connection? Just to confirm, are you choosing an ODBC database from the Import part of the External Data ribbon? If you test the ODBC connection, does it pass?



0


source


ODBC connection is fine, db fine, etc. It appears to be a "Access 2007 knows nothing about SQL 2008" problem.

UPDATE: now it works for external db! I closed access. Reopen access. Instead of creating a new DSN, I pointed to the DSN I last created (avoiding the wizard). Fine.

The DSN contains:

[ODBC]
DRIVER=SQL Server
UID=ericnel
DATABASE=AccessTest
WSID=ERICNEL1
APP=2007 Microsoft Office system
Trusted_Connection=Yes
SERVER=ericnel1
Description=test

The Upsizing Wizard still thinks SQL 2008 is not SQL Server :-)

0


source


If you create a DSN that points to SQL Server, can you then create linked tables via ODBC that use the DSN? If not, then something is wrong with the DSN, I think.

Perhaps you could outline the exact steps you are taking to connect to SQL Server 2008 (including any errors you receive), rather than just reporting what went wrong.

0


source






More articles:


All Articles