creating ODBC system DSN programatically 
Author Message
 creating ODBC system DSN programatically

anybody ever used VB to add a new ODBC system DSN so it would appear in
the Control Panel ODBC Mgr?

Sent via Deja.com
http://www.*-*-*.com/



Sun, 27 Jul 2003 20:05:08 GMT  
 creating ODBC system DSN programatically
Yes. This example is from an Access Module, but should work identically
in a VB Module  (I've commented the Access SysCmd's which are giving
progress reports to Access's bottom status line)

Option Explicit

'CREATE A System DSN to an Informix Database.
'Requires the Informix CLI ODBC drivers to be loaded on PC).
'

'*********************** Code Start ***************************
'

'Public MedicUserID As String
'Public MedicPassword As String

Const HMT_Driver = "INFORMIX-CLI 2.5 (32 bit)"
Const HMT_Medic_DSN_name = "Informix for Medic"  'This is the
description that will show in ODBC Manager
Const HMT_Database_name = "v001"
Const HMT_DSN_Description = "Informix Link to Medic v001"
Const HMT_ServerName = "ifx1"
Const HMT_Server_IP = "000.000.000.000"   'Raw IP address is used to
avoid NT
                                      'Domain name resolution probs.
Const HMT_ServiceName = "ifx1_tcp1"
Const HMT_Host = "V9400"

Private Declare Function RegEnumKeyEx Lib "advapi32.dll" _
Alias "RegEnumKeyExA" _
  (ByVal hKey As Long, _
   ByVal dwIndex As Long, _
   ByVal lpName As String, _
   lpcbName As Long, _
   ByVal lpReserved As Long, _
   ByVal lpClass As String, _
   lpcbClass As Long, _
   ByVal lpftLastWriteTime As String) As Long

Private Declare Function RegOpenKeyEx Lib "advapi32.dll" _
Alias "RegOpenKeyExA" _
    (ByVal hKey As Long, _
    ByVal lpSubKey As String, _
    ByVal ulOptions As Long, _
    ByVal samDesired As Long, phkResult As Long) As Long

Private Declare Function SQLConfigDataSource Lib "odbccp32.dll" _
    (ByVal hWndParent As Long, _
    ByVal fRequest As Integer, _
    ByVal lpszDriver As String, _
    ByVal lpszAttributes As String) As Long

Private Declare Function RegCloseKey Lib "advapi32.dll" _
    (ByVal hKey As Long) As Long

      Const HKEY_LOCAL_MACHINE = &H80000002

      Const ERROR_SUCCESS = 0&
      Const SYNCHRONIZE = &H100000
      Const STANDARD_RIGHTS_READ = &H20000
      Const STANDARD_RIGHTS_WRITE = &H20000
      Const STANDARD_RIGHTS_EXECUTE = &H20000
      Const STANDARD_RIGHTS_REQUIRED = &HF0000
      Const STANDARD_RIGHTS_ALL = &H1F0000
      Const KEY_QUERY_VALUE = &H1
      Const KEY_SET_VALUE = &H2
      Const KEY_CREATE_SUB_KEY = &H4
      Const KEY_ENUMERATE_SUB_KEYS = &H8
      Const KEY_NOTIFY = &H10
      Const KEY_CREATE_LINK = &H20
      Const KEY_READ = ((STANDARD_RIGHTS_READ Or _
                        KEY_QUERY_VALUE Or _
                        KEY_ENUMERATE_SUB_KEYS Or _
                        KEY_NOTIFY) And _
                        (Not SYNCHRONIZE))

      Const REG_DWORD = 4
      Const REG_BINARY = 3
      Const REG_SZ = 1

      Const ODBC_ADD_SYS_DSN = 4

Function Check_Medic_DSN() As Boolean

         '  Look for our System Data Source Name.  If we find it, then
great!
         '  If not, then let's create one on the fly.

         Dim lngKeyHandle As Long
         Dim lngResult As Long
         Dim lngCurIdx As Long
         Dim strValue As String
         Dim classValue As String
         Dim timeValue As String
         Dim lngValueLen As Long
         Dim classlngValueLen As Long
         Dim lngData As Long
         Dim lngDataLen As Long
         Dim Sep$
         Dim strResult As String
         Dim DSNfound As Long
         Dim syscmdresult As Long

'         syscmdresult = SysCmd(acSysCmdSetStatus, "Looking for System
DSN '" & HMT_Medic_DSN_name & "' ...")

         '  Let's open the registry key that contains all of the
         '  System Data Source Names.

         lngResult = RegOpenKeyEx(HKEY_LOCAL_MACHINE, _
                 "SOFTWARE\ODBC\ODBC.INI", _
                  0&, _
                  KEY_READ, _
                  lngKeyHandle)

         If lngResult <> ERROR_SUCCESS Then
             MsgBox "ERROR:  Cannot open the registry key
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI." & vbCrLf & vbCrLf & _
                    "Please make sure that ODBC and the SQL Server ODBC
drivers have been installed." & vbCrLf & _
                    "Contact the HMT Help Desk (410) 238-5500 for
assistance."
'             syscmdresult = SysCmd(acSysCmdClearStatus)
             Check_Medic_DSN = False
         End If

         ' Now that the key is open, Let's look among all of
         ' the possible system data source names for the one
         ' we want.

         lngCurIdx = 0
         DSNfound = False

         Do
            lngValueLen = 512
            classlngValueLen = 512
            strValue = String(lngValueLen, 0)
            classValue = String(classlngValueLen, 0)
            timeValue = String(lngValueLen, 0)
            lngDataLen = 512

            lngResult = RegEnumKeyEx(lngKeyHandle, _
                                     lngCurIdx, _
                                     strValue, _
                                     lngValueLen, _
                                     0&, _
                                     classValue, _
                                     classlngValueLen, _
                                     timeValue)
            lngCurIdx = lngCurIdx + 1

         If lngResult = ERROR_SUCCESS Then

           ' Is this our System Data Source Name?

           If strValue = HMT_Medic_DSN_name Then

             '  It is!  Let's assume everything is good and do nothing.

             DSNfound = True
             syscmdresult = SysCmd(acSysCmdClearStatus)

           End If

         End If

         Loop While lngResult = ERROR_SUCCESS And Not DSNfound

         Call RegCloseKey(lngKeyHandle)

         If Not DSNfound Then

           '  Our System Data Source Name doesn't exist, so let's
           '  try to create it on the fly.

'           syscmdresult = SysCmd(acSysCmdSetStatus, "Creating System
DSN '" & HMT_Medic_DSN_name & "'...")

          Sep$ = Chr$(0)
          'Sep$ = " ; "  <-- Doesn't Work

          'Per The INFORMIX-CLI Programmer's Manual, these are the
recognized attributes:
          'LONG NAME                SHORT NAME (Long Name or Short Name
can be used, Except DSN must be short!)
          'DataSourceName           DSN     Must use DSN, not the long
