Use of vbScript in Database Applications

BY Selva V Pasupathy
Add a New Record to a Table.htm

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = inventory.mdb"

objRecordSet.Open "SELECT * FROM GeneralProperties", _
    objConnection, adOpenStatic, adLockOptimistic

objRecordSet.AddNew
objRecordSet("ComputerName") = "atl-ws-01"
objRecordSet("Department") = "Human Resources"
objRecordSet("OSName") = "Microsoft Windows XP Professional"
objRecordSet("OSVersion") = "5.1.2600"
objRecordSet("OSManufacturer") = "Microsoft Corporation"
objRecordSet.Update

objRecordSet.Close
objConnection.Close
   

Clear a Database Table.htm

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
   

Connect to an ADO Database.htm

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
   

Create a JET Database.htm

Set objConnection = CreateObject("ADOX.Catalog")

objConnection.Create _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = new_db.mdb"
   

Create a Table in a JET Database.htm

Set objConnection = CreateObject("ADODB.Connection")

objConnection.Open _
    "Provider= Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=new_db.mdb"

objConnection.Execute "CREATE TABLE EventTable(" & _
    "EventKey COUNTER ," & _
    "Category TEXT(50) ," & _
    "ComputerName TEXT(50) ," & _
    "EventCode INTEGER ," & _
    "RecordNumber INTEGER ," & _
    "SourceName TEXT(50) ," & _
    "TimeWritten DATETIME ," & _
    "UserName TEXT(50) ," & _
    "EventType TEXT(50) ," & _
    "Logfile TEXT(50) ," & _
    "Message MEMO)"

objConnection.Close
   

Delete a Record from a Recordset.htm

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
   

Delete Multiple Records from a Table.htm

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = inventory.mdb"

objRecordSet.Open "DELETE * FROM GeneralProperties WHERE " & _
    "Department = 'Human Resources'", _
        objConnection, adOpenStatic, adLockOptimistic

objConnection.Close
   

List the Number of Records in a Recordset.htm

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = eventlogs.mdb"

objRecordSet.Open "SELECT * FROM EventTable", _
    objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Wscript.Echo "Number of records: " & objRecordset.RecordCount

objRecordSet.Close
objConnection.Close
   

List Unique Records in a Recordset.htm

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = inventory.mdb"

objRecordSet.Open "SELECT DISTINCT OSName FROM " & _
    "GeneralProperties ORDER BY OSName", _
        objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Do Until objRecordSet.EOF
    Wscript.Echo objRecordSet.Fields.Item("OSName")
    objRecordSet.MoveNext
Loop

objRecordSet.Close
objConnection.Close
   

Open Two Recordsets.htm

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
Set objRecordSet2 = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider= Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=inventory.mdb"

objRecordSet.Open "SELECT * FROM GeneralProperties Where ComputerName = 'Computer1'", _
        objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst


objRecordSet2.Open "SELECT * FROM Storage Where ComputerName = 'Computer1'", _
        objConnection, adOpenStatic, adLockOptimistic

objRecordSet2.MoveFirst

Do Until objRecordSet.EOF
    Wscript.Echo objRecordSet.Fields.Item("ComputerName")
    Wscript.Echo objRecordSet.Fields.Item("OSName")
    objRecordSet.MoveNext
Loop

Do Until objRecordSet2.EOF
    Wscript.Echo objRecordSet2.Fields.Item("DriveName"), _
        objRecordSet2.Fields.Item("DriveDescription")
    objRecordSet2.MoveNext
Loop

objRecordSet.Close
objRecordSet2.Close
objConnection.Close
   

Save a Recordset in XML format.htm

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adPersistXML = 1

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider= Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=eventlogs.mdb"
objRecordSet.Open "SELECT * FROM EventTable", _
    objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

objRecordSet.Save "output.xml", adPersistXML

objRecordSet.Close
objConnection.Close
   

Search a Database Using a LIKE Query.htm

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = eventlogs.mdb"

objRecordSet.Open "SELECT * FROM EventTable WHERE " & _
    "Message Like '%PowerPoint%'", _
        objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Wscript.Echo "Number of records: " & objRecordset.RecordCount

objRecordSet.Close
objConnection.Close
   

Search a Database Using Numeric Criteria.htm

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = eventlogs.mdb"

objRecordSet.Open "SELECT * FROM EventTable " & _
    "WHERE EventCode = 1054", _
        objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Wscript.Echo "Number of records: " & objRecordset.RecordCount

objRecordSet.Close
objConnection.Close
   

Search a Database Using Variable Criteria.htm

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider= Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=eventlogs.mdb"

dtmDate = "#1/7/2004#"

objRecordSet.Open "SELECT * FROM EventTable Where TimeWritten = " & dtmDate, objconnection, adOpenStatic,

adLockOptimistic

objRecordSet.MoveFirst

Do Until objRecordSet.EOF
    Wscript.Echo objRecordset.Fields.Item("EventCode") & vbTab _
        & objRecordset.Fields.Item("Logfile")
    objRecordSet.MoveNext
Loop
   

Search for a Record in a Recordset.htm

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
   

Searching a Database Using String Criteria.htm

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = eventlogs.mdb"

objRecordSet.Open "SELECT * FROM EventTable " & _
    "WHERE Type = 'Error'", objConnection, adOpenStatic, _
         adLockOptimistic

objRecordSet.MoveFirst

Wscript.Echo "Number of records: " & objRecordset.RecordCount

objRecordSet.Close
objConnection.Close
   

Sort a Recordset.htm

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = eventlogs.mdb"

objRecordSet.Open "SELECT * FROM EventTable " & _
    "ORDER BY EventCode ASC", _
        objConnection, adOpenStatic, adLockOptimistic
objRecordSet.MoveFirst
Do Until objRecordSet.EOF
    Wscript.Echo objRecordSet.Fields.Item("EventCode"), objRecordSet.Fields.Item("Logfile")
    objRecordSet.MoveNext
Loop
objRecordSet.Close
objConnection.Close
   

Update a Record in a Recordset.htm

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



Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

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: