DAO Examples

[José Roca]

 
Data Access Objects (DAO) provide a framework for using code to create and manipulate databases. DAO supplies a hierarchical set of objects that use the Microsoft Jet database engine to access data and database structure in:

• 
  Microsoft Jet (.MDB) databases
  
• 
  ODBC data sources, using an ODBC driver
  
• 
  Installable ISAM databases, such as dBASE®, Paradox™ and Microsoft FoxPro which the database engine can read directly
  

[José Roca]

Code Select Expand


' ########################################################################################
' AbsolutePosition and PercentPosition example
' This example positions the current record to the nth record in a dynaset-type Recordset object.
' ########################################################################################

' CSED_PBCC ' Use PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "DAO.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   LOCAL pDbEngine AS DAODBEngine         ' Pointer to the DbEnfine object
   LOCAL pDatabase AS DAODatabase         ' Pointer to the Database object
   LOCAL pRecordset AS DAORecordset       ' Pointer to the Recordset object
   LOCAL SqlStr AS WSTRING                ' SQL string
   LOCAL vRes AS VARIANT                  ' General purpose variant

   ' Create a new DBEngine object
   pDbEngine = NEWCOM "DAO.DBEngine.36"
   IF ISNOTHING(pDbEngine) THEN EXIT FUNCTION

   TRY
      ' Open the Database
      pDatabase = pDbEngine.OpenDatabase(EXE.Path$ & "BIBLIO.MDB")
      ' Open a recordset
      SqlStr = "SELECT * FROM Authors"
      pRecordset = pDatabase.OpenRecordset(SqlStr, %dbOpenDynaset, %dbOpenDynaset)
      ' Move the cursor to the last record to fetch all the records
      pRecordset.MoveLast
      ' Set the absolute position in the 11th record (is zero based)
      pRecordset.AbsolutePosition = 10
      ' Print the value of the Author field
      vRes = pRecordset.Collect("Author")
      PRINT VARIANT$$(vRes)
      PRINT "Percent position: " & STR$(pRecordset.PercentPosition) & "%"
   CATCH
      STDOUT DaoGetErrorInfo(pDbEngine, OBJRESULT)
   FINALLY
      ' Close the recordset
      IF ISOBJECT(pRecordset) THEN pRecordset.Close
      ' Close the database
      IF ISOBJECT(pDatabase) THEN pDatabase.Close
   END TRY

   WAITKEY$

END FUNCTION
' ========================================================================================


[José Roca]

Code Select Expand


' ########################################################################################
' AddNew example
' ########################################################################################

' CSED_PBCC ' Use PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "DAO.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   LOCAL pDbEngine AS DAODBEngine         ' Pointer to the DbEnfine object
   LOCAL pDatabase AS DAODatabase         ' Pointer to the Database object
   LOCAL pRecordset AS DAORecordset       ' Pointer to the Recordset object
   LOCAL SqlStr AS WSTRING                ' SQL string

   ' Create a new DBEngine object
   pDbEngine = NEWCOM "DAO.DBEngine.36"
   IF ISNOTHING(pDbEngine) THEN EXIT FUNCTION

   TRY
      ' Open the Database
      pDatabase = pDbEngine.OpenDatabase(EXE.Path$ & "BIBLIO.MDB")
      ' Open a recordset
      SqlStr = "SELECT * FROM Authors"
      pRecordset = pDatabase.OpenRecordset(SqlStr, %dbOpenDynaset)
      ' Add a new ecord
      pRecordset.AddNew
      pRecordset.Collect("Au_ID") = 999
      pRecordset.Collect("Author") = "Roca, José"
      pRecordset.Collect("Year Born") = "1950"
      pRecordset.Update %dbUpdateRegular
      PRINT "Record added"
   CATCH
      STDOUT DaoGetErrorInfo(pDbEngine, OBJRESULT)
   FINALLY
      ' Close the recordset
      IF ISOBJECT(pRecordset) THEN pRecordset.Close
      ' Close the database
      IF ISOBJECT(pDatabase) THEN pDatabase.Close
   END TRY

   WAITKEY$

END FUNCTION
' ========================================================================================


[José Roca]

Code Select Expand


' ########################################################################################
' This example shows the use of bookmarks
' ########################################################################################

' CSED_PBCC ' Use PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "DAO.INC"
#INCLUDE ONCE "OleAuto.inc"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   LOCAL pDbEngine AS DAODBEngine          ' Pointer to the DbEnfine object
   LOCAL pDatabase AS DAODatabase          ' Pointer to the Database object
   LOCAL pRecordset AS DAORecordset        ' Pointer to the Recordset object
   LOCAL SqlStr AS WSTRING                 ' SQL string
   LOCAL vRes AS VARIANT                   ' General purpose variant
   LOCAL bm AS DWORD                       ' Bookmark

   ' Create a new DBEngine object
   pDbEngine = NEWCOM "DAO.DBEngine.36"
   IF ISNOTHING(pDbEngine) THEN EXIT FUNCTION

   TRY
      ' Open the Database
      pDatabase = pDbEngine.OpenDatabase(EXE.Path$ & "BIBLIO.MDB")
      ' Open a recordset
      SqlStr = "Authors"
      pRecordset = pDatabase.OpenRecordset(SqlStr, %dbOpenTable)
      ' Set the index
      pRecordset.Index = "PrimaryKey"
      ' Fetch the first record
      pRecordset.MoveFirst
      ' Get the content of the "Author" column
      vRes = pRecordset.Collect("Author")
      PRINT VARIANT$$(vRes)
      ' Seek the 10th record
      pRecordset.Seek "=", 10
      ' Display the author's name
      vRes = pRecordset.Collect("Author")
      PRINT VARIANT$$(vRes)
      ' Get the bookmark
      bm = pRecordset.Bookmark
      ' Move to the last record
      pRecordset.MoveLast
      ' Display the author's name
      vres = pRecordset.Collect("Author")
      PRINT VARIANT$$(vRes)
      ' Return to the bookmarked record
      ' Please note the use of VARPTR to pass the address of the safe array
      ' containing the bookmark.
      IF ISTRUE bm THEN pRecordset.Bookmark = VARPTR(bm)
      ' Display the author's name
      vRes = pRecordset.Collect("Author")
      PRINT VARIANT$$(vRes)
      ' Free the safearray that contains the bookmark
      IF ISTRUE bm THEN SafeArrayDestroy(bm)
   CATCH
      STDOUT DaoGetErrorInfo(pDbEngine, OBJRESULT)
   FINALLY
      ' Close the recordset
      IF ISOBJECT(pRecordset) THEN pRecordset.Close
      ' Close the database
      IF ISOBJECT(pDatabase) THEN pDatabase.Close
   END TRY

   WAITKEY$

END FUNCTION
' ========================================================================================


[José Roca]

Code Select Expand


' ########################################################################################
' This example shows how to update a record using the undocumented Collect property.
' In the example, it is equivalent to:
'    pFields = pRecordset.Fields
'    pField = pFields.Item("Year Born")
'    pField.Value = 1950
'    pField = NOTHING
'    pFields = NOTHING
' SED_PBCC - Use the PBCC compiler
' ########################################################################################

' CSED_PBCC ' Use PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "DAO.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   LOCAL pDbEngine AS DAODBEngine         ' Pointer to the DbEngine object
   LOCAL pDatabase AS DAODatabase         ' Pointer to the Database object
   LOCAL pRecordset AS DAORecordset       ' Pointer to the Recordset object
   LOCAL SqlStr AS WSTRING                ' SQL string

   ' Create a new DBEngine object
   pDbEngine = NEWCOM "DAO.DBEngine.36"
   IF ISNOTHING(pDbEngine) THEN EXIT FUNCTION

   TRY
      ' Open the Database
      pDatabase = pDbEngine.OpenDatabase(EXE.Path$ & "BIBLIO.MDB")
      ' Open a recordset
      SqlStr = "SELECT * FROM Authors Where Author = 'Wolf, Rich'"
      pRecordset = pDatabase.OpenRecordset(SqlStr, %dbOpenDynaset)
      IF pRecordset.RecordCount > 0 AND ISTRUE pRecordset.Updatable THEN
         pRecordset.Edit
         pRecordset.Collect("Year Born") = 1950
         pRecordset.Update %dbUpdateRegular
         PRINT "Record updated"
      ELSE
         PRINT "No such author or table not updatable"
      END IF
   CATCH
      STDOUT DaoGetErrorInfo(pDbEngine, OBJRESULT)
   FINALLY
      ' Close the database
      IF ISOBJECT(pDatabase) THEN pDatabase.Close
   END TRY

   WAITKEY$

END FUNCTION
' ========================================================================================


[José Roca]

Code Select Expand


' ########################################################################################
' Container Object, Containers Collection, and Document Object, Documents Collection Example
' This example enumerates the properties of all the Container and Document objects in
' the current database.
' ########################################################################################

' CSED_PBCC ' Use PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "DAO.INC"
#INCLUDE ONCE "AfxVarToStr.inc"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   LOCAL pDbEngine AS DAODBEngine         ' Pointer to the DbEngine object
   LOCAL pDatabase AS DAODatabase         ' Pointer to the Database object
   LOCAL pContainers AS DAOContainers     ' Pointer to the Containers collection
   LOCAL pContainer AS DAOContainer       ' Pointer to a Container object
   LOCAL pDocuments AS DAODocuments       ' Pointer to the Documents collection
   LOCAL pDocument AS DAODocument         ' Pointer to a Document object
   LOCAL cCount AS LONG                   ' Number of containers
   LOCAL dCount AS LONG                   ' Number of documents
   LOCAL i AS LONG                        ' Loop counter
   LOCAL j AS LONG                        ' Loop counter

   ' Create a new DBEngine object
   pDbEngine = NEWCOM "DAO.DBEngine.36"
   IF ISNOTHING(pDbEngine) THEN EXIT FUNCTION

   TRY
      ' Open the Database
      pDatabase = pDbEngine.OpenDatabase(EXE.Path$ & "BIBLIO.MDB")
      ' Get a reference to the Containers collection
      pContainers = pDatabase.Containers
      IF ISNOTHING(pContainers) THEN EXIT TRY
      ' Retrieve the number of Containers
      cCount = pContainers.Count
      FOR i = 0 to cCount - 1     ' DAO collections are zero based
         ' Get a reference to the Container object
         pContainer = pContainers.Item(i)
         IF ISOBJECT(pContainer) THEN
            PRINT "Container Name: " & pContainer.Name
            PRINT "Container Owner: " & pContainer.Owner
            PRINT "Container User name: " & pContainer.UserName
            ' Get a reference to the Documents collection
            pDocuments = pContainer.Documents
            IF ISOBJECT(pDocuments) THEN
               ' Get the number of documents
               dCount = pDocuments.Count
               FOR j = 0 TO dCount - 1
                  ' Get a reference to the Document object
                  pDocument = pDocuments.Item(j)
                  IF ISOBJECT(pDocument) THEN
                     PRINT "Document Name: " & pDocument.Name
                     PRINT "Document Owner: " & pDocument.Owner
                     PRINT "Document User name: " & pDocument.UserName
                     PRINT "Document Date created: " & AfxVarToStr(pDocument.DateCreated)
                     PRINT "Document Date last updated: " & AfxVarToStr(pDocument.LastUpdated)
                     ' Release the Document object
                     pDocument = NOTHING
                  END IF
               NEXT
               ' Release the Documents collection
               pDocuments = NOTHING
            END IF
            ' Release the Container object
            pContainer = NOTHING
         END IF
      NEXT
      ' Release the Containers collection
      pContainers = NOTHING
   CATCH
      STDOUT DaoGetErrorInfo(pDbEngine, OBJRESULT)
   FINALLY
      ' Close the database
      IF ISOBJECT(pDatabase) THEN pDatabase.Close
   END TRY

   WAITKEY$

END FUNCTION
' ========================================================================================


[José Roca]

Code Select Expand


' ########################################################################################
' CreateDatabase example
' This example creates a new Database object and opens it (thereby appending it to the
' Databases collection) in the default Workspace object. Then enumerates the Containers
' collection and closes the new Database.
' ########################################################################################

' CSED_PBCC ' Use PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "DAO.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   LOCAL pDbEngine AS DAODBEngine         ' Pointer to the DbEngine object
   LOCAL pWorkSpaces AS DAOWorkSpaces     ' Pointer to the Workspaces collection
   LOCAL pWorkDefault AS DAOWorkSpace     ' Pointer to the default workspace object
   LOCAL pNewDatabase AS DAODatabase      ' Pointer to a new database object
   LOCAL pDatabases AS DAODatabases       ' Pointer to the Databases collection
   LOCAL pDatabase AS DAODatabase         ' Pointer to the Database object
   LOCAL pContainers AS DAOContainers     ' Pointer to the Containers collection
   LOCAL pContainer AS DAOContainer       ' Pointer to a Container object
   LOCAL nDatabases AS LONG               ' Number of databases
   LOCAL nContainers AS LONG              ' Number of containers
   LOCAL i AS LONG                        ' Loop counter
   LOCAL j AS LONG                        ' Loop counter

   ' Create a new DBEngine object
   pDbEngine = NEWCOM "DAO.DBEngine.36"
   IF ISNOTHING(pDbEngine) THEN EXIT FUNCTION

   TRY
      ' Get a reference to the Workspaces collection
      pWorkSpaces = pDbEngine.Workspaces
      IF ISNOTHING(pWorkSpaces) THEN EXIT TRY
      ' Get a reference to the default workspace
      pWorkDefault = pWorkSpaces.Item(0)
      IF ISNOTHING(pWorkDefault) THEN EXIT TRY
      ' Create a new database
      pNewDatabase = pWorkDefault.CreateDatabase("New.mdb", $dbLangGeneral)
      IF ISNOTHING(pNewDatabase) THEN EXIT TRY
      ' Get a reference to the databases collection
      pDatabases = pWorkDefault.Databases
      IF ISNOTHING(pDatabases) THEN EXIT TRY
      nDatabases = pDatabases.Count
      FOR i = 0 TO nDatabases - 1
         pDatabase = pDatabases.Item(i)
         IF ISOBJECT(pDatabase) THEN
            PRINT "Database name: " & pDatabase.Name
            ' Enumerate containers
            pContainers = pDatabase.Containers
            IF ISOBJECT(pContainers) THEN
               nContainers = pContainers.Count
               FOR j = 0 to nContainers - 1
                  ' Get a reference to the Container object
                  pContainer = pContainers.Item(j)
                  IF ISOBJECT(pContainer) THEN
                     PRINT "Container Name: " & pContainer.Name
                     ' Release the Container object
                     pContainer = NOTHING
                  END IF
               NEXT
               ' Release the Containers collection
               pContainers = NOTHING
            END IF
            ' Release the database
            pDatabase = NOTHING
         END IF
      NEXT
      pDatabases = NOTHING
      ' Release the collection
   CATCH
      STDOUT DaoGetErrorInfo(pDbEngine, OBJRESULT)
   FINALLY
      ' Close the database
      IF ISOBJECT(pNewDatabase) THEN
         pNewDatabase.Close
         pNewDatabase = NOTHING
      END IF
   END TRY

   WAITKEY$

END FUNCTION
' ========================================================================================


[José Roca]

Code Select Expand


' ########################################################################################
' This example creates a query based on the Authors table.
' ########################################################################################

' CSED_PBCC ' Use PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "DAO.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   LOCAL pDbEngine AS DAODBEngine         ' Pointer to the DbEnfine object
   LOCAL pDatabase AS DAODatabase         ' Pointer to the Database object
   LOCAL pRecordset AS DAORecordset       ' Pointer to the Recordset object
   LOCAL pQueryDef AS DAOQueryDef         ' Pointer to the QueryDef object
   LOCAL vRes AS VARIANT                  ' General purpose variant

   ' Create a new DBEngine object
   pDbEngine = NEWCOM "DAO.DBEngine.36"
   IF ISNOTHING(pDbEngine) THEN EXIT FUNCTION

   TRY
      ' Open the Database
      pDatabase = pDbEngine.OpenDatabase(EXE.Path$ & "BIBLIO.MDB")
      ' Create a query
      pQueryDef = pDatabase.CreateQueryDef("Name selection")
      IF ISNOTHING(pQueryDef) THEN EXIT TRY
      pQueryDef.SQL = "SELECT * FROM Authors WHERE Author > 'WA'"
      pRecordset = pQueryDef.OpenRecordset(%dbOpenSnapshot)
      IF ISNOTHING(pRecordset) THEN EXIT TRY
      ' While not at the end of the recordset...
      WHILE NOT pRecordset.EOF
         ' Get the content of the "Author" column
         vRes = pRecordset.Collect("Author")
         PRINT VARIANT$$(vRes)
         ' Fetch the next row
         pRecordset.MoveNext
      WEND
   CATCH
      STDOUT DaoGetErrorInfo(pDbEngine, OBJRESULT)
   FINALLY
      ' Close the recordset
      IF ISOBJECT(pRecordset) THEN pRecordset.Close
      IF ISOBJECT(pQueryDef) THEN
         ' Close the query
         pQueryDef.Close
         ' Delete the query
         pDatabase.QueryDefs.Delete("Name selection")
      END IF
      ' Close the database
      IF ISOBJECT(pDatabase) THEN pDatabase.Close
   END TRY

   WAITKEY$

END FUNCTION
' ========================================================================================


[José Roca]

Code Select Expand


' ########################################################################################
' Delete method example
' ########################################################################################

' CSED_PBCC ' Use PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "DAO.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   LOCAL pDbEngine AS DAODBEngine         ' Pointer to the DbEnfine object
   LOCAL pDatabase AS DAODatabase         ' Pointer to the Database object
   LOCAL pRecordset AS DAORecordset       ' Pointer to the Recordset object
   LOCAL SqlStr AS WSTRING                ' SQL string

   ' Create a new DBEngine object
   pDbEngine = NEWCOM "DAO.DBEngine.36"
   IF ISNOTHING(pDbEngine) THEN EXIT FUNCTION

   TRY
      ' Open the Database
      pDatabase = pDbEngine.OpenDatabase(EXE.Path$ & "BIBLIO.MDB")
      ' Open a recordset
      SqlStr = "SELECT * FROM Authors"
      pRecordset = pDatabase.OpenRecordset(SqlStr, %dbOpenDynaset)
      ' Move the cursor to the last record
      pRecordset.MoveLast
      ' Delete it
      pRecordset.Delete
      PRINT "Record deleted"
   CATCH
      STDOUT DaoGetErrorInfo(pDbEngine, OBJRESULT)
   FINALLY
      ' Close the recordset
      IF ISOBJECT(pRecordset) THEN pRecordset.Close
      ' Close the database
      IF ISOBJECT(pDatabase) THEN pDatabase.Close
   END TRY

   WAITKEY$

END FUNCTION
' ========================================================================================


[José Roca]

Code Select Expand


' ########################################################################################
' This example creates a TableDef object and a Field object, appends the Field to the
' Fields collection in the new TableDef, and appends the TableDef to the TableDefs
' collection in the current database.
' ########################################################################################

' CSED_PBCC ' Use PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "DAO.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   LOCAL pDbEngine AS DAODBEngine         ' Pointer to the DbEngine object
   LOCAL pDatabase AS DAODatabase         ' Pointer to the Database object
   LOCAL pTableDef AS DAOTableDef         ' Pointer to the TableDef object
   LOCAL pField AS DAOField               ' Pointer to the Field object
   LOCAL pFields AS DAOFields             ' Pointer to the Fields collection
   LOCAL pTableDefs AS DAOTableDefs       ' Pointer to the TableDefs collection

   ' Create a new DBEngine object
   pDbEngine = NEWCOM "DAO.DBEngine.36"
   IF ISNOTHING(pDbEngine) THEN EXIT FUNCTION

   TRY
      ' Open the Database
      pDatabase = pDbEngine.OpenDatabase(EXE.Path$ & "BIBLIO.MDB")
      ' Create a new TableDef object
      pTableDef = pDatabase.CreateTableDef("MyTable")
      IF ISOBJECT(pTableDef) THEN
         ' Create a Field object
         pField = pTableDef.CreateField("MyField", %dbDate)
         IF ISOBJECT(pField) THEN
            ' Get a reference to the Fields collection
            pFields = pTableDef.Fields
            IF ISOBJECT(pFields) THEN
               ' Append the field to the collection
               pFields.Append pField
               ' Release the Fields collection
               pFields = NOTHING
               ' Get a reference to the TableDefs collection
               pTableDefs = pDatabase.TableDefs
               IF ISOBJECT(pTableDefs) THEN
                  ' Append the TableDef to the collection
                  pTableDefs.Append pTableDef
                  ' Release the TableDefs collection
                  pTableDefs = NOTHING
                  PRINT "Table definition appended"
               END IF
            END IF
            ' Release the Field object
            pField = NOTHING
         END IF
         ' Release the TableDef object
         pTableDef = NOTHING
      END IF
   CATCH
      STDOUT DaoGetErrorInfo(pDbEngine, OBJRESULT)
   FINALLY
      ' Close the database
      IF ISOBJECT(pDatabase) THEN pDatabase.Close
   END TRY

   WAITKEY$

END FUNCTION
' ========================================================================================


[José Roca]

Code Select Expand


' ########################################################################################
' GetRows example
' ########################################################################################

' CSED_PBCC ' Use PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "DAO.INC"
#INCLUDE ONCE "AfxVarToStr.inc"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   LOCAL pDbEngine AS DAODBEngine         ' Pointer to the DbEnfine object
   LOCAL pDatabase AS DAODatabase         ' Pointer to the Database object
   LOCAL pRecordset AS DAORecordset       ' Pointer to the Recordset object
   LOCAL SqlStr AS WSTRING                ' SQL string

   ' Create a new DBEngine object
   pDbEngine = NEWCOM "DAO.DBEngine.36"
   IF ISNOTHING(pDbEngine) THEN EXIT FUNCTION

   TRY
      ' Open the Database
      pDatabase = pDbEngine.OpenDatabase(EXE.Path$ & "BIBLIO.MDB")
      ' Open a recordset
      SqlStr = "SELECT * FROM Authors"
      pRecordset = pDatabase.OpenRecordset(SqlStr, %dbOpenDynaset, %dbOpenDynaset)
      ' Get ten rows in a two-dimensional array contained in a variant
      LOCAL vRows AS VARIANT
      vRows = pRecordset.GetRows(10)
      ' Copy the contents in a Variant array
      DIM vRowsArray(0 TO 0) AS VARIANT
      vRowsArray() = vRows
      ' Calculate the lower and upper bounds of the array
      LOCAL il AS LONG, iu AS LONG
      LOCAL  jl AS LONG, ju AS LONG
      il = LBOUND(vRowsArray, 1)
      iu = UBOUND(vRowsArray, 1)
      jl = LBOUND(vRowsArray, 2)
      ju = UBOUND(vRowsArray, 2)
      ' Print the contents of the array
      LOCAL i AS LONG, j AS LONG
      FOR j = jl TO ju
         FOR i = il TO iu
            PRINT AfxVarToStr(vRowsArray(i, j))
         NEXT
         PRINT
      NEXT
   CATCH
      STDOUT DaoGetErrorInfo(pDbEngine, OBJRESULT)
   FINALLY
      ' Close the recordset
      IF ISOBJECT(pRecordset) THEN pRecordset.Close
      ' Close the database
      IF ISOBJECT(pDatabase) THEN pDatabase.Close
   END TRY

   WAITKEY$

END FUNCTION
' ========================================================================================


[José Roca]

Code Select Expand


' ########################################################################################
' This example creates a TableDef object and a Field object, appends the Field to the
' Fields collection in the new TableDef, and appends the TableDef to the TableDefs
' collection in the current database.
' ########################################################################################

