Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enumerate DSNs
Using Windows XP and Office 2003;
If you have example code on how to loop thru or enumerate the DSN's on your local machine could you please post it? Thanks much in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enumerate DSNs
Courtesy of Randy Birch
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 SQL_MAX_DSN_LENGTH As Long = 32 Private Const SQL_MAX_DESC_LENGTH As Long = 128 Private Const SQL_SUCCESS As Long = 0 Private Const SQL_FETCH_NEXT As Long = 1 Private Const SQL_NULL_HANDLE As Long = 0 Private Const SQL_HANDLE_ENV As Long = 1 Private Const SQL_ATTR_ODBC_VERSION As Long = 200 Private Const SQL_OV_ODBC3 As Long = 3 Private Const SQL_IS_INTEGER As Long = (-6) Private Sub GetUserSystemDSN() Dim hEnv As Long Dim sServer As String Dim sDriver As String Dim nSvrLen As Integer Dim nDvrLen As Integer Dim lstDSNs As String If SQLAllocHandle(SQL_HANDLE_ENV, _ SQL_NULL_HANDLE, hEnv) < 0 Then If SQLSetEnvAttr(hEnv, _ SQL_ATTR_ODBC_VERSION, _ SQL_OV_ODBC3, _ SQL_IS_INTEGER) < 0 Then sServer = Space$(SQL_MAX_DSN_LENGTH) Do While SQLDataSources(hEnv, _ SQL_FETCH_NEXT, _ sServer, _ SQL_MAX_DSN_LENGTH, _ nSvrLen, _ sDriver, _ SQL_MAX_DESC_LENGTH, _ nDvrLen) = SQL_SUCCESS lstDSNs = lstDSNs & Left$(sServer, nSvrLen) & vbNewLine sServer = Space$(SQL_MAX_DSN_LENGTH) Loop End If 'If SQLSetEnvAttr Call SQLFreeHandle(SQL_HANDLE_ENV, hEnv) End If 'If SQLAllocHandle MsgBox lstDSNs End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "XP" wrote in message ... Using Windows XP and Office 2003; If you have example code on how to loop thru or enumerate the DSN's on your local machine could you please post it? Thanks much in advance. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enumerate DSNs
"XP" wrote:
Using Windows XP and Office 2003; If you have example code on how to loop thru or enumerate the DSN's on your local machine could you please post it? Thanks much in advance. You can find a VBScript sample he http://www.microsoft.com/technet/scr...5/hey0725.mspx You can run it from VBA, you only need to replace WScript.Echo with Debug.Print. Hope this helps. -- urkec |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel and DSNs | Excel Discussion (Misc queries) | |||
How to detect use of DSNs? | Excel Programming | |||
enumerate sheet | Excel Worksheet Functions | |||
how to enumerate? | New Users to Excel | |||
Creating Multiple ODBC System DSNs from an Excel Spreadsheet | Excel Programming |