I never use Access 2007 - until today.
I want to connect to an existing SQL Server 2008 database. I have tried using:
I have done some searching and only found others with same issue. About to do some more... but hoping one of you has the answer OTTOYH. Thanks.
What error do you get when trying to link to the ODBC connection? Just to confirm, you are selecting ODBC Database from the Import part of the 'External Data' ribbon? If you test your ODBC conneciton does it pass?
The ODBC connection is fine, db fine etc. Looks like this is a "Access 2007 doesn't know anything about SQL 2008" sort of a problem.
UPDATE: It now works for external db! I closed Access. Re-opened Access. Rather than creating a new DSN, I pointed at the DSN I created last time around (avoiding the wizard). Worked fine.
The DSN contained:
[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 though :-)
If you create a DSN that points to the SQL Server, can you not then create linked tables via ODBC that use the DSN? If not, then there's something wrong with the DSN, seems to me.
Perhaps you could outline the exact steps you are taking in order to connect to SQL Server 2008 (including any errors you get), instead of just reporting what went wrong.
The upsizing wizard in Access 2007 is not "aware" of SQL Server 2008 and therefore won't connect. You should use the "SQL Server migration assistant for Access" tool that is provided with SQL Server 2008. The link is : http://www.microsoft.com/sqlserver/2008/en/us/migration.aspx. Hope that helps