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
' ########################################################################################
' 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
' ========================================================================================
' ########################################################################################
' 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
' ========================================================================================
' ########################################################################################
' 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
' ========================================================================================
' ########################################################################################
' 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
' ========================================================================================
' ########################################################################################
' 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
' ========================================================================================
' ########################################################################################
' 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
' ========================================================================================
' ########################################################################################
' 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
' ========================================================================================
' ########################################################################################
' 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
' ========================================================================================
' ########################################################################################
' 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
' ========================================================================================
' ########################################################################################
' 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
' ========================================================================================
' ########################################################################################
' 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
' ========================================================================================
' ########################################################################################
' 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
' ========================================================================================
' ########################################################################################
' 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
' ========================================================================================
' ########################################################################################
' 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
' ========================================================================================
' ########################################################################################
' Parses the Properties collection
' ########################################################################################
' 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 pProperties AS DAOProperties ' Pointer to the Properties collection
LOCAL pProperty AS DAOProperty ' Pointer to the Property object
LOCAL nCount AS LONG ' Number of objects in the collection
LOCAL i AS LONG ' Loop counter variable
' 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", %FALSE)
' Enumerate the Properties collection of the Database object.
PRINT "Database properties for " & pDatabase.Name
pProperties = pDatabase.Properties
IF ISNOTHING(pProperties) THEN EXIT TRY
' Get the number of properties
nCount = pProperties.Count
' Parse the Properties collection
FOR i = 0 TO nCount - 1
' Get a reference to the property object
pProperty = pProperties.Item(i)
IF ISOBJECT(pProperty) THEN
' Show the name of the property
PRINT pProperty.Name
pProperty = NOTHING
END IF
NEXT
pProperties = NOTHING
CATCH
STDOUT DaoGetErrorInfo(pDbEngine, OBJRESULT)
FINALLY
' Close the database
IF ISOBJECT(pDatabase) THEN pDatabase.Close
' Close the Workspace
IF ISOBJECT(pWrkMain) THEN pWrkMain.Close
END TRY
WAITKEY$
END FUNCTION
' ========================================================================================
' ########################################################################################
' Parses the Properties collection
' 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 pProperties AS DAOProperties ' Pointer to the Properties collection
LOCAL pProperty AS DAOProperty ' Pointer to the Property object
LOCAL nCount AS LONG ' Number of objects in the collection
LOCAL i AS LONG ' Loop counter variable
' 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")
' Enumerate the Properties collection of the Database object.
PRINT "Database properties for " & pDatabase.Name
pProperties = pDatabase.Properties
IF ISNOTHING(pProperties) THEN EXIT TRY
' Get the number of properties
nCount = pProperties.Count
' Parse the Properties collection
FOR i = 0 TO nCount - 1
' Get a reference to the property object
pProperty = pProperties.Item(i)
IF ISOBJECT(pProperty) THEN
' Show the name of the property
PRINT pProperty.Name
pProperty = NOTHING
END IF
NEXT
pProperties = NOTHING
CATCH
STDOUT DaoGetErrorInfo(pDbEngine, OBJRESULT)
FINALLY
' Close the database
IF ISOBJECT(pDatabase) THEN pDatabase.Close
END TRY
WAITKEY$
END FUNCTION
' ========================================================================================
' ########################################################################################
' This example creates a new QueryDef object and appends it to the QueryDefs collection in
' the current database. Then the example enumerates all the properties of the new QueryDef
' and finally deletes it.
' ########################################################################################
' 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 pNewQueryDef AS DAOQueryDef ' Pointer to the QueryDef object
LOCAL pQueryDef AS DAOQueryDef ' Pointer to the QueryDef object
LOCAL pQueryDefs AS DAOQueryDefs ' Pointer to the QueryDefs 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 query definition
pNewQueryDef = pDatabase.CreateQueryDef("This is a test")
IF ISOBJECT(pNewQueryDef) THEN
' Enumerate built-in properties of the new QueryDef
PRINT "qdfTest.Name: " & pNewQueryDef.Name
PRINT "qdfTest.DateCreated: " & AfxVarToStr(pNewQueryDef.DateCreated)
PRINT "qdfTest.LastUpdated: " & AfxVarToStr(pNewQueryDef.LastUpdated)
PRINT "qdfTest.SQL: " & pNewQueryDef.SQL
PRINT "qdfTest.ODBCTimeout: " & STR$(pNewQueryDef.ODBCTimeout)
PRINT "qdfTest.Updatable: " & STR$(pNewQueryDef.Updatable)
PRINT "qdfTest.Type: " & STR$(pNewQueryDef.Type)
PRINT "qdfTest.Connect: " & pNewQueryDef.Connect
PRINT "qdfTest.ReturnsRecords: " & STR$(pNewQueryDef.ReturnsRecords)
' Delete the QueryDef
' pQueryDefs = pDatabase.QueryDefs
' IF ISOBJECT(pQueryDefs) THEN
' pQueryDefs.Delete "This is a test"
' pQueryDefs = NOTHING
' END IF
' The following compound instruction replaces the above remed lines
pDatabase.QueryDefs.Delete("This is a test")
pNewQueryDef = NOTHING
END IF
CATCH
STDOUT DaoGetErrorInfo(pDbEngine, OBJRESULT)
FINALLY
' Close the database
IF ISOBJECT(pDatabase) THEN pDatabase.Close
END TRY
WAITKEY$
END FUNCTION
' ========================================================================================
' ########################################################################################
' This example creates a new snapshot-type Recordset object and opens it, appending it to
' the Recordsets collection in the default database. It then finds a record and displays it.
' ########################################################################################
' 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 pFields AS DAOFields ' Pointer to the Fields collection
LOCAL pField AS DAOField ' Pointer to the Field object
LOCAL vValue AS VARIANT ' Value
' 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", %dbOpenSnapshot)
IF pRecordset.RecordCount > 0 THEN
pRecordset.FindFirst("Author Like '*CA*'")
DO
IF ISTRUE pRecordset.NoMatch THEN EXIT DO
vValue = pRecordset.Collect("Author")
PRINT VARIANT$$(vValue)
pRecordset.FindNext("Author Like '*CA*'")
LOOP
END IF
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
' ========================================================================================
' ########################################################################################
' This example retrieves the version number of the database engine.
' ########################################################################################
' 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
' Create a new DBEngine object
pDbEngine = NEWCOM "DAO.DBEngine.36"
IF ISNOTHING(pDbEngine) THEN EXIT FUNCTION
' Show the version number
PRINT "Version: " & pDbEngine.Version
WAITKEY$
END FUNCTION
' ========================================================================================
' ########################################################################################
' This example enumerates the workspaces of the database engine.
' ########################################################################################
' 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 pWorkSpace AS DAOWorkSpace ' Pointer to the Workspace object
LOCAL nCount AS LONG ' Number of workspaces
LOCAL i 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
' Retrieve the number of workspaces
nCount = pWorkspaces.Count
' Enumerate the workspaces
FOR i = 0 TO nCount - 1
pWorkSpace = pWorkspaces.Item(i)
IF ISOBJECT(pWorkSpace) THEN
PRINT "Name: " & pWorkspace.Name
PRINT "UserName: " & pWorkspace.UserName
' Release the Workspace object
pWorkSpace = NOTHING
END IF
NEXT
' Release the workspaces collection
pWorkSpaces = NOTHING
' Display built-in properties.
PRINT "DBEngine Version: " & pDbEngine.Version
PRINT "DBEngine LoginTimeout: " & STR$(pDbEngine.LoginTimeout)
CATCH
STDOUT DaoGetErrorInfo(pDbEngine, OBJRESULT)
END TRY
WAITKEY$
END FUNCTION
' ========================================================================================