Mental Jetsam

By Peter Finch

Archive for April 10th, 2007

Accessing an ADO Database table from VB Script

Posted by pcfinch on April 10, 2007

This is a simple bit of VB Script that will access a database via an ADO object and extract some information from a table. Simply create a file called something like “whatsmypasswordtoday.vbs” and you can double-click on it from explorer, or run from the command line, to check your password if you ever forgot it [not that that ever happens].

In this case the code is querying the users table of an Oracle database and extracting the password of the user called “bob”. More advanced versions of this script could be used to extract or insert information into the database and perform routine batch operations. Although you probably would not write an entire application in VB Script (grin), the following is a good example of now useful, quick and powerful VB script can be in automating everyday windows tasks.

Option Explicit
' on error resume next

dim CR, strConnection, oConn, oCmd, rs, record

CR = chr(13)

strConnection = "Driver={Microsoft ODBC for Oracle};Server=server.mydomain.com;UID=user;Pwd=password;"

Set oConn = CreateObject("ADODB.Connection")
Set oCmd = CreateObject("ADODB.Command")

oConn.Open strConnection
if (Err.Number <> 0) then
  MsgBox "Unable to connect to database" & CR &_
  "Error(" & Err.Number & ")" & CR &_
  Err.Description
  WScript.Quit
end if

Set oCmd.ActiveConnection = oConn
oCmd.CommandText = "SELECT * FROM USERS WHERE LOWER(USERNAME)='bob'"
set rs = oCmd.Execute

rs.MoveFirst
Do Until rs.EOF
  msgBox "password=" & rs.fields("password")
  rs.MoveNext
loop

oConn.Close
set oCmd = nothing
set oConn = nothing
Advertisements

Posted in VBScript | Leave a Comment »