Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

vbs script to create ODBC system DSN works in XP but not Windows 7

Posted on 2011-03-06
7
Medium Priority
?
5,930 Views
Last Modified: 2012-06-27
I have a vbs script (below) that creates an ODBC system DSN that's been working nicely on Windows XP for years. Unfortunately it no longer works on Windows 7. The software that uses the connection is 32 bit, and I've successfully created the appropriate 32 bit system DSN manually by running:
%windir%\SysWOW64\odbcad32.exe

Open in new window

and going through the manual steps. The registry key that gets created (manually) appears in the registry with path:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\CPMTEST

Now I need to get the script working for Windows 7 to achieve the same thing, and simply adding "Wow6432Node" to the registry path doesn't fix it. Can anyone suggest what needs to be done to fix it?
Option Explicit
'On Error Resume Next

Dim oFS, oWshNetwork, oWshShell, sUID, sPCID, SystemDrive, sUserProfile
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Init()



Dim DSName, DSDescription, DSServer,DSDatabase,DSLastUser, DSDriverPath, DSDriver
DSServer = "wwsql1"
DSLastUser = ""
DSDriverPath = "C:\WINNT\System32\SQLSRV32.dll"
DSDriver= "SQL Server"

	DSName = "CPMTEST"
	DSDescription = "CPM Test Database"
	DSDatabase = "CPMTEST"
	RegWriteX "HKLM\SOFTWARE\ODBC\ODBC.INI\" & DSName & "\Driver", DSDriverPath, "REG_SZ"
	RegWriteX "HKLM\SOFTWARE\ODBC\ODBC.INI\" & DSName & "\Description", DSDescription, "REG_SZ"
	RegWriteX "HKLM\SOFTWARE\ODBC\ODBC.INI\" & DSName & "\Server", DSServer, "REG_SZ"
	RegWriteX "HKLM\SOFTWARE\ODBC\ODBC.INI\" & DSName & "\Database", DSDatabase, "REG_SZ"
	RegWriteX "HKLM\SOFTWARE\ODBC\ODBC.INI\" & DSName & "\LastUser", DSLastUser, "REG_SZ"
	RegWriteX "HKLM\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources\" & DSName, DSDriver,"REG_SZ"

	MsgBox "CPM Test Database Setup complete.", 64, "CPM Test Database Setup"

Sub Init()
	Set oFS = CreateObject("Scripting.FileSystemObject")
	Set oWshNetwork = WScript.CreateObject("WScript.Network")
	Set oWshShell = WScript.CreateObject("WScript.Shell")
	SystemDrive = oWshShell.ExpandEnvironmentStrings("%SystemDrive%")
	sUserProfile = oWshShell.ExpandEnvironmentStrings("%UserProfile%")
	sUid = Lcase(oWshNetwork.UserName)
	sPcid = Lcase(oWshNetwork.ComputerName)
End Sub

Function RegKeyExists(ByVal Val)
 	On Error Resume Next : Err.Clear()
 	If Not Right(Val, 1) = "\" Then	Val = Val & "\"
 	oWshShell.RegRead Val
 	If Not Instr(Err.Description, "Invalid root in registry key") <> 0 Then	Err.Clear()
	RegKeyExists = CheckError()
End Function

Function RegValueExists(Val)
	On Error Resume Next : Err.Clear()
	oWshShell.RegRead Val
	RegValueExists = CheckError()
End Function

Function RegWriteX(sKey, Data, sType)
	On Error Resume Next : Err.Clear()
	oWshShell.RegWrite sKey, Data, sType
	RegWriteX = CheckError()
End Function

Function RegReadX(Val)
	On Error Resume Next : Err.Clear()
	RegReadX = ""
	RegReadX = oWshShell.RegRead(Val)
End Function

Function CheckError()
	CheckError = False
	If Err.Number = 0 Then CheckError = True
	Err.Clear()
End Function

Open in new window

0
Comment
Question by:Terry Woods
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 2

Assisted Solution

by:clayhopkins
clayhopkins earned 1900 total points
ID: 35059822
Hi Terry-

