Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ADO & SQL connection (Access 2k sqlServer 7)

    I'm trying to experiment with ADO and SQL-Server.
    The following code give an error :
    Login failed for user 'sa'. Reason:Not associated with a trusted SQL Server connection.

    <pre>Function TestSQL()
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strCnn As String

    strCnn = "Provider=sqloledb;" & _
    "Data Source=Obelix;Database=Northwind;User Id=sa;Password=; "
    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset
    cnn.Open strCnn
    rst.Open "customers", cnn
    Do While Not rst.EOF
    Debug.Print rst!ContactName
    rst.MoveNext
    Loop
    rst.Close
    cnn.Close
    End Function
    </pre>

    If I use strCnn = "DSN=NW;User Id=sa;Password=; " the function is running, but I try to avoid ODBC.
    Anny sugestion ?
    Francois

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO & SQL connection (Access 2k sqlServer 7)

    Try "UID=sa;" and "PWD=;" instead of User ID and Password.
    Also, are you connecting directly to SQL Server? My connection string looks like:<pre>strCnn= "PROVIDER=sqloledb; " & _
    "DRIVER={SQL SERVER}; SERVER=myserver; UID=sa; PWD=; " & _
    "DATABASE=mydata;"</pre>

    HTH <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO & SQL connection (Access 2k sqlServer 7)

    Thanks to answer.
    Yes, i'm trying to connect directly to the SQL Server.
    I try your syntax but have the same error.
    Francois

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO & SQL connection (Access 2k sqlServer 7)

    When I first started working with SQL Server I had some problems of the same nature. Double check the name of the Database on the server. I think the name of the Northwind example is actually NorthwindCS.

    Also, I usually create another username for testing purposes. I rarely use sa. Either way, be sure that the username has access rights to read/write to the database as needed.

    HTH <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO & SQL connection (Access 2k sqlServer 7)

    The name of the database is OK.
    I try different users and with every user I get the same error.
    All used user names work well when working with ODBC and DSN.
    Francois

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO & SQL connection (Access 2k sqlServer 7)

    Problem solved.
    I have to add
    strCnn = "Provider=sqloledb;" & _
    "Data Source=Obelix;Database=Northwind;UID=;Password=;integrated security=sspi;"

    I don't know what it means, but I found it in the newsgroup Data.OLEDB from MS
    Thanks for helping
    Francois

Posting Permissions

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