' Draft Scripting class 28 (9/18/2011) ' This class covers the use of ADO, and the MS JET database engine, for adding database functionality to an app.
' an update for MS JET (called MS ACE) for Office 2010 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 2010 access db formats) ' Example 1: ' example showing creation of either an entirely new database with a single table, or a new table within an existing database. ' (this example uses the ADOX.Catalog, ADOX.Table, and ADODB.Connection objects) ' for more information on ADOX and it's use, see http://allenbrowne.com/func-adox.html ' (this could be run at the start of your app each time, to check and see if it has the needed tables in place) ' A database is a file which can hold multiple different tables (much like a folder can hold multiple files) ' A table is the collection of data, usually in identical rows and columns like a spreadsheet. dim strDBName dim strProvider dim strConnectionString Dim objCatalog dim objTable Dim objConn strDBName = clientInformation.ScriptPath & "\" & "DBDialog.mdb" ' first try to share the homer/DBDialog database If Not FileExists(strDBName) Then ' DBDialog hasn't been installed, so create our own database strDBName = clientInformation.ScriptPath & "\" & "example_database.mdb" End If ' create a catalog object, which lets us create the database if necessary Set objCatalog = CreateObject("adox.catalog") loadClassInformation objCatalog ' create the connect string for the connection object and the catalog object to use. ' it needs to specify the type of database, the database name, and any other parameters (such as passwords) needed to open the database. ' this connection string is the only part of using ADO which can vary quite a bit depending on the type of database. strProvider = "Microsoft.Jet.OLEDB.4.0" ' this indicates a MS JET (the same as MS Access) database ' if using an Access 2010 file then install the ACE provider and use "Microsoft.ACE.OLEDB.12.0" as your provider strConnectionString = "Provider=" & strProvider & ";" & "Data Source=" & strDBName ' and this adds the file name parameter If Not FileExists(strDBName) Then ' database file does not exist. ' now create the database file speak "creating the database file " ' now create a database of this type and having this file name on error resume next objCatalog.Create strConnectionString if err.number <> 0 then msgBox "Error creating database file " & err.description & vbcr _ & strDBName msgBox "Connection string was " & strConnectionString stopScript end if else speak "database found to exist." End If on error goto 0 ' now create a connection object, used by other objects which read or write data to/from tables in the database or from the catalog. Set objConn = CreateObject("AdoDB.Connection") loadClassInformation objConn objConn.Mode = adModeShareDenyNone ' indicates other apps may also open this database in read/write mode ' if other apps aren't going to be sharing the database at the same time, then use objConn.Mode = adModeReadWrite objConn.CursorLocation = adUseClient ' always add this when initializing a Connection object for best performance objConn.provider = strProvider On Error Resume Next objConn.open strDBName If Err.Number <> 0 Then speak "Error when opening database " & strDBName & ", " & Err.Description On Error GoTo 0 stopScript End If On Error GoTo 0 ' now the database is open, assign it's connection object to a catalog object, which allows us to work with the structure of any of the tables in the database. Set objCatalog.ActiveConnection = objConn ' see if the table for our app is already in the catalog (or database) set objTable = nothing On Error Resume Next Set objTable = objCatalog.tables("tbl_Example") On Error GoTo 0 If objTable Is Nothing Then ' need to create our example table in this database ' (which could be the Homer DBDialog database, or our new empty database). speak "creating example table" Set objTable = CreateObject("adox.table") loadClassInformation objTable ' now we have an empty table object, we need to give it a name, and assign it columns (fields). objTable.Name = "tbl_Example" ' the fields of a table object are listed in a collection named it's "columns". you can see what they are, or add new fields to the collection. With objTable.Columns ' it starts out empty, so we'll add a bunch of new fields ' add a field which holds a unique ID number for each record (generally thought to be a good practice, but not required) .Append "tbl_Example_ID", adInteger ' now modify it's field properties to make it an auto-incrementing number; give it a description as well With objTable.Columns("tbl_Example_ID") Set .ParentCatalog = objCatalog .properties("Autoincrement") = True 'AutoNumber. .properties("Description") = "Automatically generated unique identifier for this record." End With ' now add a character field to hold a name .Append "Name", adVarWChar, 75 ' now modify it's properties to make the name a required field With objTable.Columns("Name") Set .ParentCatalog = objCatalog .Properties("Nullable") = False .Properties("Jet OLEDB:Allow Zero Length") = False ' must have at least 1 char end with ' now add a character field for the phone number .Append "Phone", adVarWChar, 20 ' now modify it's properties to make the phone a required field With objTable.Columns("Phone") Set .ParentCatalog = objCatalog .Properties("Nullable") = False 'Required. .Properties("Jet OLEDB:Allow Zero Length") = False ' must have at least 1 char end with ' now add a boolean field to indicate if this contact is public (can be shared with others) or private .Append "Public", adBoolean ' now modify it's properties to make Public a required field With objTable.Columns("Public") Set .ParentCatalog = objCatalog .Properties("Nullable") = False 'Required. end with ' now add two real number fields (floating point) to hold contact's location GPS coordinants .Append "Latitude", adDouble ' now modify it's properties to make the latitude not a required field With objTable.Columns("Latitude") Set .ParentCatalog = objCatalog .Properties("Nullable") = true ' not Required. end with .Append "Longitude", adDouble ' now modify it's properties to make the longitude not a required field With objTable.Columns("Longitude") Set .ParentCatalog = objCatalog .Properties("Nullable") = true ' not Required. end with ' now add a date field to show when this contact record was added .Append "added", adDate End With ' now modify it's properties to make Added a required field With objTable.Columns("Added") Set .ParentCatalog = objCatalog .Properties("Nullable") = False 'Required. end with ' now add this new table to the catalog (database) On Error Resume Next objCatalog.tables.Append objTable If Err.Number <> 0 Then Err.Raise vbObjectError + 2, clientInformation.ScriptName, "unable to create POI table: " & Err.Description Else ' err.number On Error GoTo 0 speak "table successfully created." End If ' err.number Else ' objTable is nothing ' table already exists in catalog speak "The table was found in the database." objConn.close set objTable=nothing set objConn = nothing End If ' objTable is nothing then 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 1 ' 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 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.CursorLocation = adUseClient objRec.CursorType=adOpenStatic objRec.LockType=adLockOptimistic ' needed for updating a table objRec.activeConnection = objConn objRec.open "tbl_Example" speak "table has " & objRec.recordCount & " records." IF objRec.recordCount > 0 THEN objRec.moveFirst objRec.Find "name='Chip Orange'" if not objRec.EOF then Speak "found it" Else ' not objRec.EOF speak "adding new" objRec.AddNew objRec.Fields("name").Value = "Chip Orange" objRec.Fields("phone").Value = "850-413-6314" objRec.Fields("added").Value = now 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 ' 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/
