VB Script | ADODB Connection

August 23, 2009

If you’re scripting and looking to retrieve data from a relational or non-relational database this post should help. Whether you’re retrieving data from Microsoft or another database provider you will need the following API to interface with the database(s) : (A)ctiveX (D)ata (O)bjects

I put (2) examples together. . . 1st w/ Microsoft SQL Connection. . . 2nd w/ LDAP which will build on what you learn from the SQL example.

IMPORTANT – You are required to know connection credentials and be familiar with the data schema. If you don’t, consult with your database admin to gather these requirements.

Here is a VB Script example for connecting to a Microsoft SQL Server v2005 via ADODB API, and then retrieving the row output with a record set (rs) array. You can extend this in the user-defined sections (Highlighted in Blue)

Details of the following code :

  1. You will need access to the server’s file system via FSO (FileSystem Object). With FSO you can create new or access existing files. Do this by setting the FSO variable to the following.
  2. Declare the connection (con), command (com) and record set (rs) variables via DIM statement
  3. Set the connection variable (var)
  4. Set the command var
  5. Set the con properties : Provider. Connection String.
  6. Open the con
  7. Set com property to con : ActiveConnection
  8. Set com property with SQL query : CommandText
  9. Execute com and set results to rs array; cycle through records to rs array until end-of-file (EOF) is met; then end loop via Wend
  10. Close rs array

 VB Script for Microsoft SQL ADO Connection

Set fso = CreateObject(“Scripting.FileSystemObject”)
Dim con, com, rs

Set con = CreateObject(“ADODB.Connection”)
Set com = CreateObject(“ADODB.Command”)
con.Provider = “SQLOLEDB”
con.ConnectionString = “Driver={SQL Server};Server=[server name];Database=[database name];UID=[username];PWD=[password]
con.Open

Set com.ActiveConnection = con
   com.CommandText = “Select [field 1], . . . , [field n] from [table]”

Set rs = com.Execute

rs.MoveFirst

counter = 1

While NOT rs.EOF

[user defined logic]

rs.MoveNext

counter = counter + 1

Wend

rs.close

Once you have the data stored within the record set you can begin building your vbscript logic to determine what you want to do with the data stored within the record set (rs). This should get you rolling. . . If you have any questions about the above script or need help moving to the next step, let me know.

VB Script for Lightweight Directory Access Protocol Connection

Const LDAPSource = “LDAP://

Dim con, com, rs
    Set con = CreateObject(“ADODB.Connection”)
    Set com = CreateObject(“ADODB.Command”)
    con.Provider = “ADsDSOObject”
  con.Properties(“User ID”) = “[username]”
  con.Properties(“Password”) = “[password]
    con.Open

Set com.ActiveConnection = con
        com.CommandText = “Select [field 1], . . . , [field n] from ‘” & LDAPSource & “‘ where [condition(s)]

[ . .  record set logic . . ]

Don’t forget to close the record set logic . . .

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: