' Draft Scripting class 29 (9/25/2011)
' This class covers the use of ADO, and the MS JET database engine, for
adding database functionality to an app (part 2).
' (the MS JET database engine is built into all Windows XP SP3, Vista and
later.
' an update for MS JET (called MS ACE) for Office 2007 files can be found at
http://www.microsoft.com/download/en/details.aspx?id=13255
' (you'll need to install this update before you can use the "ACE provider"
to access the newest 2007 access db formats;
' then, you would use "Microsoft.ACE.OLEDB.12.0" as your provider)
' these examples use many predefined constants as part of ADO; their names
begin with the two letters "ad"
' (this is possible because of use of the VBScript loadClassInformation
command).
' Example 2:
' checking if a record with a certain value is in a table, and if not,
adding it.
' (this example uses ADODB.Connection and ADODB.RecordSet objects)
dim objConn
dim objRec
dim strProvider
dim strDBName
strProvider = "Microsoft.Jet.OLEDB.4.0" ' this indicates a MS JET (the same
as MS Access) database
strDBName = clientInformation.ScriptPath & "\" & "DBDialog.mdb" ' first try
to share the homer/DBDialog database
If Not FileExists(strDBName) Then
' it hasn't been installed, so use our own database
strDBName = clientInformation.ScriptPath & "\" & "example_database.mdb"
End If
set objConn=createObject("ADODB.Connection")
loadClassInformation objConn
objConn.Mode = adModeReadWrite ' this could also be adModeReadOnly
objConn.provider = strProvider
objConn.CursorLocation = adUseClient ' always add this line when
initializing a connection object
objConn.open strDBName
set objRec = createObject("ADODB.RecordSet")
loadClassInformation objRec
objRec.CursorType = adOpenStatic ' always add this line when initializing a
recordSet object
objRec.LockType = adLockOptimistic ' needed for updating single records in a
table
objRec.activeConnection = objConn ' assign the connection object for the
database we're using to this record set
objRec.open "tbl_Example" ' now open the table we need from the database of
our connection object
speak "table has " & objRec.recordCount & " records."
IF objRec.recordCount > 0 THEN objRec.moveFirst
objRec.Find "name='Chip Orange'"
' EOF will now be true if the "find" operation failed, otherwise we'll be
positioned on the record we found.
if not objRec.EOF then
Speak "found it"
Else ' not objRec.EOF
speak "adding new"
objRec.AddNew ' adds a new blank record
' now assign each field a value
objRec.Fields("name").Value = "Chip Orange"
objRec.Fields("phone").Value = "850-413-6314"
objRec.Fields("added").Value = now
' now save this record
objRec.Update
Speak "record added"
END IF ' not objRec.EOF
objRec.close
objConn.close
Function FileExists(ByVal sFile)
' Test whether File exists
Dim oSystem
Set oSystem = CreateObject("Scripting.FileSystemObject")
FileExists = Not oSystem.FolderExists(sFile) And oSystem.FileExists(sFile)
Set oSystem = Nothing
End Function
' Basic sequence of operations when using ADO:
' 1. Create a connection object to connect to the database.
' 2. Open the connection
' 3. Create a recordset object in order to receive data in.
' 4. Populate the recordset by opening it and passing the desired table name
or SQL statement as a parameter to open function.
' 5. Do all the desired searching/processing on the fetched data.
' 6. Commit the changes you made to the data (if any) by using Update or
UpdateBatch methods.
' 7. Close the recordset
' 8. Close the connection
' end of example 2
' Example 3:
' (this example uses ADODB.Connection and ADODB.RecordSet objects)
dim objConn
dim objRec
dim strProvider
dim strDBName
dim x
strProvider = "Microsoft.Jet.OLEDB.4.0" ' this indicates a MS JET (the same
as MS Access) database
' strDBName = clientInformation.ScriptPath & "\" & "DBDialog.mdb" ' first
try to share the homer/DBDialog database
strDBName = clientInformation.ScriptPath & "\" & "test.mdb"
If Not FileExists(strDBName) Then
' it hasn't been installed, so use our own database
strDBName = clientInformation.ScriptPath & "\" & "example_database.mdb"
End If
set objConn=createObject("ADODB.Connection")
loadClassInformation objConn
objConn.Mode = adModeReadWrite
objConn.provider = strProvider
objConn.CursorLocation=adUseClient
objConn.open strDBName
set objRec = createObject("ADODB.RecordSet")
loadClassInformation objRec
objRec.CursorType=adOpenStatic
objRec.LockType=adLockOptimistic ' needed for updating a table
objRec.activeConnection = objConn
objRec.open "tbl_Example"
speak "table opened"
' sort by name
objRec("name").Properties("Optimize") = True ' this tells ADO we'll be
working a lot with this field
speak "table optimized"
objRec.Sort = "name" ' you can specify a comma-separated list of fields, and
whether each is ascending or descending, to sort on
speak "table sorted"
objRec.moveFirst ' moves to the first record in the sorted order
' go through all the records of the table
do until objRec.EOF
' now list all the fields (their names and their values in this record)
for each x in objRec.fields
speak x.name
speak " = "
if not isNull(x.value) then speak x.value
next
' go to the next record
objRec.MoveNext
loop
objRec.close
objConn.close
Function FileExists(ByVal sFile)
' Test whether File exists
Dim oSystem
Set oSystem = CreateObject("Scripting.FileSystemObject")
FileExists = Not oSystem.FolderExists(sFile) And oSystem.FileExists(sFile)
Set oSystem = Nothing
End Function
' end of example 3
' Example 4:
' shows you how to update a group of records at once, and if there's a
problem with any of the updates, then none of them will happen.
' assume a table is open which has fields LName (last name), FName (first
name), and phone.
' Note: since this will be updating multiple records at one time, you'll
need to change the lock type on the record set (shown below) before opening
it
objRec.LockType = adLockBatchOptimistic ' needed for updating multiple
records at once
' assume the phone number area codes need to be changed for a given area
code.
' now only change records which have phone numbers in the 415 area code
' (this can be accomplished by setting a filter which only shows us records
in the 415 area code)
objRec.Filter = "phone LIKE '415*'"
' note the use of the "like" operator in the condition above; it is specific
to ADO, and is not a VBScript feature.
' now as we move through the table, we'll only see the records which meet
the filter condition
' find first such record
objRec.MoveFirst
Do While Not objRec.EOF
speak "Name: " & objRec("fname") & " " & _
objRec.fields("lname") & _
"Phone: " & objRec.fields("phone")
' change it's area code
objRec.fields("phone") = "777" & Mid(objRec.fields("phone"), 5, 11)
' go to next such phone number
objRec.MoveNext
Loop
' save all updates in a single "transaction"
objConn.BeginTrans
' anything saved after this point, may not be saved, if there's any problem;
this is a group of "all or none" updates
On Error resume next
objRec.UpdateBatch ' save all the changed records
' note the use of the .updateBatch method instead of the .update method
if err.number <> 0 then
' had a problem
call conflictHandler
else
On Error GoTo 0
' no problem, so end the transaction
objConn.CommitTrans
end if
On Error GoTo 0
objRec.close
objConn.close
sub ConflictHandler()
' change the filter to work only with records which have an update problem
objRec.Filter = adFilterConflictingRecords
' this time the filter is assigned a constant instead of a condition
' find first such error
objRec.MoveFirst
Do While Not objRec.EOF
speak "Conflict: Name: " & objRec.fields("fname") & " " &
objRec.fields("lname")
' move to next problem record
objRec.MoveNext
Loop
' undo all attempts to update
objConn.Rollback ' transaction is ended, and none of the updates were saved
end sub
' end of example 4
' further documentation on ADO can be found in the MDAC SDK from
http://www.microsoft.com/download/en/details.aspx?id=21995
' archives of these classes can be found at:
' https://www.gwmicro.com/App_Central/Developers/Interactive_Classes/
