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
Comments