1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77
| Option Explicit
Private Const SQL_SUCCESS = 0
' ---------------------------------------------------------
' Création/libération Handles
' ---------------------------------------------------------
Private Const SQL_NULL_HANDLE As Long = 0&
Private Const SQL_HANDLE_ENV As Byte = 1 ' Environment
Private Declare Function SQLAllocHandle Lib "odbc32.dll" ( _
ByVal HandleType As Integer, _
ByVal InputHandle As Long, _
ByRef OutputHandle As Long) As Integer
Private Declare Function SQLFreeHandle Lib "odbc32.dll" ( _
ByVal HandleType As Integer, _
ByVal Handle As Long) As Integer
' ---------------------------------------------------------
' Attribut d'environnement version ODBC
' agit sur le comportement de certaines fonctions de l'api ODBC
' ---------------------------------------------------------
Private Const SQL_ATTR_ODBC_VERSION As Long = 200
Private Const SQL_OV_ODBC3 As Long = 3
Private Declare Function SQLSetEnvAttr Lib "odbc32.dll" ( _
ByVal EnvironmentHandle As Long, _
ByVal lAttribute As Long, _
ByVal Value As Long, _
ByVal StringLength As Long) As Integer
' ---------------------------------------------------------
' Enumérations sources de données
' ---------------------------------------------------------
Private Const SQL_FETCH_FIRST As Long = 2
Private Const SQL_FETCH_FIRST_USER = 31
Private Const SQL_FETCH_FIRST_SYSTEM = 32
Private Const SQL_FETCH_NEXT As Long = 1
Private Declare Function SQLDataSources Lib "odbc32.dll" ( _
ByVal hEnv As Long, _
ByVal fDirection As Integer, _
ByVal szDSN As String, _
ByVal cbDSNMax As Integer, _
ByRef pcbDSN As Integer, _
ByVal szDescription As String, _
ByVal cbDescriptionMax As Integer, _
ByRef pcbDescription As Integer) As Integer
Sub ListDataSces()
Dim hEnv As Long, sqlretVal As Integer, intDir As Integer
Dim strDsn As String, intDsnSize As Integer, strDesc As String, intDescSize As Integer
Dim strList As String
' Environment Handle
sqlretVal = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, hEnv)
' Version ODBC : 3
sqlretVal = SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, SQL_OV_ODBC3, 4)
' SQL_FETCH_FIRST ou SQL_FETCH_FIRST_USER ou SQL_FETCH_FIRST_SYSTEM
intDir = SQL_FETCH_FIRST_USER
Do While sqlretVal = SQL_SUCCESS
strDsn = String(257, vbNullChar): intDsnSize = 256
strDesc = String(257, vbNullChar): intDescSize = 256
sqlretVal = SQLDataSources(hEnv, intDir, _
strDsn, 256, intDsnSize, _
strDesc, 256, intDescSize)
If sqlretVal = SQL_SUCCESS Then
strList = strList & Left(strDsn, intDsnSize) & vbCrLf
End If
intDir = SQL_FETCH_NEXT
Loop
sqlretVal = SQLFreeHandle(SQL_HANDLE_ENV, hEnv)
MsgBox strList
End Sub |
Partager