Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Location
    Cape Town
    Posts
    38

    Unanswered: How to Check using if SQL Server is running on a remote computer

    I am about designing an access project file that connect to a SQL Server database : it could be either on the local computer or a remote workstation, this depending on where the front end is running.

    Bypassing the default log on dialog box i have set my own form that users should use to connect on adp. Then using baseconnection string i connect the adp to sql server database.

    My concern is how can i check from vba if the instance of SQL Server where my back end database is located is running in order to make
    users knowing that due sql server service status off their connection
    tentative will fail.

    inform u i can check same from console management however i would to automate same controle from my adp and avoid that users have to operate such task...
    Benone

  2. #2
    Join Date
    Jul 2002
    Posts
    87
    you can try the connection with on error resume next, if it fails it will give out an error.
    This will surely take time (timeout) but could be a quick solution...

  3. #3
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    good idea coroner,

    what you could do is set up a function that will connect to the database implementing the on error code and within the connection string make the timeout length short, say 10 secs.

    Call the function on load event of the form then on the return an error msg to the user if the cinnection times out i.e can't connect to the database

    cheers

  4. #4
    Join Date
    Oct 2003
    Location
    Cape Town
    Posts
    38
    Thanks following ur suggestion i created below function to operate a connection and retrieve connection error. it seems working perfectly

    ------------
    Function IsSQLServerRunning(sSvrName As String, sUID As String, _
    sPWD As String) As Boolean

    On Error GoTo IsSQLServerRunningTrap:

    Dim svr As SQLDMO.SQLServer
    Set svr = CreateObject("SQLDMO.SQLServer")

    svr.LoginTimeout = 60
    svr.HostName = strServername
    svr.Connect , sUID, sPWD
    svr.Disconnect
    IsSQLServerRunning = True

    IsSQLServerRunningExit:
    Exit Function

    IsSQLServerRunningTrap:
    IsSQLServerRunning = False
    If Err.Number = -2147221504 Then
    MsgBox ModuleName & " - Can not get access to Cresys SQL Server Database. " & _
    "SQL Server does not exit or access is denied or Service is not running", vbCritical, SystemName & " - " & ModuleName
    Else
    MsgBox Err.Description
    End If

    End Function


    -------------
    Code that call function

    If IsSQLServerRunning(XMLReference(0), UserIdent, UserPwd) = True Then

    sCreateConnection XMLReference(0), UserIdent, UserPwd, XMLReference(1)
    Else
    MsgBox "Log In aborted due SQL Server database failure", vbCritical, SystemName & " - " & ModuleName
    End If
    Benone

  5. #5
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149

    Talking

    Thanks to coroner for the original suggestion !

Posting Permissions

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