' CSED_PBCC ' Use PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "DAO.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   LOCAL pDbEngine AS DAODBEngine         ' Pointer to the DbEngine object
   LOCAL pDatabase AS DAODatabase         ' Pointer to the Database object
   LOCAL pTableDef AS DAOTableDef         ' Pointer to the TableDef object
   LOCAL pField1 AS DAOField              ' Pointer to the Field object
   LOCAL pField2 AS DAOField              ' Pointer to the Field object
   LOCAL pTableDefs AS DAOTableDefs       ' Pointer to the TableDefs collection
   LOCAL pIdxPrimary AS DAOIndex          ' Pointer to the Index object
   LOCAL pIndexes AS DAOIndexes           ' Pointer to the Indexes collection
   LOCAL pFields AS DAOFields             ' Pointer to the Fields collection
   LOCAL pIndexFields AS DAOIndexFields   ' Pointer to the IndexFields collection
   LOCAL vIndexFields AS VARIANT

   ' Create a new DBEngine object
   pDbEngine = NEWCOM "DAO.DBEngine.36"
   IF ISNOTHING(pDbEngine) THEN EXIT FUNCTION

   TRY
      ' Open the Database
      pDatabase = pDbEngine.OpenDatabase(EXE.Path$ & "BIBLIO.MDB")
      ' Create a new TableDef object
      pTableDef = pDatabase.CreateTableDef("MyTable")
      IF ISOBJECT(pTableDef) THEN
         ' Create a Field object
         pField1 = pTableDef.CreateField("Field1", %dbLong)
         ' No Null values allowed
         IF ISOBJECT(pField1) THEN pField1.Required = %VARIANT_TRUE
         ' Create another Field object
         pField2 = pTableDef.CreateField("Field2", %dbLong)
         ' Create another Field object
         IF ISOBJECT(pField2) THEN pField2.Required = %VARIANT_TRUE
         ' Get a reference to the Fields collection
         pFields = pTableDef.Fields
         IF ISOBJECT(pFields) THEN
            ' Append the fields to the collection
            IF ISOBJECT(pField1) THEN pFields.Append pField1
            IF ISOBJECT(pField2) THEN pFields.Append pField2
            ' Release the Fields collection
            pFields = NOTHING
            ' Get a reference to the TableDefs collection
            pTableDefs = pDatabase.TableDefs
            IF ISOBJECT(pTableDefs) THEN
               ' Append the TableDef to the collection
               pTableDefs.Append pTableDef
               ' Release the TableDefs collection
               pTableDefs = NOTHING
            END IF
         END IF
         ' Release the Field objects
         pField1 = NOTHING
         pField2 = NOTHING
         ' Create primary index for those two fields.
         pIdxPrimary = pTableDef.CreateIndex("MyIndex")
         IF ISOBJECT(pIdxPrimary) THEN
            pIdxPrimary.Primary = %VARIANT_TRUE
            ' Create two field objects
            pField1 = pTableDef.CreateField("Field1")
            pField2 = pTableDef.CreateField("Field2")
            ' Get a reference to the fields collection
            vIndexFields = pIdxPrimary.Fields
            pIndexFields = vIndexFields
            vIndexFields = EMPTY
            IF ISOBJECT(pIndexFields) THEN
               ' Append the fields to the collection
               pIndexFields.Append pField1
               pIndexFields.Append pField2
               ' Release the IndexFields collection
               pIndexFields = NOTHING
            END IF
            ' Release the Field objects
            pField1 = NOTHING
            pField2 = NOTHING
            ' Get a reference to the Indexes collection
            pIndexes = pTableDef.Indexes
            IF ISOBJECT(pIndexes) THEN
               pIndexes.Append pIdxPrimary
               pIdxPrimary = NOTHING
               PRINT "Index created"
            END IF
            ' Release the primary index object
            pIdxPrimary = NOTHING
         END IF
         ' Release the TableDef object
         pTableDef = NOTHING
      END IF
   CATCH
      STDOUT DaoGetErrorInfo(pDbEngine, OBJRESULT)
   FINALLY
      ' Close the database
      IF ISOBJECT(pDatabase) THEN pDatabase.Close
   END TRY

   WAITKEY$

END FUNCTION
' ========================================================================================


[José Roca]

Code Select Expand


' ########################################################################################
' Creates a recordset and parses it.
' ########################################################################################

' CSED_PBCC ' Use PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "DAO.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   LOCAL pDbEngine AS DAODBEngine         ' Pointer to the DbEngine object
   LOCAL pWrkMain AS DAOWorkspace         ' Pointer to the main Workspace object
   LOCAL pDatabase AS DAODatabase         ' Pointer to the Database object
   LOCAL pRecordset AS DAORecordset       ' Pointer to the Recordset object
   LOCAL SqlStr AS WSTRING                ' SQL string
   LOCAL vRes AS VARIANT                  ' General purpose variant

   ' Create a new DBEngine object
   pDbEngine = NEWCOM "DAO.DBEngine.36"
   IF ISNOTHING(pDbEngine) THEN EXIT FUNCTION

   TRY
      ' Create Microsoft Jet Workspace object
      pWrkMain = pDBEngine.CreateWorkSpace("", "admin", "", %dbUseJet)
      ' Open the Database
      pDatabase = pWrkMain.OpenDatabase(EXE.Path$ & "BIBLIO.MDB")
      ' Open a recordset
      SqlStr = "SELECT * FROM Authors ORDER BY Author"
      pRecordset = pDatabase.OpenRecordset(SqlStr, %dbOpenDynaset, %dbReadOnly)
      ' While not at the end of the recordset...
      WHILE NOT pRecordset.EOF
         ' Get the content of the "Author" column
         vRes = pRecordset.Collect("Author")
         PRINT VARIANT$$(vRes)
         ' Fetch the next row
         pRecordset.MoveNext
      WEND
   CATCH
      STDOUT DaoGetErrorInfo(pDbEngine, OBJRESULT)
   FINALLY
      ' Close the recordset
      IF ISOBJECT(pRecordset) THEN pRecordset.Close
      ' Close the Workspace
      IF ISOBJECT(pWrkMain) THEN pWrkMain.Close
      ' Close the database
      IF ISOBJECT(pDatabase) THEN pDatabase.Close
   END TRY

   WAITKEY$

