SQL Server 2012 Express connection to MS Access 2010

Question

I'm a newby with SQL. I am trying to connect sql server 2012 express (local) to a project on ms acces 2010 using the wizard but it is giving me this error.

Connection failed: SQL State:'01000' SWL Server Error: 2 [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen(connect()). Connection failed: SQL State:'08001' SQL Server Error:17 [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.

I'm wondering if somebody can give me a hand on these please. Many thanks in advance.

OS: windows 7 (32 bit)

Jhun

Answer 1

By default, SQL Server Express installs itself as a SQL Server instance named SQLEXPRESS. In those cases when specifying the SQL Server for the ODBC DSN you need to use (local)\SQLEXPRESS, not just (local).

Example: After selecting External Data > ODBC Database from the Access ribbon you choose "New" on the Select Data Source dialog

After selecting the SQL Server driver if you use the drop-down list and simply select (local)...

...then the connection will fail. However, if you manually add the \SQLEXPRESS instance name then the connection should succeed