Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: ADO Connection from Excel to SQL Server

    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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Also, any reason why you're selecting TOP 100 PERCENT?
    George
    Home | Blog

  4. #4
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    I'm using that so that it will return only 1 value for the Weeks Range Title. Would you have written the SQL in another way?

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Think about it; top 100% is the whole resultset!

    TOP 1 limits it to a single record...
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •