Remote ODBC

I just had to write this so thought i'd share:

Sometimes you still have to use ODBC for legacy work, and all the users still have to have the ODBC installed locally, this can be done in many ways, a .reg file, an NT login script, or using VB....


Here's the project to remotely install an SQL SERVER ODBC Entry onto another machine, it uses the Registry API.  You could add to this to recurse a list of machines, or a list of ODBC installs on many machines e.t.c e.t.c, anyway maybe you'll use it maybe you wont.......

Sample Code:

To run the project you'll need the following controls on a form "frmRegConnect"



txtUserLogin - TextBox
txtServer - TextBox
txtDescription - TextBox
txtDatabaseName - TextBox
txtCompName - TextBox
cmdReadReg - Command Button

Place the following code into the form:

Option Explicit

Private Sub cmdReadReg_Click()
Dim CompName$
Dim lRes&
If txtDatabaseName = "" Then
 MsgBox "Enter Database Name", vbCritical, "Err"
 Exit Sub



Now place the following code into a module:



Option Explicit

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

Public Declare Function RegConnectRegistry Lib "advapi32.dll" Alias "RegConnectRegistryA" _
 (ByVal lpMachineName As String, ByVal hKey As Long, phkResult As Long) As Long

Public 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

Public Declare Function RegQueryValueEx Lib "advapi32.dll" Alias "RegQueryValueExA" _
 (ByVal hKey As Long, ByVal lpValueName As String, ByVal lpReserved As Long, _
 lpType As Long, lpData As Any, lpcbData As Long) As Long

Public Declare Function RegSetValueEx Lib "advapi32.dll" Alias "RegSetValueExA" _
 (ByVal hKey As Long, ByVal lpValueName As String, ByVal Reserved As Long, _
 ByVal dwType As Long, ByVal lpData As String, ByVal cbData As Long) As Long

Public Declare Function RegDeleteValue Lib "advapi32.dll" Alias "RegDeleteValueA" _
 (ByVal hKey As Long, ByVal lpValueName As String) As Long

Public Declare Function RegCreateKeyEx Lib "advapi32.dll" Alias "RegCreateKeyExA" _
 (ByVal hKey As Long, ByVal lpSubKey As String, ByVal Reserved As Long, _
 ByVal lpClass As String, ByVal dwOptions As Long, ByVal samDesired As Long, _
 lpSecurityAttributes As Any, phkResult As Long, _
 lpdwDisposition As Long) As Long

Public Declare Function RegDeleteKey Lib "advapi32.dll" Alias "RegDeleteKeyA" _
 (ByVal hKey As Long, ByVal lpSubKey As String) As Long

'Defined Keys
Public Const HKEY_LOCAL_MACHINE = &H80000002
Public Const HKEY_USERS = &H80000003
Public Const HKEY_PERFORMANCE_DATA = &H80000004
Public Const HKEY_DYN_DATA = &H80000006
Public Const HKEY_CURRENT_CONFIG = &H80000005

'Constants Definition
Public Const SYNCHRONIZE = &H100000
Public Const STANDARD_RIGHTS_ALL = &H1F0000
Public Const KEY_QUERY_VALUE = &H1
Public Const KEY_SET_VALUE = &H2
Public Const KEY_CREATE_LINK = &H20
Public Const KEY_CREATE_SUB_KEY = &H4
Public Const KEY_ENUMERATE_SUB_KEYS = &H8
Public Const KEY_EVENT = &H1
Public Const KEY_NOTIFY = &H10
Public Const READ_CONTROL = &H20000
Public Const STANDARD_RIGHTS_READ = (READ_CONTROL)
Public Const STANDARD_RIGHTS_WRITE = (READ_CONTROL)
Public Const KEY_ALL_ACCESS = ((STANDARD_RIGHTS_ALL Or KEY_QUERY_VALUE Or _
 KEY_SET_VALUE Or KEY_CREATE_SUB_KEY Or KEY_ENUMERATE_SUB_KEYS Or _
 KEY_NOTIFY Or KEY_CREATE_LINK) And (Not SYNCHRONIZE))
Public Const KEY_READ = ((STANDARD_RIGHTS_READ Or KEY_QUERY_VALUE Or _
 KEY_ENUMERATE_SUB_KEYS Or KEY_NOTIFY) And (Not SYNCHRONIZE))
Public Const KEY_EXECUTE = (KEY_READ)
Public Const KEY_WRITE = ((STANDARD_RIGHTS_WRITE Or KEY_SET_VALUE Or _
 KEY_CREATE_SUB_KEY) And (Not SYNCHRONIZE))
Public Const REG_BINARY = 3
Public Const REG_CREATED_NEW_KEY = &H1
Public Const REG_DWORD = 4
Public Const REG_DWORD_BIG_ENDIAN = 5
Public Const REG_DWORD_LITTLE_ENDIAN = 4
Public Const REG_EXPAND_SZ = 2
Public Const REG_FULL_RESOURCE_DESCRIPTOR = 9
Public Const REG_LINK = 6
Public Const REG_MULTI_SZ = 7
Public Const REG_NONE = 0
Public Const REG_SZ = 1
Public Const REG_NOTIFY_CHANGE_ATTRIBUTES = &H2
Public Const REG_NOTIFY_CHANGE_LAST_SET = &H4
Public Const REG_NOTIFY_CHANGE_NAME = &H1
Public Const REG_NOTIFY_CHANGE_SECURITY = &H8
Public Const REG_OPTION_BACKUP_RESTORE = 4
Public Const REG_OPTION_CREATE_LINK = 2
Public Const REG_OPTION_NON_VOLATILE = 0
Public Const REG_OPTION_RESERVED = 0
Public Const REG_OPTION_VOLATILE = 1
Public Const REG_LEGAL_CHANGE_FILTER = (REG_NOTIFY_CHANGE_NAME Or _
 REG_NOTIFY_CHANGE_ATTRIBUTES Or REG_NOTIFY_CHANGE_LAST_SET Or _
 REG_NOTIFY_CHANGE_SECURITY)
