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

    Unanswered: Connecting to SQL

    I am trying to get the Access database I'm creating to talk to the SQL server and return me with my data set.

    I've looked at ConnectionStrings.com and have got a string from there but it still doesnt work, I'm not sure where I gone wrong...

    my code is here

    Code:
    Private Sub Form_Load()
        Dim cnn As ADODB.Connection
        Dim rs As ADODB.Recordset
        Dim sQRY As String
        Dim strFilePath As String
        Dim strConnect As String
    
        Set cnn = New ADODB.Connection
        Set rs = New ADODB.Recordset
        cnn.Open strFilePath = "Driver={SQLServer};Server=CISSQL1;Database=CORPINFO;Trusted_Connection=yes;"
        MsgBox "Connection Made"
        sQRY = _
            "SELECT * FROM jez.HaH_ReferralsReason"
        rs.CursorLocation = adUseClient
        rs.Open sQRY, cnn, adOpenKeyset, adLockReadOnly
        MsgBox "Connection Lost"
        MsgBox rs.RecordCount
        'Set lstSearch.Recordset = rs
    
        rs.Close
        cnn.Close
        Set rs = Nothing
        Set cnn = Nothing
        Exit Sub
    End Sub
    I also tried this string as well
    Code:
    cnn.Open strConnect = _T("Provider=sqloledb;Data Source=CISSQL1;" & _
            "Initial Catalog=CORPINFO;" & _
            "Integrated Security=SSPI;");
    I have used the line "Driver={SQLServer};Server=CISSQL1;Database=CORPIN FO;Trusted_Connection=yes;" in some Excel reports and it works like a treat...
    Last edited by JezLisle; 10-03-08 at 07:05.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What errors messages are you getting?
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Sorry, having bad day, forgot to add that in too...

    Error:
    [Microsoft] [ODBC Driver Manager] Data source name not found and no default driver specified

    I dont know why as I said in thread 1 the connection works fine from Excel to SQL Server

  4. #4
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    I have changed the cnn.Open to this below and all works fine...

    Code:
    cnn.Open "Provider=sqloledb;Data Source=CISSQL1;Initial Catalog=CORPINFO;Integrated Security=SSPI;"

Posting Permissions

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