Option Explicit
Private Declare Function SQLDataSources Lib "odbc32.dll" (ByVal hEnv As Long, ByVal fDirection As Integer, ByVal szDSN As String, ByVal cbDSNMax As Integer, pcbDSN As Integer, ByVal szDescription As String, ByVal cbDescriptionMax As Integer, pcbDescription As Integer) As Long
Private Declare Function SQLAllocHandle Lib "odbc32.dll" (ByVal HandleType As Integer, ByVal InputHandle As Long, OutputHandlePtr As Long) As Long
Private Declare Function SQLSetEnvAttr Lib "odbc32.dll" (ByVal EnvironmentHandle As Long, ByVal dwAttribute As Long, ByVal ValuePtr As Long, ByVal StringLen As Long) As Long
Private Declare Function SQLFreeHandle Lib "odbc32.dll" (ByVal HandleType As Integer, ByVal Handle As Long) As Long
Private Const DSN_LEN As Long = 32
Private Const DESC_LENGTH As Long = 128
Private Const FoundOk As Long = 0
Private Const GetNext As Long = 1
Private Const Null_HANDLE As Long = 0
Private Const HANDLE_ENV As Long = 1
Private Const ATTR_ODBC_VERSION As Long = 200
Private Const OV_ODBC3 As Long = 3
Private Const IS_INTEGER As Long = (-6)
Sub Example()
If DSNExists("MS Access Database") Then
MsgBox "DSN Exists"
Else
MsgBox "DSN does not exist"
End If
End Sub
Function DSNExists(DSN As String) As Boolean
Dim hEnv As Long
Dim sServer As String
Dim sDriver As String
Dim nSvrLen As Integer
Dim nDvrLen As Integer
If SQLAllocHandle(HANDLE_ENV, _
Null_HANDLE, hEnv) <> 0 Then
If SQLSetEnvAttr(hEnv, _
ATTR_ODBC_VERSION, _
OV_ODBC3, _
IS_INTEGER) <> 0 Then
sServer = Space$(DSN_LEN)
sDriver = Space$(DESC_LENGTH)
Do While SQLDataSources(hEnv, _
GetNext, _
sServer, _
DSN_LEN, _
nSvrLen, _
sDriver, _
DESC_LENGTH, _
nDvrLen) = FoundOk
If Trim(DSN) = Trim(Left$(sServer, nSvrLen)) Then
DSNExists = True
Exit Do
End If
sServer = Space$(DSN_LEN)
Loop
End If
Call SQLFreeHandle(HANDLE_ENV, hEnv)
End If
End Function