Mental Jetsam

By Peter Finch

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};;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 &_
end if

Set oCmd.ActiveConnection = oConn
set rs = oCmd.Execute

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

set oCmd = nothing
set oConn = nothing

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: