Hello,

I am having a problem with a DB I have.  It is being used by several users and is connected to SQL server 2000 tables.  To do this we are using ODBC drivers.  I am trying to have the DB check to see if a DSN is already created on the workstation.  If not to build the DSN.  This is all being done in VB.  The SQL database is using SQL authentication.  I have had some success with the coding however I am running into a small problem with the user name and password coding.  Can anyone give me a hand with this?  I have encluded the code I am using in the attachment.  Any asssistance will be great.


CODE:

Option Compare Database

Const JDS_DSN_name = "CAOSQL1"
Const JDS_Server_name = "<enter server IP>"   ' Raw IP address is used to avoid NT _
                                                                           Domain name resolution probs.

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_SDSN()

         '  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 strResult As String
         Dim DSNfound As Long
         Dim syscmdresult As Long

         syscmdresult = SysCmd(acSysCmdSetStatus, "Looking for System DSN " & JDS_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 call your MDTS System Administrator for more information."
             syscmdresult = SysCmd(acSysCmdClearStatus)
             Check_SDSN = -1
         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 = JDS_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 " & JDS_DSN_name & "...")
        
           lngResult = SQLConfigDataSource(0, _
                                           ODBC_ADD_SYS_DSN, _
                                           "SQL Server", _
                                           "DSN=" & JDS_DSN_name & Chr(0) & _
                                           "Server=" & JDS_Server_name & Chr(0) & _
                                           "Database=cao" & Chr(0) & _
                                           "UseProcForPrepare=Yes" & Chr(0) & _
                                           "Description=JEFFTEST1" & Chr(0) & Chr(0))
                                                                                    
                                          
           If lngResult = False Then
          
             MsgBox "ERROR:  Could not create the System DSN " & JDS_DSN_name & "." & vbCrLf & vbCrLf & _
                    "Please make sure that the SQL Server ODBC drivers have been installed." & vbCrLf & _
                    "Contact your MDTS System Administrator for more information."
                    
             syscmdresult = SysCmd(acSysCmdClearStatus)
             Check_SDSN = -1

           End If
          
         End If
        
         syscmdresult = SysCmd(acSysCmdClearStatus)
         Check_SDSN = 0

End Function

Try checking for an existing UDL on the client side, and if not exists, copy over the server side UDL.

see if this is of any help.....
http://www.codesforall.com/vb/codes80510.htm


____________________________
Mahipal Padigela
http://www.mahipalreddy.com

'More information: This includes three functions: one to create any system DSN,and two use this function to create DSNs specifically for Access and SQL server.

Option Explicit

Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" _
   (ByVal hwndParent As Long, ByVal fRequest As Long, _
   ByVal lpszDriver As String, ByVal lpszAttributes As String) _
   As Long

Private Declare Function SQLConfigDataSource16BIT Lib "ODBCINST.DLL" _
          (ByVal hwndParent As Integer, ByVal fRequest As Integer, ByVal _
          lpszDriver As String, ByVal lpszAttributes As String) As Integer


Private Const ODBC_ADD_SYS_DSN = 4

Public Function CreateSQLServerDSN(DSNName As String, _
   ServerName As String, Database As String) As Boolean

'PURPOSE: 'CREATES A SYSTEM DSN FOR AN SQL SERVER DATABASE
'PARAMETERS: 'DSNName = DSN Name
             'ServerName = Name of Server
             'Database = Database to Use
'RETURNS: True if successful,false otherwise
'EXAMPLE: CreateSQLServerDSN "MyDSN","MyServer","MyDatabase"

Dim sAttributes As String

sAttributes = "DSN=" & DSNName & Chr(0)
sAttributes = sAttributes & "Server=" & ServerName & Chr(0)
sAttributes = sAttributes & "Database=" & Database & Chr(0)
CreateSQLServerDSN = CreateDSN("SQL Server", sAttributes)
Exit Function
lblErr:
    Err.Clear
On Error GoTo lblErr2
    CreateSQLServerDSN = CreateDSN16bit("SQL Server", sAttributes)
Exit Function
lblErr2:
    MsgBox Err.Description & vbCrLf & " Error Number= " & Err.Number, vbCritical + vbOKOnly, "Error"
    Err.Clear
End Function

Public Function CreateAccessDSN(DSNName As String, _
  DatabaseFullPath As String) As Boolean
On Error GoTo lblErr

'PURPOSE: 'CREATES A SYSTEM DSN FOR AN ACCESS DATABASE
'PARAMETERS: 'DSNName = DSN Name
             'DatabaseFullPath = Full Path to .mdb file
'RETURNS: True if successful,false otherwise
'EXAMPLE: CreateAccessDSN "MyDSN","C:\MyDb.mdb"

    Dim sAttributes As String
    
    'TEST TO SEE IF FILE EXISTS: YOU CAN REMOVE IF YOU
    'DON'T WANT IT
    If Dir(DatabaseFullPath) = "" Then Exit Function
    
sAttributes = "DSN=" & DSNName & Chr(0)
sAttributes = sAttributes & "DBQ=" & DatabaseFullPath & Chr(0)
CreateAccessDSN = CreateDSN("Microsoft Access Driver (*.mdb)", sAttributes)
Exit Function
lblErr:
    Err.Clear
On Error GoTo lblErr2
    CreateAccessDSN = CreateDSN16bit("Microsoft Access Driver (*.mdb)", sAttributes)
Exit Function
lblErr2:
    MsgBox Err.Description & vbCrLf & " Error Number= " & Err.Number, vbCritical + vbOKOnly, "Error"
    Err.Clear
End Function

Public Function CreateDSN(Driver As String, Attributes As _
  String) As Boolean
On Error GoTo lblErr
'PURPOSE: CREATES A SYSTEM DSN
'PARAMETERS: 'Driver = DriverName
'ATTRIBUTES: 'Attributes; varies as a function
             'of the Driver
'EXAMPLE: Refer to Code Above

    CreateDSN = SQLConfigDataSource(0&, ODBC_ADD_SYS_DSN, Driver, Attributes)
Exit Function
lblErr:
    MsgBox Err.Description & vbCrLf & " Error Number= " & Err.Number, vbCritical + vbOKOnly, "Error"
    Err.Clear
End Function

Public Function CreateDSN16bit(Driver As String, Attributes As _
  String) As Boolean
On Error GoTo lblErr
'PURPOSE: CREATES A SYSTEM DSN
'PARAMETERS: 'Driver = DriverName
'ATTRIBUTES: 'Attributes; varies as a function
             'of the Driver
'EXAMPLE: Refer to Code Above

    CreateDSN16bit = SQLConfigDataSource16BIT(0&, ODBC_ADD_SYS_DSN, Driver, Attributes)
Exit Function
lblErr:
    MsgBox Err.Description & vbCrLf & " Error Number= " & Err.Number, vbCritical + vbOKOnly, "Error"
    Err.Clear
End Function



'From other functions/subroutines :

CreateAccessDSN "OTSin", App.Path & "\OTSin.mdb"

Dim recSet As Recordset, conn As Connection, StrSql As String
Set conn = New Connection
Set recSet = New Recordset

conn.Open "dsn=OTSin;Password=TheDbPassword"

StrSql = "SELECT field1,field2 FROM table1"
Set recSet = conn.Execute(StrSql)