locked
MS Access 2007 connect to SQL Server Express 2012 RRS feed

  • Question

  • I am trying to connect to SQL Server Express 2012 from Access 2007. 

    Both programs are running on the same machine,

    there is a system DSN data source set up,

    SSExpress  is running

    Excel can connect with no problem

    SS Management Studio connects, visual studio connects

    Access will not connect.  "No connection could be made because the target machine actively refused it."


    I have scoured the web for solutions, none have worked so far.  Did all pertinent updates from MS, still no change.

    Thanks

    • Moved by ArthurZ Monday, September 23, 2013 6:07 PM
    Monday, September 23, 2013 5:51 PM

Answers

  • F.Y.I.:  If you need to create DSN to work with 32-bit application, use the odbcad32.exe (ODBC DSN Administrator) in the folder Windows\SysWOW64 to create the (system) DSN and your 32-bit Access will be able to use this DSN.

     


    Van Dinh

    Wednesday, September 25, 2013 10:45 PM

All replies

  • 1.  Did you test the DSN to make sure the connection succeeds when you set up the DSN?

    2.  What did you do when you wrote "Access will not connect"?  Are you using MDB/ACCDB or ADP?

     


    Van Dinh

    Monday, September 23, 2013 10:46 PM
  • Yes.  DSN tested fine.  Excel connects using this DSN.

    Visual studio connects using .net data provider

    I am using accdb.  In Access I went to External Data and tried to establish a connection for a linked table.  The only choice available was through the ODBC machine data source (DSN).  An error is returned saying "No connection could be made because the target machine actively refused it." from [SQL Server Native Client 11]TCP Provider

    Upon closing the error box, a SQL Server Login dialog box appears.  After filling it in with the appropriate name, and hitting OK, a different error appears "Shared Memory Provider: The Shared Memory dll used to connect to SQL Server 2000 was not found [126]."

    I have set up SQL Server for shared memory, which is supposed to work when the client program is running on the same machine.  Named pipes and TCP/IP are disabled.   I tried both of those options also, but got nowhere, also receiving errors.

    Thanks for your help.

    Mel

    Tuesday, September 24, 2013 3:50 PM
  • Connection has finally been made.   One more try with the Access wizard, instead of using the system DSN, I selected "New" and set up a file DSN.  I have no idea why that works rather than the system DSN, but it does.

    Mel

    Tuesday, September 24, 2013 4:16 PM
  • Is your Windows operating system 32-bit or 64-bit?

    If it is 64-bit, the old DSN was set up for 64-bit and Access 2007 (32-bit application) cannot use the DSN for 64-bit.

     


    Van Dinh

    Tuesday, September 24, 2013 10:53 PM
  • That would explain it.   Yes, my system is 64bit

    Thanks,

    Mel

    Wednesday, September 25, 2013 3:18 PM
  • F.Y.I.:  If you need to create DSN to work with 32-bit application, use the odbcad32.exe (ODBC DSN Administrator) in the folder Windows\SysWOW64 to create the (system) DSN and your 32-bit Access will be able to use this DSN.

     


    Van Dinh

    Wednesday, September 25, 2013 10:45 PM