Results 1 to 6 of 6
  1. #1
    Jerry Lutz is offline Novice
    Windows 10 Office 365
    Join Date
    May 2021
    Posts
    11

    Display Error when remote sql server down

    Good Morning, I have searched for an answer to this but so far been unsuccessful - I know the answer is probably pretty simple ( For real programmers)

    What I am trying to accomplish is that when a user opens the front end of a Microsoft Access application - if the remote sql server is down - an error message is displayed to the front end user to advise an admin that the remote server needs to be started.

    is there a simple way to do this?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,950
    try to get a count of a table

    Code:
    if not IsServerUp() then  
      msgbox "Server is Down"
      exit sub
    endif
    Code:
    public function IsServerUp() as boolean
    on error resume next
    
    IsServerUp = Dcount("*","dbo.tblData")>0
    exit function

  3. #3
    Jerry Lutz is offline Novice
    Windows 10 Office 365
    Join Date
    May 2021
    Posts
    11
    Thank You ranman256 for your response, admittedly what you have responded seems to be beyond my basic skills... may I ask exactly how I implement the above?

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,950
    adding vb code:
    open your project,
    open vbe (alt-11)
    menu: INSERT, MODULE.

    paste the code block:

    Code:
    public function IsServerUp() as boolean
    on error resume next
    IsServerUp = Dcount("*","dbo.tblData")>0
    if not IsServerUp then msgbox "server is down"
    end function
    


    create a macro: AUTOEXEC
    add the event: RUNCODE
    set the in the function box: IsServerUp()

    Save macro.
    then when the app opens, it will tell the user the server is down (if it is)

  5. #5
    Jerry Lutz is offline Novice
    Windows 10 Office 365
    Join Date
    May 2021
    Posts
    11
    Thank you very much for your time.
    I have created the Module and saved it, the module is called Module2
    I have an existing Autoexec Macro - and added to the top of the Macro
    RUNCODE: IsServerUp()

    Now when I start the applications I see the error message "server is down", the application then proceeds to the next item in the startup macro (My Navigation Form).

    1) The server is not currently down but it reports in the error message that it is

    I also would like to close the program after server down message displays



  6. #6
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    2,009
    I use these two functions
    Code:
    Public Function TableExists(TableName As String) As Boolean
        
        Dim test As String
        Const NAME_NOT_IN_COLLECTION = 3265
        
        ' Trap for any errors.
        On Error Resume Next
        
        test = CurrentDb.TableDefs(TableName).name
        If Err <> NAME_NOT_IN_COLLECTION Then
            TableExists = True
        Else
            TableExists = False
        End If
        
        ' Reset the error variable.
        Err = 0
        
    End Function
    
    
    Function IsODBCConnected(TableName As String) As Boolean
        
        If Not TableExists(TableName) Then Exit Function
        
        Dim rst As DAO.Recordset
        
        On Error Resume Next
        'Debug.Print Err.Number
        Set rst = CurrentDb.OpenRecordset(TableName, dbOpenSnapshot)
        IsODBCConnected = (Err.Number <> 3151)
        
    End Function
    Then in the form that opens put in some code along the lines of
    Code:
    If Not IsODBCConnected("A_LinkedTableName_Goes_In_Here")  Then
       Do some stuff to tell the user
       Application.Quit
    End If
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

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

Similar Threads

  1. Remote SQL Server Password
    By Squeezeoj in forum Access
    Replies: 1
    Last Post: 08-26-2019, 03:18 PM
  2. ODBC with remote path to server
    By jaryszek in forum Programming
    Replies: 3
    Last Post: 06-01-2018, 04:19 AM
  3. Replies: 4
    Last Post: 01-25-2018, 03:04 PM
  4. Replies: 6
    Last Post: 01-11-2017, 05:10 AM
  5. Replies: 3
    Last Post: 02-23-2012, 07:16 PM

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