name, for this attribute.
          'Database                 DB
          'HostName                 HOST
          'LoginID                  UID
          'Password                 PWD
          'Service                  SERV
          'CursorBehavior           CB      0=Closed, 1=Preserve
          'EnableScrollableCursors  ESC     0=None, 1=Enable scrollable
Cursors
          'Server                   SERVER
          'GetDBListFromInformix    GDBLFI  1 = Yes
          'Protocol                 PRO     (Windows 3.1 and Windows NT
only) onsoctcp
          'YieldProc                YLD     (Windows 3.1 and Windows NT
only) 1 = None
          '
          'HOWEVER!  The API call below responds best to the key names
in the Registry, under
          '          HKEY_LOCAL_MACHINE / Software / ODBC / ODBC.INI
           lngResult = SQLConfigDataSource(0, _
                                           ODBC_ADD_SYS_DSN, _
                                           HMT_Driver, _
                                           "DSN=" & HMT_Medic_DSN_name
& Sep$ & _
                                           "ServerName=" &
HMT_ServerName & Sep$ & _
                                           "Database=" &
HMT_Database_name & Sep$ & _
                                           "Service=" & HMT_ServiceName
& Sep$ & _
                                           "EnableScrollableCursors=0"
& Sep$ & _
                                           "HostName=" & HMT_Host &
Sep$ & _
                                           "CursorBehavior=0" & Sep$ & _
                                           "YieldProc=1" & Sep$ & _
                                           "Protocol=onsoctcp" & Sep$ &
_
                                           "GetDBListFromInformix=1" &
Sep$ & _
                                           "Description=" &
HMT_DSN_Description & Sep$ & Chr(0))

'this was in the original code, but doesn't appear to do anything.
"UseProcForPrepare=Yes" & Sep$ & -

           If lngResult = False Then

             MsgBox "ERROR:  Could not create the System DSN '" &
HMT_Medic_DSN_name & "'." & vbCrLf & vbCrLf & _
                    "Please make sure that the " & HMT_Driver & " ODBC
drivers have been installed." & vbCrLf & _
                    "Contact the Help Desk at (999) 999-9999 for more
information."

'             syscmdresult = SysCmd(acSysCmdClearStatus)
             Check_Medic_DSN = False

           End If

         End If

'         syscmdresult = SysCmd(acSysCmdClearStatus)
         Check_Medic_DSN = True

End Function

'*********************** Code End ***************************



Quote:
> anybody ever used VB to add a new ODBC system DSN so it would appear
in
> the Control Panel ODBC Mgr?

> Sent via Deja.com
> http://www.deja.com/

Sent via Deja.com
http://www.deja.com/


Tue, 29 Jul 2003 04:15:01 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Crystal and ODBC DSN/DSN-less connections - do I need USER DSN or SYSTEM DSN

2. Creating ODBC System DSN by using ODBC API ?

3. How to create a ODBC system DSN

4. How do I create a ODBC system DSN?

5. Creating ODBC System DSN entry

6. Creating a system dsn or file dsn

7. which Dsn(Data Source Name) is Faster System DSN Or File DSN

8. Create DSN programatically

9. Create DSN programatically

10. Programatically register ODBC System Data Source??

11. Programatically register ODBC System Data Source??

12. Programatically register ODBC System Data Source??

 

 
Powered by phpBB® Forum Software