Public Const REG_LEGAL_OPTION = (REG_OPTION_RESERVED Or _
 REG_OPTION_NON_VOLATILE Or REG_OPTION_VOLATILE Or _
 REG_OPTION_CREATE_LINK Or REG_OPTION_BACKUP_RESTORE)

Function InstallRemoteODBC(ByVal sRemoteServer, ByVal KeyRoot As Long, _
 ByVal sRegPath As String) As Long
   Dim hKey As Long
   Dim KeyValType As Long
   Dim KeyValSize As Long
   Dim KeyVal As String
   Dim tmpVal As String
   Dim DataSourceName As String
   Dim DatabaseName As String
   Dim Description As String
   Dim DriverPath As String
   Dim DriverName As String
   Dim LastUser As String
   Dim Regional As String
   Dim Server As String
   Dim Trusted As String
   Dim Password As String
   Dim lResult As Long
   Dim res As Long
   Dim i As Integer
   Dim iChar As Integer
   Dim sChar, sWorkStr As String
   Dim bUseZero As Boolean
   Dim lReturnCode, lHive, lhRemoteRegistry As Long
   DataSourceName = frmRegConnect.txtDatabaseName
   DatabaseName = frmRegConnect.txtDatabaseName
   If frmRegConnect.txtDescription = "" Then
     Description = frmRegConnect.txtDatabaseName
   Else
     Description = frmRegConnect.txtDescription
   End If
   DriverPath = "C:\WINNT\System32\SQLSRV32.dll"
   LastUser = frmRegConnect.txtUserLogin
   Server = frmRegConnect.txtServer
   DriverName = "SQL Server"
   'Connect to the remote registry
   lReturnCode = RegConnectRegistry(sRemoteServer, KeyRoot, lhRemoteRegistry)
   'Open the remote key you wish to read
   res = RegOpenKeyEx(lhRemoteRegistry, sRegPath, 0, KEY_ALL_ACCESS, hKey)
   If res <> 0 Then GoTo Errore
   'create an empty buffer for strings
   tmpVal = String(1024, 0)
   KeyValSize = 1024
   'Begin creating your registry branch
   RegCreateKeyEx hKey, ByVal DataSourceName, 0, "REG_DWORD", REG_OPTION_NON_VOLATILE, KEY_ALL_ACCESS, ByVal 0&, lResult, lReturnCode
   res = RegOpenKeyEx(lhRemoteRegistry, sRegPath & "\" & DataSourceName, 0, KEY_ALL_ACCESS, hKey)
   'cet up the appropriate values in your key
   lResult = RegSetValueEx(hKey, "Database", 0&, REG_SZ, _
      ByVal DatabaseName, Len(DatabaseName))
   If Not lResult Then lResult = RegSetValueEx(hKey, "Description", 0&, REG_SZ, _
      ByVal Description, Len(Description))
   If Not lResult Then lResult = RegSetValueEx(hKey, "Driver", 0&, REG_SZ, _
      ByVal DriverPath, Len(DriverPath))
   If Not lResult Then lResult = RegSetValueEx(hKey, "LastUser", 0&, REG_SZ, _
      ByVal LastUser, Len(LastUser))
   If Not lResult Then lResult = RegSetValueEx(hKey, "Server", 0&, REG_SZ, _
      ByVal Server, Len(Server))
   'Create the entry in the registered list of entries
   res = RegOpenKeyEx(lhRemoteRegistry, sRegPath & "\ODBC Data Sources", 0, KEY_ALL_ACCESS, hKey)
   If Not lResult Then lResult = RegSetValueEx(hKey, ByVal DataSourceName, 0&, REG_SZ, _
      ByVal DriverName, Len(DriverName))
   If Not lResult Then lResult = RegCloseKey(hKey)
   InstallRemoteODBC = lResult
   'And we're done
   Exit Function
Errore:
   InstallRemoteODBC = lResult
   RegCloseKey hKey
End Function



Read the comments in the code for a description of whats going on, I have included some more constants to give you scope for development, you could extend the project to recurse a listbox of computer names, to install the connection to more than one for example.
ElseIf txtCompName = "" Then
 MsgBox "Enter Computer Name", vbCritical, "Err"
 Exit Sub
ElseIf txtServer = "" Then
 MsgBox "Enter Server Name", vbCritical, "Err"
 Exit Sub
ElseIf txtUserLogin = "" Then
 MsgBox "Enter Login Name", vbCritical, "Err"
 Exit Sub
End If
If InStr(1, txtCompName, "\\") > 0 Then
 CompName = txtCompName
Else
 CompName = "\\" & txtCompName
End If
lRes = InstallRemoteODBC(CompName, HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBC.INI")
If lRes Then
 MsgBox "Error Occured Installing ODBC"
Else
   MsgBox "ODBC Installed", vbOKOnly, "InstallInfo"
End If
End Sub

You might also like...

Comments

Crispin Wright

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Some people, when confronted with a problem, think "I know, I’ll use regular expressions." Now they have two problems.” - Jamie Zawinski