asked on

VB SCRIPT - setting remote registry entries

I need a simple script to setup a ODBC DNS. I got things working with .Regwrite for a local machine, but for a remote machine seems like I need to use SetStringValue.

In the following code lresult returns 2.

      'Option Explicit
'Constants
Const HKEY_CLASSES_ROOT       = &H80000000
Const HKEY_CURRENT_USER       = &H80000001
Const HKEY_LOCAL_MACHINE       = &H80000002
Const HKEY_USERS             = &H80000003
Const HKEY_CURRENT_CONFIG       = &H80000005

      Dim RegObj
      Dim SysEnv

      sComputer = "optimist"
      'Set theArgs = wscript.Arguments
'Wscript.echo theArgs(0)

Set RegObj = WScript.CreateObject("WScript.Shell")

      '***** Specify the DSN parameters *****

DataSourceName = "Northwind"
DatabaseName = "C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb"
Description = "Northwind DB"

DriverName = "Microsoft Access Driver (*.mdb)"

sPath            = "SOFTWARE\ODBC\ODBC.INI\" & DataSourceName

Set oRegistry      = GetObject("winmgmts:{impersonationLevel=impersonate}//" & sComputer & "/root/default:StdRegProv")

lResult = oRegistry.SetStringValue(HKEY_LOCAL_MACHINE, sPath, "DBQ", DatabaseName)
lResult = oRegistry.SetStringValue(HKEY_LOCAL_MACHINE, sPath, "Description", Descritption)
lResult = oRegistry.SetStringValue(HKEY_LOCAL_MACHINE, sPath, "Driver", DriverName)

If (lResult = 0) And (Err.Number = 0) Then
msgbox("Success")
Else
msgbox("lResult = " & lResult & " Err.Number =" & Err.Number)
End If

      'MsgBox DataSourceName & " DSN Created!"

      Set RegObj = Nothing
      Set SysEnv = Nothing

Smallint

Give it a shot.

Cheers

Option Explicit

Dim oShell
Dim sRemoteComputer
Dim DataSourceName
Dim DatabaseName
Dim Description
Dim DriverName
Dim sPath
Dim strResult

function CapturingCMDInfo(strCommand)

      dim oExec

      CapturingCMDInfo= ""
      Set oExec = oShell.Exec (strCommand)

      Do While Not oExec.StdOut.AtEndOfStream
            CapturingCMDInfo = oExec.StdOut.ReadAll()
            If Instr(CapturingCMDInfo, "out") > 0 Then
                  Exit Do
            End If
      Loop

end function

'***** Specify remote computer *****
sRemoteComputer = "\\geddesarmini"

'***** Specify the DSN parameters *****
DataSourceName = "Northwind"
DatabaseName = "C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb"
Description = "Northwind DB"
DriverName = "Microsoft Access Driver (*.mdb)"
sPath = "\HKLM\SOFTWARE\ODBC\ODBC.INI\" & DataSourceName

Set oShell= WScript.CreateObject("WScript.Shell")

strResult = CapturingCMDInfo("REG ADD " & sRemoteComputer & sPath &" /f")
If instr (strResult, "operation completed successfully") = 0 Then
      msgbox("Failed!!!")
      Set oShell = Nothing
      WScript.quit
End If

strResult = CapturingCMDInfo("REG ADD " & sRemoteComputer & sPath &" /v DBQ /t REG_SZ /d " & chr(34) & DatabaseName & chr(34) & " /f")
If instr (strResult, "operation completed successfully") = 0 Then
      msgbox("Failed!!!")
      Set oShell = Nothing
      WScript.quit
End If

strResult = CapturingCMDInfo("REG ADD " & sRemoteComputer & sPath &" /v Description /t REG_SZ /d " & chr(34) & Description & chr(34)& " /f")
If instr (strResult, "operation completed successfully") = 0 Then
      msgbox("Failed!!!")
      Set oShell = Nothing
      WScript.quit
End If

strResult = CapturingCMDInfo("REG ADD " & sRemoteComputer & sPath &" /v Driver /t REG_SZ /d " & chr(34) & DriverName & chr(34) & " /f")
If instr (strResult, "operation completed successfully") = 0 Then
      msgbox("Failed!!!")
      Set oShell = Nothing
      WScript.quit
End If

msgbox("Success!!!")

Set oShell = Nothing

jeffg_91911

ASKER

Smallint your script works well!

Can you help me out. I'm trying to add a key to the ODBC Data Sources. This is needed so it shows up in the ODBC DSN gui.

You have already well deseerved the 500 pts A+

Option Explicit

Dim oShell
Dim sRemoteComputer
Dim DataSourceName
Dim DatabaseName
Dim Description
Dim DriverName
Dim sPath
Dim dsPath
Dim strResult
Dim Driver

function CapturingCMDInfo(strCommand)

dim oExec

CapturingCMDInfo= ""
Set oExec = oShell.Exec (strCommand)

Do While Not oExec.StdOut.AtEndOfStream
CapturingCMDInfo = oExec.StdOut.ReadAll()
If Instr(CapturingCMDInfo, "out") > 0 Then
Exit Do
End If
Loop

end function

'***** Specify remote computer *****
'sRemoteComputer = "\\geddesarmini"
sRemoteComputer = "\\optimist"

'***** Specify the DSN parameters *****
DataSourceName = "Northwind"
DatabaseName = "C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb"
Description = "Northwind DB"
DriverName = "Microsoft Access Driver (*.mdb)"
sPath = "\HKLM\SOFTWARE\ODBC\ODBC.INI\" & DataSourceName
dsPath = "\HKLM\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources\Northwind"
Driver = "C:\Windows\System32\odbcjt32.dll"
Set oShell= WScript.CreateObject("WScript.Shell")

strResult = CapturingCMDInfo("REG ADD " & sRemoteComputer & sPath &" /f")
If instr (strResult, "operation completed successfully") = 0 Then
msgbox("Failed1!!!")
Set oShell = Nothing
WScript.quit
End If

strResult = CapturingCMDInfo("REG ADD " & sRemoteComputer & sPath &" /v DBQ /t REG_SZ /d " & chr(34) & DatabaseName & chr(34) & " /f")
If instr (strResult, "operation completed successfully") = 0 Then
msgbox("Failed2!!!")
Set oShell = Nothing
WScript.quit
End If

strResult = CapturingCMDInfo("REG ADD " & sRemoteComputer & sPath &" /v Description /t REG_SZ /d " & chr(34) & Description & chr(34)& " /f")
If instr (strResult, "operation completed successfully") = 0 Then
msgbox("Failed3!!!")
Set oShell = Nothing
WScript.quit
End If

strResult = CapturingCMDInfo("REG ADD " & sRemoteComputer & sPath &" /v Driver /t REG_SZ /d " & chr(34) & Driver & chr(34) & " /f")
If instr (strResult, "operation completed successfully") = 0 Then
msgbox("Failed4!!!")
Set oShell = Nothing
WScript.quit
End If

strResult = CapturingCMDInfo("REG ADD " & sRemoteComputer & sPath &" /v DriverID /t REG_DWORD /d " & chr(34) & 25 & chr(34) & " /f")
If instr (strResult, "operation completed successfully") = 0 Then
msgbox("Failed5!!!")
Set oShell = Nothing
WScript.quit
End If

'This is the part that is messed up
strResult = CapturingCMDInfo("REG ADD " & sRemoteComputer & dsPath &" /v DriverName /t REG_SZ /d " & chr(34) & DriverName & chr(34) & " /f")
If instr (strResult, "operation completed successfully") = 0 Then
msgbox("Failed6!!!")
Set oShell = Nothing
WScript.quit
End If

msgbox("Success!!!")

Set oShell = Nothing

Smallint

Your full script. Of course will only work for Access ODBC Data Sources.

Cheers

Option Explicit

Dim oShell
Dim sRemoteComputer
Dim DataSourceName
Dim DatabaseName
Dim Description
Dim DriverName
Dim sRootPath
Dim sDriverPath

function CapturingCMDInfo(strCommand)

      dim oExec
      'msgbox strCommand
      CapturingCMDInfo= ""
      Set oExec = oShell.Exec (strCommand)

      Do While Not oExec.StdOut.AtEndOfStream
            CapturingCMDInfo = oExec.StdOut.ReadAll()
            If Instr(CapturingCMDInfo, "out") > 0 Then
                  Exit Do
            End If
      Loop

end function

Sub SetRemoteRegValue(sRemoteComputer,sPath,sKey,sDataType,sData)

      Dim sResult

      Select case sDataType

            case ""
                  sResult = CapturingCMDInfo("REG ADD " & chr(34) & sRemoteComputer & sPath & chr(34) &" /f")

            case "REG_SZ"
                  sResult = CapturingCMDInfo("REG ADD " & chr(34) & sRemoteComputer & sPath & chr(34) &" /v " & sKey & " /t REG_SZ /d " & chr(34) & sData & chr(34) & " /f")

            case "REG_DWORD"
                  sResult = CapturingCMDInfo("REG ADD " & chr(34) & sRemoteComputer & sPath & chr(34) &" /v " & sKey & " /t REG_DWORD /d " & sData & " /f")

            'This case is not complete because there are more key types... but enough for our purposes

      end select

      If instr (sResult, "operation completed successfully") = 0 Then
            msgbox("Failed Setting " & sPath & "\" & sKey & "!!!" )
            Set oShell = Nothing
            WScript.quit
      end if

end Sub

'***** Specify remote computer *****
sRemoteComputer = "\\optimist"

Set oShell= WScript.CreateObject("WScript.Shell")

'***** Specify the DSN parameters *****
DataSourceName = "Northwind"
DatabaseName = "C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb"
Description = "Northwind DB"
sRootPath = "\HKLM\SOFTWARE\ODBC\ODBC.INI\"

'Be carefully because is language dependant
DriverName = "Microsoft Access Driver (*.mdb)"

'Firts of all let's find where Access Driver is located, because is installation dependant
sDriverPath = oShell.RegRead("HKLM\SOFTWARE\ODBC\ODBCINST.INI\" & DriverName & "\Driver")

'Here we go!
call SetRemoteRegValue(sRemoteComputer,sRootPath & "ODBC Data Sources" ,"TestKK","REG_SZ",DriverName)
call SetRemoteRegValue(sRemoteComputer,sRootPath & DataSourceName,"","","")
call SetRemoteRegValue(sRemoteComputer,sRootPath & DataSourceName,"DBQ","REG_SZ",DatabaseName)
call SetRemoteRegValue(sRemoteComputer,sRootPath & DataSourceName,"Driver","REG_SZ",sDriverPath)
call SetRemoteRegValue(sRemoteComputer,sRootPath & DataSourceName,"Description","REG_SZ",Description)
call SetRemoteRegValue(sRemoteComputer,sRootPath & DataSourceName,"DriverId","REG_DWORD","25")
call SetRemoteRegValue(sRemoteComputer,sRootPath & DataSourceName,"FIL","REG_SZ","MS Access;")
call SetRemoteRegValue(sRemoteComputer,sRootPath & DataSourceName,"SafeTransactions","REG_DWORD","0")
call SetRemoteRegValue(sRemoteComputer,sRootPath & DataSourceName,"UID","REG_SZ","")
call SetRemoteRegValue(sRemoteComputer,sRootPath & DataSourceName & "\Engines","","","")
call SetRemoteRegValue(sRemoteComputer,sRootPath & DataSourceName & "\Engines\Jet","","","")

'These values are by default when installing ODBC Access source, modify at your needings
call SetRemoteRegValue(sRemoteComputer,sRootPath & DataSourceName & "\Engines\Jet","ImplicitCommitSync","REG_SZ","")
call SetRemoteRegValue(sRemoteComputer,sRootPath & DataSourceName & "\Engines\Jet","MaxBufferSize","REG_DWORD","2048")
call SetRemoteRegValue(sRemoteComputer,sRootPath & DataSourceName & "\Engines\Jet","PageTimeout","REG_DWORD","5")
call SetRemoteRegValue(sRemoteComputer,sRootPath & DataSourceName & "\Engines\Jet","Threads","REG_DWORD","3")
call SetRemoteRegValue(sRemoteComputer,sRootPath & DataSourceName & "\Engines\Jet","UserCommitSync","REG_SZ","Yes")

msgbox("Success!!!")

Set oShell = Nothing

Smallint

Ops, bug, i went too fast. I was taking driver location from local computer!

change this line

sDriverPath = oShell.RegRead("HKLM\SOFTWARE\ODBC\ODBCINST.INI\" & DriverName & "\Driver")

with

sTemp = CapturingCMDInfo("REG QUERY " & chr(34) & sRemoteComputer & "\HKLM\SOFTWARE\ODBC\ODBCINST.INI\" & DriverName & chr(34) &" /v Driver")
aTemp = Split(sTemp,"REG_SZ")
if Ubound(aTemp) <> 1 then
Msgbox "Failed querying Access Driver Location"
wscript.quit
end if
sDriverPath = mid(trim(aTemp(1)),1,Len(trim(aTemp(1)))-4)

ASKER CERTIFIED SOLUTION

Smallint

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial