ADO Connection from Excel to SQL Server

JezLisle · #1 (**permalink**) 09-12-08, 07:13

I am trying to connect via ADO to an SQL Server database, the code below is what I'm using as the connection but keep getting an error message...

The following error has occured-
[Microsoft][ODBC SQL Server Driver][SQL Server] loging failed for user 'myusername'

I dont understand why this would be, I have created an ODBC Driver. The only difference is that the Username and Password are taken from the authenticity of the Network Login, but I have used these when putting in the code. I dont know where am going wrong...

Code:

Sub GetData()

    On Error GoTo Err:
   
    strDWFilePath = "Driver={SQL Server};Server=SQL1;Database=INFO;User Id=mynetworkusername;Password=mynetworkpassword"
    Set cnnDW = New ADODB.Connection
    Set rsDW = New ADODB.Recordset
    
    Sheet1.Range("B4:C9").ClearContents
    
    cnnDW.Open strDWFilePath
           
    sQRY = "SELECT     TOP (100) PERCENT [Weeks Range Title] AS WeeksWaiting, COUNT(*) AS Count " & _
            "FROM jez.PodAll " & _
            "GROUP BY [Weeks Range], [Weeks Range Title] " & _
            "ORDER BY [Weeks Range]"
    rsDW.CursorLocation = adUseClient
    rsDW.Open sQRY, cnnDW, adOpenDynamic, adLockOptimistic, adCmdText
    Application.ScreenUpdating = False
    Sheet1.Range("B4").CopyFromRecordset rsDW
    rsDW.Close
    Set rsDW = Nothing
    
    cnnDW.Close
    Set cnnDW = Nothing
       
    Exit Sub
    
Err:
    MsgBox "The following error has occured-" & vbCrLf & vbCrLf & VBA.Error, vbCritical, "SQL Connection"
    MsgBox VBA.Err
End Sub

gvee · #2 (**permalink**) 09-12-08, 07:18

Sounds to me like you're using a trusted logon (Windows Auth) yet supplying the username and password - which is a little contradictory.

Try using the trusted connection connection string here