Sanity check first.  Have you tried running the script explicitly "As Administrator" by right-clicking on the script file/icon and picking "Run as Administrator" from the menu?
0
 
LVL 35

Author Comment

by:Terry Woods
ID: 35061185
I haven't done that, but there appears to be no administrator option available. I'm logged in as a user with administrator privileges, so I don't know why it's not there. Here's a screenshot of the available options: Right click context menu (no "Run as Administrator" option)
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 100 total points
ID: 35065750
I'm trying to overcome this as well.  I have a VBA function from Access that used to do the same thing. It has something to do User Account Control. I'm sort of stretched thin at the moment -- but if you or I can find it -- it will help both of us.

http://technet.microsoft.com/en-us/library/cc709691%28WS.10%29.aspx

http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_1339-Creating-DSNs-for-SQL-Server-In-depth-on-how-to-make-one.html
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 2

Assisted Solution

by:clayhopkins
clayhopkins earned 1900 total points
ID: 35069060
Try this.  Create a shortcut to the script on your desktop.  RIght-click the shortcut, then click Properties.  Click the Shortcut tab, if it's not already selected.  Click the Advanced button.  There should be a checkbox to Run As Administrator.  This should work for any application, the only exception I've seen up to this point is Office 2010 shortcuts.
0
 
LVL 35

Author Comment

by:Terry Woods
ID: 35073296
That sounded very hopeful, but the option is greyed out: Screenshot of script shortcut -> Right click context menu -> Properties -> Advanced
0
 
LVL 35

Accepted Solution

by:
Terry Woods earned 0 total points
ID: 35088939
I got it working just now, after about 8hrs of effort!

It was clear that my manually created 32-bit DSN's included "Wow6432Node" as part of the path, but it wasn't clear whether I had to specify that as part of the path when creating the registry entries. When Windows runs applications in 32-bit mode, it automatically adds "Wow6432Node" to the registry path to get values, so it would make some sense for it to be automatically added when writing to the registry too. However, it did turn out that I had to manually include Wow6432Node as part of the registry path when writing to the registry.

The functions within my script were suppressing errors, so by commenting out the lines containing "On Error Resume Next : Err.Clear()", I could see when an error occurred. This was helpful, but not the solution.

It was suggested to me that I might need to run the .vbs script with the 32-bit Windows script engine, in which case the script would need to be passed to C:\Windows\SysWOW64\wscript.exe, which is not the default engine. To achieve this, I created a shortcut to the .vbs script, alter the properties of the shortcut so the .vbs script is passed to the 32-bit script engine, like this:
 Alter the shortcut properties and add prefix C:\Windows\SysWOW64\wscript.exe and a space to the target
Changing the shortcut to point to the script engine made the "Run as administrator" appear in the context menu, and when I used that option the script worked. It turns out that it still worked when I just added wscript.exe to the target (which Windows automatically updates to C:\Windows\System32\wscript.exe upon saving the change) so the 32-bit scripting engine was not actually required. The key thing was getting the Run as Administrator option to appear.

Note that a .bat file containing:
wscript.exe "C:\myscript.vbs"
would probably work equally well, as long as it's Run As Administrator. I tried this quite early on I think, but the error suppression in my script hid the errors I needed to see.

Thanks all for your help - it did get me pointed in the right direction at least.
0
 
LVL 35

Author Closing Comment

by:Terry Woods
ID: 35126449
2 key parts of the answer I solved myself were adding wscript.exe to the shortcut, and commenting out the error suppression in the .vbs file. Thanks all... you did get me pointed in the right direction at least.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By default the complete memory dump option is disabled in windows . If we want to enable the complete memory dump for a diagnostic purpose, we have a solution for it. here we are using the registry method to enable this.
IF you are either unfamiliar with rootkits, or want to know more about them, read on ....
This Micro Tutorial will go in depth within Systems and Security in Windows 7 and will go into detail regarding Action Center, Windows Firewall, System, etc. This will be demonstrated using Windows 7 operating system.
The viewer will learn how to successfully create a multiboot device using the SARDU utility on Windows 7. Start the SARDU utility: Change the image directory to wherever you store your ISOs, this will prevent you from having 2 copies of an ISO wit…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question