Scripts to work with Databases

Adding a New Record to a Database
Clearing a Database Table
Connecting to an ADO Database
Deleting a Record from a Recordset
Finding a Record in a Recordset
Updating Records in a Recordset

Adding a New Record to a Database

Demonstration script that retrieves sound card information for a computer, and then saves that information to an ADO database with the DSN "Inventory."

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "DSN=Inventory;"
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM Hardware" , objConnection, _
    adOpenStatic, adLockOptimistic
Set colSoundCards = GetObject("winmgmts:").ExecQuery _
    ("Select * from Win32_SoundDevice")
For Each objSoundCard in colSoundCards
    objRecordset.AddNew
    objRecordset("ComputerName") = objSoundCard.SystemName
    objRecordset("Manufacturer") = objSoundCard.Manufacturer
    objRecordset("ProductName") = objSoundCard.ProductName
    objRecordset.Update
Next
objRecordset.Close
objConnection.Close

Clearing a Database Table

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "DSN=Inventory;"
objRecordset.CursorLocation = adUseClient
objRecordset.Open "Delete * FROM Hardware" , objConnection, _
    adOpenStatic, adLockOptimistic
objConnection.Close

Connecting to an ADO Database

Demonstration script that connects to an ADO database with the DSN "Inventory."

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "DSN=Inventory;"
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM Hardware" , objConnection, _
    adOpenStatic, adLockOptimistic
objRecordset.Close
objConnection.Close

Deleting a Record from a Recordset

Demonstration script that deletes the record for a computer named WebServer from an ADO database with the DSN "Inventory."

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "DSN=Inventory;"
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM Hardware" , objConnection, _
    adOpenStatic, adLockOptimistic
strSearchCriteria = "ComputerName = 'WebServer'"
objRecordSet.Find strSearchCriteria
objRecordset.Delete
objRecordset.Close
objConnection.Close

Finding a Record in a Recordset

Searches an ADO database with a DSN of "Inventory" looking for a specific computer record.

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "DSN=Inventory;"
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM Hardware" , objConnection, _
    adOpenStatic, adLockOptimistic
strSearchCriteria = "ComputerName = 'WebServer'"
objRecordSet.Find strSearchCriteria
If objRecordset.EOF Then 
    Wscript.Echo "Record cannot be found."
Else
    Wscript.Echo "Record found."
End If
objRecordset.Close
objConnection.Close

Updating Records in a Recordset

Demonstration script that updates sound card information for a computer named Webserver. Requires an ADO Database with a DSN of "Inventory."

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "DSN=Inventory;"
objRecordset.CursorLocation = adUseClient
objRecordset.Open "SELECT * FROM Hardware" , objConnection, _
    adOpenStatic, adLockOptimistic
strSearchCriteria = "ComputerName = 'WebServer'"
objRecordSet.Find strSearchCriteria
Set colSoundCards = GetObject("winmgmts:").ExecQuery _
    ("Select * from Win32_SoundDevice")
For Each objSoundCard in colSoundCards
    objRecordset("ComputerName") = objSoundCard.SystemName
    objRecordset("Manufacturer") = objSoundCard.Manufacturer
    objRecordset("ProductName") = objSoundCard.ProductName
    objRecordset.Update
Next
objRecordset.Close
objConnection.Close