[AccessD] SQL Server not found

Ping The server first to check?

Ping(MySQLServerNameOrAlias)

With the following code:

'======================================
Option Compare Database

Option Explicit

Const SOCKET_ERROR = 0
Const MAX_IP = 10

Private Type WSAdata
    wVersion As Integer
    wHighVersion As Integer
    szDescription(0 To 255) As Byte
    szSystemStatus(0 To 128) As Byte
    iMaxSockets As Integer
    iMaxUdpDg As Integer
    lpVendorInfo As Long
End Type

Private Type Hostent
    h_name As Long
    h_aliases As Long
    h_addrtype As Integer
    h_length As Integer
    h_addr_list As Long
End Type

Private Type IP_OPTION_INFORMATION
    TTL As Byte
    Tos As Byte
    flags As Byte
    OptionsSize As Long
    OptionsData As String * 128
End Type

Private Type IP_ECHO_REPLY
    Address(0 To 3) As Byte
    Status As Long
    RoundTripTime As Long
    DataSize As Integer
    Reserved As Integer
    Data As Long
    Options As IP_OPTION_INFORMATION
End Type

Private Declare Function GetHostByName Lib "wsock32.dll" Alias
"gethostbyname" (ByVal HostName As String) As Long
Private Declare Function WSAStartup Lib "wsock32.dll" (ByVal
wVersionRequired&, lpWSAdata As WSAdata) As Long
Private Declare Function WSACleanup Lib "wsock32.dll" () As Long
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory"
(hpvDest As Any, hpvSource As Any, ByVal cbCopy As Long)
Private Declare Function IcmpCreateFile Lib "icmp.dll" () As Long
Private Declare Function IcmpCloseHandle Lib "icmp.dll" (ByVal HANDLE As
Long) As Boolean
Private Declare Function IcmpSendEcho Lib "ICMP" (ByVal IcmpHandle As
Long, ByVal DestAddress As Long, ByVal RequestData As String, ByVal
RequestSize As Integer, RequestOptns As IP_OPTION_INFORMATION,
ReplyBuffer As IP_ECHO_REPLY, ByVal ReplySize As Long, ByVal Timeout As
Long) As Boolean

Public Function Ping(sAddr As String, Optional Timeout As Integer =
2000) As Integer
Dim hFile As Long, lpWSAdata As WSAdata
Dim hHostent As Hostent, AddrList As Long
Dim Address As Long, rIP As String
Dim OptInfo As IP_OPTION_INFORMATION
Dim EchoReply As IP_ECHO_REPLY

Call WSAStartup(&H101, lpWSAdata)

If GetHostByName(sAddr + String(64 - Len(sAddr), 0)) <> SOCKET_ERROR
Then
    CopyMemory hHostent.h_name, ByVal GetHostByName(sAddr + String(64 -
Len(sAddr), 0)), Len(hHostent)
    CopyMemory AddrList, ByVal hHostent.h_addr_list, 4
    CopyMemory Address, ByVal AddrList, 4
End If

hFile = IcmpCreateFile()

If hFile = 0 Then
    Ping = -2 ' MsgBox "Unable to Create File Handle"
    Exit Function
End If

OptInfo.TTL = 255

If IcmpSendEcho(hFile, Address, String(32, "A"), 32, OptInfo, EchoReply,
Len(EchoReply) + 8, Timeout) Then
    rIP = CStr(EchoReply.Address(0)) + "." + CStr(EchoReply.Address(1))
+ "." + CStr(EchoReply.Address(2)) + "." + CStr(EchoReply.Address(3))
Else
    Ping = -1 ' MsgBox "Timeout"
End If

If EchoReply.Status = 0 Then
    Ping = EchoReply.RoundTripTime
Else
    Ping = -3
End If

IcmpCloseHandle hFile
WSACleanup

End Function

'===================================================================

Cheers
Darryl
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Doug Steele
Sent: Sunday, 4 October 2009 5:53 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] SQL Server not found

Hello all:

I have an Access front end with a SQL Server express back end.
Occasionally the SQL Server isn't available.  I'd like the Access db to
exit quickly and gracefully when this happens.  However, it takes about
two minutes for the initial query to SQL server to fail and it fails
with a screen asking the user to fill in the Server name.  Is there a
quick way to find out if the server is available before I try to query
it and to avoid getting the failure screens?

Thanks,
Doug Steele
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

"This e-mail and any attachments to it (the "Communication") is, unless otherwise stated, confidential,  may contain copyright material and is for the use only of the intended recipient. If you receive the Communication in error, please notify the sender immediately by return e-mail, delete the Communication and the return e-mail, and do not read, copy, retransmit or otherwise deal with it. Any views expressed in the Communication are those of the individual sender only, unless expressly stated to be those of Australia and New Zealand Banking Group Limited ABN 11 005 357 522, or any of its related entities including ANZ National Bank Limited (together "ANZ"). ANZ does not accept liability in connection with the integrity of or errors in the Communication, computer virus, data corruption, interference or delay arising from or in respect of the Communication."