Results 1 to 8 of 8
  1. #1
    LMS is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2014
    Posts
    3

    Question Connecting to unavailable SQL server via ODBC - can this be handled elegantly from Access VBA?

    In an application that uses MS Access as a front-end to some SQL databases, in VBA I use DBEngine.CreateWorkspace to get a workspace, then workspace.OpenDatabase to connect to my remote SQL server via a defined ODBC System DSN. This all works quite nicely, until someone disconnects the remote SQL machine from the network, or shuts it down, or something else similarly ridiculous. (Note: I know there's a lot ridiculous about this setup, but unfortunately it's an inevitability at this point)

    My question is: Is there a way to elegantly deal with the timeout and subsequent 'SQL Server does not exist or access denied' error messages that come up, within the VBA code? workspace.OpenDatabase throws an error that I can catch and deal with in code, but not before two popups come up and stop my VBA code until an operator clicks OK. I'm trying to modify my VBA code to just suck it up and move on if the external SQL server is not available rather than wait for operator input.

    The code I'm using is as follows:


    Code:
    Dim wrk As Workspace
    Dim db As Database
    
    Set wrk = DBEngine.CreateWorkspace("NewODBCWorkspace", "admin", "", dbUseJet)
    Set db = wrk.OpenDatabase("", False, False, "ODBC;UID=bla;PWD=blabla;")
    When the SQL server behind the selected DSN is not available, the wrk.OpenDatabase call times out, gives me two popups, then fails with an error I can catch. I want to catch/suppress/smash through the popups.

    DoCmd.SetWarnings False doesn't affect it as the error popups are not actually coming from Access itself - I think they're from the underlying ODBC process or the Jet Engine that drives it.

    Any ideas?

    If this would be better suited under the Programming or Import/Export data topics I can repost there if required.
    Also, if anyone knows some good terminology I can Google surrounding this issue, that would also be appreciated. I'm sure the information is out there, but while I can search the MSDN articles for the functions I'm using, I don't know what sort of terms to search to describe 'catch error messages from... ODBC.. thingy... in VBA'
    Cheers.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Is the error trappable via a normal error trap?

    http://www.baldyweb.com/ErrorTrap.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    LMS is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2014
    Posts
    3
    Hi pbaldy, thanks for the response. I have error handling implemented in the way your link describes - once the ODBC connection fails it DOES raise an error that I can catch - but the problem is that two popups appear before that error is raised and caught by the trap, blocking the execution of the error handling until an operator clicks through them. I want to suppress/acknowledge the popups so my code can handle the error and continue on silently.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can you post a db that will fail in this way? I've got a couple of db's that connect to a remote SQL Server, and I was able to trap for it not being found (happens if they fail to connect VPN first). I'm connecting differently (ADO connection), so I can play with yours if you can post a sample.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    accedeholdings is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Sep 2014
    Location
    Hackney, South Australia
    Posts
    17
    I think it will be much better if you will post the code for us to determine the problem.

  6. #6
    LMS is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2014
    Posts
    3
    The code is pretty simple:

    Code:
    Function gfnDSNSelect()
        
        Dim wrk As Workspace
        Dim db As Database
        
    On Error GoTo Err_gfnDSNSelect
        
        Set wrk = DBEngine.CreateWorkspace("NewODBCWorkspace", "admin", "", dbUseJet)
        
        DoCmd.SetWarnings False
        '**** this is the relevant line of code
        Set db = wrk.OpenDatabase("", False, False, "ODBC;UID=bla;PWD=blabla;")
        DoCmd.SetWarnings True
        
        'do stuff with the connection
    
        'Close the workspace and connection.
        'conn.Close
        Set db = Nothing
        wrk.Close
        
    Exit_gfnDSNSelect:
        Exit Function
        
    Err_gfnDSNSelect:
        Call gfnErrorLog("gfnDSNSelect - ", Err.Description, Err.Number)
        Resume Exit_gfnDSNSelect
        
    End Function
    The problem is that the error trapping doesn't stop two popups occurring if the SQL server behind the DSN you select is offline.


    I eventually found something that works by searching 'Suppress ODBC connection failure warnings', courtesy of Trevor Best from http://bytes.com/topic/access/answer...nection-dialog

    PBaldy, I think you would have found it out eventually, as Trevor wrote some code that uses ADO to make the database connection (like you do) which apparently allows VBA error trapping to catch the error before the system throws any popups at you.
    Code:
        Function CanOpenSQLDbLB(pstrServer As String, pstrDb As String, pstrUser
        As String, pstrPassword As String, Optional pfReportError As Boolean =
        True) As Boolean
    
        On Error GoTo CanOpenSQLDbLB_Err
        
        Dim objConn As Object
        Dim strConn As String
        Dim strError As String, lngErr As Long
        Const cstrSQLErr = "[Microsoft][ODBC SQL Server Driver][SQL Server]"
        
        Set objConn = CreateObject("ADODB.Connection")
        
        strConn = strConn & "DRIVER=SQL Server"
        strConn = strConn & ";SERVER=" & pstrServer
        strConn = strConn & ";APP=" & Application.Name
        strConn = strConn & ";WSID=AWorkstation"
        strConn = strConn & ";DATABASE=" & pstrDb
        
        objConn.Open strConn, pstrUser, pstrPassword
        
        CanOpenSQLDbLB = True
        
        CanOpenSQLDbLB_Exit:
        On Error Resume Next
        objConn.Close
        Set objConn = Nothing
        Exit Function
        CanOpenSQLDbLB_Err:
        lngErr = Err.Number
        strError = Err.Description
    
        If InStr(1, strError, cstrSQLErr) Then
        strError = "Error reported by server" & vbCr & vbCr &
        Replace(strError, cstrSQLErr, "")
        End If
    
        Select Case lngErr
        Case Else
        If pfReportError Then
        MsgBox strError, 16, "Error #" & Err & " Attempting to
        open server database"
        End If
        End Select
        Resume CanOpenSQLDbLB_Exit
        
        End Function
    Cheers for the responses everyone.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Yes, I trap for the error opening the ADO connection. Users only get my error message, nothing else.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    oliviaegan is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Oct 2014
    Posts
    1
    I am unable to connect my project to SQL server. Connection is not establishing while connecting it to my young.
    casino list
    Last edited by oliviaegan; 11-04-2014 at 01:10 AM.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 07-18-2014, 11:35 AM
  2. Connecting sybase thru ms-access odbc
    By kolokotr in forum Access
    Replies: 1
    Last Post: 08-15-2012, 12:08 PM
  3. Connecting to Access ODBC behind a router
    By justinmregan87 in forum Access
    Replies: 0
    Last Post: 02-29-2012, 10:23 AM
  4. Connecting sybase thru ms-access odbc
    By murali.kothuru in forum Queries
    Replies: 2
    Last Post: 08-01-2010, 10:00 PM
  5. Replies: 3
    Last Post: 02-01-2010, 08:26 AM

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
  •  
Other Forums: Microsoft Office Forums