Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Oct 2001
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ODBC - connection failed

    I have an Access database that I upsized with the wizard wishing to continue using Access for the Front End. I want to be able to connect to the SQL database without having each computer setup for the DSN. I added this to the autoexec macro/converted to a module.



    • Function autoexec()
    • On Error GoTo autoexec_Err
    • **oConn.Open "ODBC;Driver={SQL Server};" "Server=xxxxMSQL01;" "Database=Sublist;" "Trusted_Connection=yes"**
    • DoCmd.OpenForm "FSublistc", acNormal, "", "", , acNormal
    • DoCmd.Maximize
    • autoexec_Exit:
    • Exit Function
    • autoexec_Err:
    • MsgBox Error$
    • Resume autoexec_Exit
    • End Function

    The error I get is ODBC-Connection failed.
    For my testing I did create a DSN on this computer with this user that worked fine. Then when I removed the DSN connection and ran the databaseI got this message, ODBC-Connection failed, again.


    Any ideas?
    Thanks.

    Jane

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    221
    Thanks
    0
    Thanked 21 Times in 20 Posts
    Quote Originally Posted by jfrasier View Post
    I have an Access database that I upsized with the wizard wishing to continue using Access for the Front End. I want to be able to connect to the SQL database without having each computer setup for the DSN. I added this to the autoexec macro/converted to a module.


    • Function autoexec()
    • On Error GoTo autoexec_Err
    • **oConn.Open "ODBC;Driver={SQL Server};" "Server=xxxxMSQL01;" "Database=Sublist;" "Trusted_Connection=yes"**
    • DoCmd.OpenForm "FSublistc", acNormal, "", "", , acNormal
    • DoCmd.Maximize
    • autoexec_Exit:
    • Exit Function
    • autoexec_Err:
    • MsgBox Error$
    • Resume autoexec_Exit
    • End Function
    The error I get is ODBC-Connection failed.
    For my testing I did create a DSN on this computer with this user that worked fine. Then when I removed the DSN connection and ran the databaseI got this message, ODBC-Connection failed, again.


    Any ideas?
    Thanks.

    Jane
    This isn't exactly the same situation, but the following code works for me - can you adapt successfully?

    Code:
    Sub TestConnection2()
        Dim cnn As ADODB.Connection
        Dim rst As ADODB.Recordset
        
        On Error GoTo TC2Error
        Set cnn = New ADODB.Connection
        cnn.Open "Driver={SQL Server};Server=CATMAN-PC\SQLEXPRESS;Database=pubs;Trusted_Connection=yes"
        MsgBox "After connection opened", vbInformation, "Test Connection"
        Set rst = New ADODB.Recordset
        rst.Open "authors", cnn, adOpenKeyset, adLockReadOnly
        MsgBox rst.RecordCount, vbInformation, "Record Count"
        rst.Close
        cnn.Close
        MsgBox "End of connection processing", vbInformation, "TestConnection"
    TC2Exit:
        On Error Resume Next
        Set rst = Nothing
        Set cnn = Nothing
        Exit Sub
    TC2Error:
        MsgBox "Error " & Err.Number & vbCrLf & Err.Description, vbExclamation, "Error in TestConnection"
        Resume TC2Exit
    End Sub
    Jeremy

  4. #3
    Star Lounger
    Join Date
    Oct 2001
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by jeremybarker View Post
    This isn't exactly the same situation, but the following code works for me - can you adapt successfully?

    Code:
    Sub TestConnection2()
        Dim cnn As ADODB.Connection
        Dim rst As ADODB.Recordset
        
        On Error GoTo TC2Error
        Set cnn = New ADODB.Connection
        cnn.Open "Driver={SQL Server};Server=CATMAN-PC\SQLEXPRESS;Database=pubs;Trusted_Connection=yes"
        MsgBox "After connection opened", vbInformation, "Test Connection"
        Set rst = New ADODB.Recordset
        rst.Open "authors", cnn, adOpenKeyset, adLockReadOnly
        MsgBox rst.RecordCount, vbInformation, "Record Count"
        rst.Close
        cnn.Close
        MsgBox "End of connection processing", vbInformation, "TestConnection"
    TC2Exit:
        On Error Resume Next
        Set rst = Nothing
        Set cnn = Nothing
        Exit Sub
    TC2Error:
        MsgBox "Error " & Err.Number & vbCrLf & Err.Description, vbExclamation, "Error in TestConnection"
        Resume TC2Exit
    End Sub
    Jeremy

    I'll try this. Where do I put this? I have tried adding it the autoexec converted module and as a event procedure ON OPEN for the form that I could open with a startup action, but don't seem to get it right.

  5. #4
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    221
    Thanks
    0
    Thanked 21 Times in 20 Posts
    Quote Originally Posted by jfrasier View Post
    I'll try this. Where do I put this? I have tried adding it the autoexec converted module and as a event procedure ON OPEN for the form that I could open with a startup action, but don't seem to get it right.
    Well you don't need literally everything that's in there - that was just me demonstrating how to open a recordset after setting up a connection, and the whole thing is wrapped in lots of error handling!

    I was basically hoping that you could use my "cnn.Open" statement (with your server and PC name) in place of your "oConn.Open" statement, which seems to be the thing that's failing. I assume that if you can get a connection established via the autoexec then your form will work once opened - is that not what you implied in your original post?

    Jeremy

  6. #5
    Star Lounger
    Join Date
    Oct 2001
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Nope. I tried cnn.Open. Connection.open ... I had parentheses for each item in the string and I see all of the parts of yours use just one so I tried that.

    Thanks.

  7. #6
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    221
    Thanks
    0
    Thanked 21 Times in 20 Posts
    Quote Originally Posted by jfrasier View Post
    Nope. I tried cnn.Open. Connection.open ... I had parentheses for each item in the string and I see all of the parts of yours use just one so I tried that.

    Thanks.
    So is this still not working? When you originally created a DSN. how did you get the application to work in that case - did you have a Connection Open statement that referred to the DSN?

  8. #7
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Redditch, Worcestershire, England
    Posts
    221
    Thanks
    0
    Thanked 21 Times in 20 Posts
    Quote Originally Posted by jfrasier View Post
    Nope. I tried cnn.Open. Connection.open ... I had parentheses for each item in the string and I see all of the parts of yours use just one so I tried that.

    Thanks.
    Rereading your original post, I'm a bit puzzled. When I upsize to SQL Server and tell the wizard to keep Access as the front end it seems to connect the tables without needing a DSN. So surely when you copy the Access client to another machine this connection string should be retained within Access since the name of the SQL Server and the remote tables isn't changing?

    I've found some code that will change the connection string for a linked table if required, but just not sure if I've misunderstood your setup!

    Jeremy

Tags for this Thread

Posting Permissions

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