ODBC Creation

By | August 8, 2006

I needed to create ODBC connections on multiple computers, but I didn’t want to use the GUI as it would be very slow.
So, I created a script that would create they keys for me. All you have to to do input the user name!

———-COPY EVERYTHING BELOW THIS LINE———-

‘Created April 21 2006 By Chey Harden
‘This script will setup the ODBC connection for pervasive and MSSQL databases

‘Constants
Const HKEY_LOCAL_MACHINE = &H80000002

‘Variables
On Error resume next
Dim DataSourceName, DataSourceName2
Dim DatabaseName, DatabaseName2
Dim Description, Description2, Description3
Dim DriverPath, DriverPath2
Dim LastUser
Dim Server, Server2
Dim Trusted_connection
Dim DriverName
Dim oReg
Dim strComputer
Dim strKeyPath, strKeyPath2
Dim bufferSize, fetch, quote, DSN
Dim tcpPort, strDll, transOption, transHint

‘Value assignment
strComputer = “.”
DataSourceName = “Name1”  ‘Enter DSN Name
DataSourceName2 = “Name2”  ‘Enter DSN Name
DataSourceName3 = “Name3”  ‘Enter DSN Name
DataSourceName4 = “Name4”  ‘Enter DSN Name
DatabaseName = “DB Name”  ‘Database Name
DatabaseName2 = “DB Name”  ‘Database Name
DriverPath = “C:\WINNT\System32\sqlsrv32.dll”
DriverPath2 = “C:\PVSW\Bin\w3odbcci.dll”
LastUser = inputbox(“Enter Users Login Name.”)
Server =”Server” ‘ Enter Server name here
Server2 = “Server2” ‘Enter second server name here
Trusted_connection=”Yes”
Description =”ODBC DSN for the Database:” & DatabaseName
Description2 =”ODBC DSN for the Database:” & DatabaseName2
Description3 =”Pervasive ODBC Client Interface”
DriverName =”SQL Server”
DriverName2 = “Pervasive ODBC Client Interface”
strKeyPath = “SOFTWARE\ODBC\ODBC.INI\” & DataSourceName
strKeyPath2 = “SOFTWARE\ODBC\ODBC.INI\” & DataSourceName2
strKeyPath3 = “SOFTWARE\ODBC\ODBC.INI\” & DataSourceName3
strKeyPath4 = “SOFTWARE\ODBC\ODBC.INI\” & DataSourceName4
strKeyPath5 = “SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources”
bufferSize = “8”
fetch = “1”
quote = “0”
DSN = “DB Name1”    ‘ Database Name
DSN2 = “DB Name2”   ‘ Database Name
tcpPort = “1583”
strDll = “”
transOption = “”
transHint = “TCP”

Set oReg=GetObject(“winmgmts:{impersonationLevel=impersonate}!\\” &_
strComputer & “\root\default:StdRegProv”)

oReg.CreateKey HKEY_LOCAL_MACHINE,strKeyPath
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,”Database”, DatabaseName
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,”Description”, Description
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,”Driver”, DriverPath
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,”LastUser”,LastUser
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,”Server”,Server
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,”Trusted_Connection”,Trusted_connection
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath5, DataSourceName , DriverName

oReg.CreateKey HKEY_LOCAL_MACHINE,strKeyPath2
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath2,”Database”, DatabaseName2
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath2,”Description”, Description2
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath2,”Driver”, DriverPath
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath2,”LastUser”,LastUser
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath2,”Server”,Server
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath2,”Trusted_Connection”,Trusted_connection
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath5, DataSourceName2 , DriverName

oReg.CreateKey HKEY_LOCAL_MACHINE,strKeyPath3
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath3,”ArrayBufferSize”, bufferSize
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath3,”ArrayFetchOn”, fetch
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath3,”AutoDoubleQuote”, quote
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath3,”Description”,Description3
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath3,”Driver”,DriverPath2
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath3,”ServerDSN”,DSN
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath3,”ServerName”, Server2
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath3,”TCPPort”, tcpPort
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath3,”TranslationDLL”, strDll
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath3,”TranslationOption”,transOption
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath3,”TransportHint”,transHint
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath5, DataSourceName3 , DriverName2

oReg.CreateKey HKEY_LOCAL_MACHINE,strKeyPath4
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath4,”ArrayBufferSize”, bufferSize
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath4,”ArrayFetchOn”, fetch
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath4,”AutoDoubleQuote”, quote
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath4,”Description”,Description3
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath4,”Driver”,DriverPath2
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath4,”ServerDSN”,DSN2
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath4,”ServerName”, Server2
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath4,”TCPPort”, tcpPort
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath4,”TranslationDLL”, strDll
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath4,”TranslationOption”,transOption
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath4,”TransportHint”,transHint
oReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath5, DataSourceName4 , DriverName2

SetRegKeyStrValue strComputer, HKEY_LOCAL_MACHINE,  , DataSourceName , DriverName
SetRegKeyStrValue strComputer, HKEY_LOCAL_MACHINE,  , DataSourceName2 , DriverName
SetRegKeyStrValue strComputer, HKEY_LOCAL_MACHINE,  , DataSourceName3 , Discription3
SetRegKeyStrValue strComputer, HKEY_LOCAL_MACHINE,  , DataSourceName4 , Discription3

MsgBox (“Database Connections Completed”)

———-COPY EVERYTHING ABOVE THIS LINE———-

This script creates four DB connections, two for MSSQL and two for Pervasive SQL.
Feel free to remove servers, keypaths or DSN names  if you do not need four connections.
If you need other connections besides MSSQL or Pervasive SQL look in HKLM\SOFTWARE\ODBC\ODBC.INI\ and add the changes to your script.

Change your DSN Names to the DSN names you need to create. Add Server names of the servers hosting your databases.

If you have any questions about your settings, just do a search through your registry for your settings and plug them into this script!

PLEASE MAKE SURE NO WORD WRAPPING IS HAPPENING IN YOUR NOTEPAD!!!

*Make sure that all users have write access to the registry paths!

This information is provided “AS IS” with no warranties expressed or implied.

Leave a Reply

Your email address will not be published. Required fields are marked *