How can we add ODBC connections without opening the Windows “ODBC Administrator” application?
Works in Windows Vista and Windows 7 RC. We are going from this:
PS C:\> Add-ODBCconnection2HKCU.ps1
Here’s my sample of my registry before I run my PowerShell script:
I’m going to add an SQL ODBC connection to my local SQL Server “Developer” database which can be use in an MS Access and/or Excel application.
First, let us take a look at a VBScript sample that does this process.
(VBScript code modify from the Microsoft Scripting Center: http://tinyurl.com/eyk9t )
VBScript Code “AddODBCline.vbs” – (see how many lines of code?)
I’m not going to dissect VBScript, but this code works fine. Now, let take a look at PowerShell script that will do the same thing… and you guessed right!! You end up using less code and easy to read.
I basically study the VBScript work and then I found some blogs about build a registry keys using PowerShell script.
Using PowerShell make it easy for administrators to access and work with Registry keys. Use the CmdLet “Get-PSDrive” to see all predefined drive you can directly navigate and work. One of these is the Registry drives: “HKCU:” and “HKLM” drives.
Then, what’s needed to build these script?
- Create a variable to hold the HKCU path (not really require)
- Use the following DOS-like commands: “CD”, “MD”.
- Use the PowerShell Cmdlet: “Set-ItemProperty” (use the Help to list available parameters)
- Then, save everything in *.ps1 file.
- Additional considerations:
- Folder names with spaces, should be enclosed in single quotes.
- Run scripts as an “Administrator”
PowerShell – Add-ODBCConnect2HKCU.ps1 (how many lines of code?)
Last check both the Registry and your Windows ODBC Administrator application to see the added connection,
Now, you can use this connections in your Office application. If you need to remove the ODBC connection(s) then use the following script:
Of course, you can keep enhancing these scripts and make them more powerful.
Have fun with PowerShell!!
Download all scripts here:http://cid-a034d6a0ddc4e64e.skydrive.live.com/embedrow.aspx/PowerShellRegistryScripts2009