END FUNCTION
' ========================================================================================


[José Roca]

Code Select Expand


' ########################################################################################
' Creates a recordset and parses it.
' This example uses the OpenDatabase method of the DbEngine interface instead of
' a workspace.
' ########################################################################################

' CSED_PBCC ' Use PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "DAO.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   LOCAL pDbEngine AS DAODBEngine         ' Pointer to the DbEngine object
   LOCAL pDatabase AS DAODatabase         ' Pointer to the Database object
   LOCAL pRecordset AS DAORecordset       ' Pointer to the Recordset object
   LOCAL SqlStr AS WSTRING                ' SQL string
   LOCAL vRes AS VARIANT                  ' General purpose variant

   ' Create a new DBEngine object
   pDbEngine = NEWCOM "DAO.DBEngine.36"
   IF ISNOTHING(pDbEngine) THEN EXIT FUNCTION

   TRY
      ' Open the Database
      pDatabase = pDbEngine.OpenDatabase(EXE.Path$ & "BIBLIO.MDB")
      ' Open a recordset
      SqlStr = "SELECT * FROM Authors ORDER BY Author"
      pRecordset = pDatabase.OpenRecordset(SqlStr, %dbOpenDynaset, %dbReadOnly)
      ' While not at the end of the recordset...
      WHILE NOT pRecordset.EOF
         ' Get the content of the "Author" column
         vRes = pRecordset.Collect("Author")
         PRINT VARIANT$$(vRes)
         ' Fetch the next row
         pRecordset.MoveNext
      WEND
   CATCH
      STDOUT DaoGetErrorInfo(pDbEngine, OBJRESULT)
   FINALLY
      ' Close the recordset
      IF ISOBJECT(pRecordset) THEN pRecordset.Close
      ' Close the database
      IF ISOBJECT(pDatabase) THEN pDatabase.Close
   END TRY

   WAITKEY$

END FUNCTION
' ========================================================================================


[José Roca]

Code Select Expand


' ########################################################################################
' This example opens a table-type Recordset and selects an index for the Recordset. By
' setting an index, the Microsoft Jet database engine returns records in the order
' specified by the index. Without an index, table-type Recordset objects return records
' from the database table in no particular order.
' ########################################################################################

' CSED_PBCC ' Use PBCC compiler
#COMPILE EXE
#DIM ALL
#INCLUDE ONCE "DAO.INC"

' ========================================================================================
' Main
' ========================================================================================
FUNCTION PBMAIN

   LOCAL pDbEngine AS DAODBEngine          ' Pointer to the DbEnfine object
   LOCAL pDatabase AS DAODatabase          ' Pointer to the Database object
   LOCAL pRecordset AS DAORecordset        ' Pointer to the Recordset object
   LOCAL vRes AS VARIANT                   ' General purpose variant

   ' Create a new DBEngine object
   pDbEngine = NEWCOM "DAO.DBEngine.36"
   IF ISNOTHING(pDbEngine) THEN EXIT FUNCTION

   TRY
      ' Open the Database
      pDatabase = pDbEngine.OpenDatabase(EXE.Path$ & "BIBLIO.MDB")
      ' Open recordset on Authors table
      pRecordset = pDatabase.OpenRecordset("Authors", %dbOpenTable)
      ' Set the index
      pRecordset.Index = "Author"
      ' While not at the end of the recordset...
      WHILE NOT pRecordset.EOF
         ' Get the content of the "Author" column
         vRes = pRecordset.Collect("Author")
         PRINT VARIANT$$(vRes)
         ' Fetch the next row
         pRecordset.MoveNext
      WEND
   CATCH
      STDOUT DaoGetErrorInfo(pDbEngine, OBJRESULT)
   FINALLY
      ' Close the recordset
      IF ISOBJECT(pRecordset) THEN pRecordset.Close
      ' Close the database
      IF ISOBJECT(pDatabase) THEN pDatabase.Close
   END TRY

   WAITKEY$

END FUNCTION
' ========================================================================================