none
VBA ODBC logins to SQL databases have stopped working for some users RRS feed

  • Question

  • We have developed several excel spreadsheets that log into a SQL server database and extract information for our company.  We are all on the same internal domain.  These spreadsheets worked for everyone until a few weeks ago.  The following code is an example used in all spreadsheets:

    '
    ' ======================================================================================================
    '           Connect to QUERY database
    ' ======================================================================================================
    '
    Sub LogIntoDB()
        LoginFailed = False
        On Error GoTo NotConnected
       
        luserid = "userid"
        lsqlpassword = "P@zzw0rd"
        lintercompanyid = "sqlDatabase"
        lsqldatasourcename = "ODBC"
        '
        ' Create the ADO_connection object and then open it
        '
        With ADO_con
            .ConnectionString = "DSN=" + lsqldatasourcename + _
                                ";UID=" + luserid + _
                                ";PWD=" + lsqlpassword + _
                                ";DATABASE=" + lintercompanyid
            .CursorLocation = adUseClient
            .Open
        End With

        ADO_cmd.ActiveConnection = ADO_con
        ADO_cmd.CommandType = adCmdText
        On Error GoTo 0
        Exit Sub
    NotConnected:
        LoginFailed = True
        MsgBox "    Data Source=" + lsqldatasourcename + vbLf _
             + "            UID=" + luserid + vbLf _
             + "            PWD=" + lsqlpassword + vbLf _
             + "Initial Catalog=" + lintercompanyid _
             , vbCritical, "Login to CW failed"

        On Error GoTo 0
        End
    End Sub

    ' ======================================================================================================
    As mentioned, this worked for everyone but now many cannot run this but other can.

    On each workstation we opened the ODBC manager and selected the 'ODBC' connection. We entered the same ID and Password and then tested the connection and IT WORKED.

    However, it no longer works in the spreadsheet.  References are properly set in the VBA code.

    For all those that upgraded to Windows 10 it fails.

    We are all using Office 2010 Professional.

     

    ANY SUGGESTIONS WILL BE GREATLY APPRECIATED

    Tuesday, August 9, 2016 7:08 PM

All replies

  • Random ideas.  I am guessing here.  There are 2 drivers for SQL Server at

    %WINDIR%\system32\sqlsrv32.dll - 64 bit

    %WINDIR%\SysWoW64\sqlsrv32.dll - 32 bit

    I am thinking maybe it is getting confused on  32-bit versus 64-bit.

    Check the registry.

    HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\....

    Recreate the DSN.  Be sure to use the ODBC Administrator tool in %windir%\SysWOW64\odbcad32.exe

    Maybe compare the registry on Win 10 machines versus Win 7.

    Wednesday, August 10, 2016 2:30 AM
  • Thanks mogulman52, I will try this.

    Bill

    Wednesday, August 10, 2016 1:51 PM
  • Thinking about it the ODBC Administrator tool in %windir%\SysWOW64\odbcad32.exe is the 32-bit version.  The one that shows in the Admin tools is 64 bit.  I think it is entirely possible the upgrade picked up the 64 bit version.  A fresh install of Windows shows both a 32 and 64 version in Admin tools.
    Wednesday, August 10, 2016 3:22 PM
  • I checked regedit as suggested and the ODBC connection appears to be using the driver %WINDIR%\system32\sqlsrv32.dll.

    The ODBC is a 64 bit ODBC

    Excel is 64 bit

    The SQL database has a user with read only access.  Is there a way for VBA to make a connection without using ODBC?

    Wednesday, August 10, 2016 9:02 PM
  • So it works on Win7, Excel 64-bit and ODBC 64-bit but not Win10, Excel 64-bit and ODBC 64-bit.  If so, I have no clue.  I've avoided Office 64-bit due to all the VBA issues.  I use ADODB.  Example

         Dim MyConnObj As New ADODB.Connection 'ADODB Connection Object

            Dim myRecSet As New ADODB.Recordset 'Recordset Object
            Dim sqlStr As String ' String variable to store sql command

     
            MyConnObj.Open _
                "Provider = sqloledb;" & _
                "Data Source=172.16.1.60;" & _
                "Initial Catalog=TESTATV;" & _
                "User ID=sa;" & _
                "Password=p@ssW0rd;"

             sqlStr = "select * from employee"
            myRecSet.Open sqlStr, MyConnObj, adOpenKeyset
            MsgBox "Total Number of records = " & myRecSet.RecordCount

    Thursday, August 11, 2016 1:26 AM
  • Thanks mogulman52, I will try this form of connection. 
    Thursday, August 11, 2016 9:10 PM