Author Topic: ADO/ADOX Examples  (Read 48281 times)

0 Members and 1 Guest are viewing this topic.

Offline José Roca

  • Administrator
  • Hero Member
  • *****
  • Posts: 2481
  • User-Rate: +204/-0
Code: [Select]
' ########################################################################################
' Microsoft Windows
' File: ADOXEX_CreateTable3.bas
' Contents: ADOX example
' Demonstrates how to create a new table containing an autogenerate guid field.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

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

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

   LOCAL pCatalog AS ADOXCatalog
   LOCAL pTables AS ADOXTables
   LOCAL pTable AS ADOXTable
   LOCAL pColumns AS ADOXColumns
   LOCAL pColumn AS ADOXColumn
   LOCAL pProperties AS ADOProperties
   LOCAL pProperty AS ADOProperty
   LOCAL pConnection AS ADOConnection
   LOCAL vConnection AS VARIANT
   LOCAL ConStr AS WSTRING
   LOCAL HRESULT AS LONG
   LOCAL vBool AS VARIANT
   LOCAL pVariant AS IVariant

   ' // Crete an instance of the CVariant class
   pVariant = CLASS "CVariant"
   IF ISNOTHING(pVariant) THEN EXIT FUNCTION

   ' // Create a Catalog object
   pCatalog = NEWCOM "ADOX.Catalog"
   IF ISNOTHING(pCatalog) THEN EXIT FUNCTION

   ' // Create a Table object
   pTable = NEWCOM "ADOX.Table"
   IF ISNOTHING(pTable) THEN EXIT FUNCTION

   TRY
      ' // Set the ActiveConnection property of the Catalog
      pCatalog.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
      ' // Get a pointer to the Tables collection
      pTables = pCatalog.Tables
      ' // Create a new table called "Contacts3"
      pTable.Name = "Contacts4"
      ' // Sets the parent catalog
      pTable.ParentCatalog = pCatalog
      ' // Create fields and append them to the Columns collection of the new Table object
      ' // Note that in ADOX the ADO Fields are called Columns
      pColumns = pTable.Columns
      pColumns.Append "GUID_ID", %adGUID
      ' // Make an autogenerate field seting is Autogenerate property to True
      pColumn = pColumns.Item("GUID_ID")
      pProperties = pColumn.Properties
      pProperty = pProperties.Item("Jet OLEDB:AutoGenerate")
      ' // We need to pass a %VT_BOOL variant
      vBool = pVariant.FromBoolean(-1)
      pProperty.Value = vBool
      ' // Append the other fields
      pColumns.Append "FirstName", %adVarWChar
      pColumns.Append "LastName", %adVarWChar
      pColumns.Append "Phone", %adVarWChar
      pColumns.Append "Notes", %adLongVarWChar
      ' // Add the new Table to the Tables collection of the database
      pTables.Append pTable
      STDOUT "Table created"
   CATCH
      HRESULT = OBJRESULT
      ' // Display error information
      vConnection = pCatalog.ActiveConnection
      pConnection = vConnection
      STDOUT AdoGetErrorInfo(pConnection, HRESULT)
      vConnection = EMPTY
      pConnection = NOTHING
   FINALLY
      ' // Release objects and collections
      pProperty = NOTHING
      pProperties = NOTHING
      pColumn = NOTHING
      pColumns = NOTHING
      pTables = NOTHING
   END TRY

   ' // Release the main objects
   pTable = NOTHING
   pCatalog = NOTHING

   WAITKEY$

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

Offline José Roca

  • Administrator
  • Hero Member
  • *****
  • Posts: 2481
  • User-Rate: +204/-0
ADOX Example: How to delete a column from a table
« Reply #61 on: August 20, 2011, 11:51:51 PM »

The following example demonstrates how to delete a column from a table.

Code: [Select]
' ########################################################################################
' Microsoft Windows
' File: ADOXEX_DeleteColumn.bas
' Contents: ADOX example
' Demonstrates how to delete a column (Field in ADO lingo) from a table.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

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

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

   LOCAL pCatalog AS ADOXCatalog
   LOCAL pTables AS ADOXTables
   LOCAL pTable AS ADOXTable
   LOCAL pColumns AS ADOXColumns
   LOCAL pConnection AS ADOConnection
   LOCAL vConnection AS VARIANT
   LOCAL ConStr AS WSTRING
   LOCAL HRESULT AS LONG

   ' // Create a Catalog object
   pCatalog = NEWCOM "ADOX.Catalog"
   IF ISNOTHING(pCatalog) THEN EXIT FUNCTION

   TRY
      ' // Set the ActiveConnection property of the Catalog
      pCatalog.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
      ' // Get a pointer to the Tables collection
      pTables = pCatalog.Tables
      ' // Get a pointer to the table "Contacts4"
      pTable = pTables.Item("Contacts4")
      ' // Get a pointer to the Columns collection
      pColumns = pTable.Columns
      ' // Delete the "Notes" column from the collection
      ' --> change it as needed <--
      pColumns.Delete "Notes"
      STDOUT "Column deleted"
   CATCH
      HRESULT = OBJRESULT
      ' // Display error information
      vConnection = pCatalog.ActiveConnection
      pConnection = vConnection
      STDOUT AdoGetErrorInfo(pConnection, HRESULT)
      vConnection = EMPTY
      pConnection = NOTHING
   FINALLY
      ' // Release objects and collections
      pColumns = NOTHING
      pTable = NOTHING
      pTables = NOTHING
   END TRY

   ' // Release the Catalog object
   pCatalog = NOTHING

   WAITKEY$

END FUNCTION
' ========================================================================================
« Last Edit: August 21, 2011, 10:50:47 AM by José Roca »

Offline José Roca

  • Administrator
  • Hero Member
  • *****
  • Posts: 2481
  • User-Rate: +204/-0
ADOX Example: How to delete a procedure
« Reply #62 on: August 20, 2011, 11:52:38 PM »

The following example shows how to delete a procedure.

Code: [Select]
' ########################################################################################
' Microsoft Windows
' File: ADOXEX_DeleteProcedure.bas
' Contents: ADOX example
' Demonstrates how to delete a procedure.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

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

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

   LOCAL pConnection AS ADOConnection
   LOCAL pCatalog AS ADOXCatalog
   LOCAL pProcedures AS ADOXPRocedures
   LOCAL ConStr AS WSTRING

   ' // Create a Connection object
   pConnection = NEWCOM "ADODB.Connection"
   IF ISNOTHING(pConnection) THEN EXIT FUNCTION

   ' // Create a Catalog object
   pCatalog = NEWCOM "ADOX.Catalog"
   IF ISNOTHING(pCatalog) THEN EXIT FUNCTION

   TRY
      ' // Connection String - Change it if needed
      ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
      ' // Open the connection
      pConnection.Open ConStr
      ' // Set the ActiveConnection property of the Catalog
      pCatalog.putref_ActiveConnection = pConnection
      ' // Get a reference to the Procedures collection
      pProcedures = pCatalog.Procedures
      ' // Delete the "AuthorById Procedure"
      pProcedures.Delete "AuthorById"
      STDOUT "Procedure deleted"
   CATCH
      ' // Display error information
      STDOUT AdoGetErrorInfo(pConnection, OBJRESULT)
   FINALLY
      ' // Release the collection
      pProcedures = NOTHING
      ' // Close the connection
      IF pConnection.State = %adStateOpen THEN pConnection.Close
   END TRY

   ' // Release the objects
   pCatalog = NOTHING
   pConnection = NOTHING

   WAITKEY$

END FUNCTION
' ========================================================================================
« Last Edit: August 21, 2011, 10:51:23 AM by José Roca »

Offline José Roca

  • Administrator
  • Hero Member
  • *****
  • Posts: 2481
  • User-Rate: +204/-0
ADOX Example: DeleteRule Property
« Reply #63 on: August 20, 2011, 11:53:45 PM »

This example demonstrates the use of the DeleteRule property.

Code: [Select]
' ########################################################################################
' Microsoft Windows
' File: ADOXEX_DeleteRule.bas
' Contents: ADOX example
' This example demonstrates the DeleteRule property of a Key object. The code appends a
' new Table and then defines a new primary key, setting DeleteRule to adRICascade.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

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

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

   LOCAL pCatalog AS ADOXCatalog
   LOCAL pTables AS ADOXTables
   LOCAL pTable AS ADOXTable
   LOCAL pColumns AS ADOXColumns
   LOCAL pKeyColumns AS ADOXColumns
   LOCAL pKeyColumn AS ADOXColumn
   LOCAL pPrimaryKey AS ADOXKey
   LOCAL pKeys AS ADOXKeys
   LOCAL pConnection AS ADOConnection
   LOCAL vConnection AS VARIANT
   LOCAL HRESULT AS LONG

   ' // Create a Catalog object
   pCatalog = NEWCOM "ADOX.Catalog"
   IF ISNOTHING(pCatalog) THEN EXIT FUNCTION

   ' // Create a Table object
   pTable = NEWCOM "ADOX.Table"
   IF ISNOTHING(pTable) THEN EXIT FUNCTION

   ' // Create a Key object
   pPrimaryKey = NEWCOM "ADOX.Key"
   IF ISNOTHING(pPrimaryKey) THEN EXIT FUNCTION

   TRY
      ' // Set the ActiveConnection property of the Catalog
      pCatalog.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
      ' // Set the name of the new table
      pTable.Name = "NewTable"
      ' // Append a numeric and a text field to the new table
      pColumns = pTable.Columns
      pColumns.Append "NumField", %adInteger
      ' // Note: If you are using Jet 3.51 instead of 4.0 use %adVarChar
      pColumns.Append "TextField", %adVarWChar, 20
      ' // Append the new table
      pTables = pCatalog.Tables
      pTables.Append pTable
      ' // Define the primary key
      pPrimaryKey.Name = "NumField"
      pPrimaryKey.Type = %adKeyPrimary
      pPrimaryKey.RelatedTable = "Title Author"
      pKeyColumns = pPrimaryKey.Columns
      pKeyColumns.Append "NumField", %adVarWChar
      pKeyColumn = pKeyColumns.Item("NumField")
      pKeyColumn.RelatedColumn = "Au_ID"
      pPrimaryKey.DeleteRule = %adRICascade
      ' // Append the primary key
      pKeys = pTable.Keys
      pKeys.Append pPrimaryKey
   CATCH
      HRESULT = OBJRESULT
      ' // Display error information
      vConnection = pCatalog.ActiveConnection
      pConnection = vConnection
      STDOUT AdoGetErrorInfo(pConnection, HRESULT)
      vConnection = EMPTY
      pConnection = NOTHING
   FINALLY
      ' // Delete the table as this is a demo
      IF ISTRUE ISOBJECT(pTables) THEN
         pTables.Delete "NewTable"
         STDOUT "NewTable deleted"
      END IF
      ' // Release objects and collections
      pKeys = NOTHING
      pKeyColumn = NOTHING
      pKeyColumns = NOTHING
      pColumns = NOTHING
      pTable = NOTHING
      pTables = NOTHING
   END TRY

   ' // Release the main objects
   pPrimaryKey = NOTHING
   pTable = NOTHING
   pCatalog = NOTHING

   WAITKEY$

END FUNCTION
' ========================================================================================
« Last Edit: August 21, 2011, 10:52:04 AM by José Roca »

Offline José Roca

  • Administrator
  • Hero Member
  • *****
  • Posts: 2481
  • User-Rate: +204/-0
ADOX Example: Deletes a View from the collection
« Reply #64 on: August 20, 2011, 11:55:04 PM »

The following example shows how to delete a View.

Code: [Select]
' ########################################################################################
' Microsoft Windows
' File: ADOXEX_DeleteView.bas
' Contents: ADOX example
' Demonstrates how to delete a View.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

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

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

   LOCAL pConnection AS ADOConnection
   LOCAL pCatalog AS ADOXCatalog
   LOCAL pViews AS ADOXViews
   LOCAL ConStr AS WSTRING

   ' // Create a Connection object
   pConnection = NEWCOM "ADODB.Connection"
   IF ISNOTHING(pConnection) THEN EXIT FUNCTION

   ' // Create a Catalog object
   pCatalog = NEWCOM "ADOX.Catalog"
   IF ISNOTHING(pCatalog) THEN EXIT FUNCTION

   TRY
      ' // Connection String - Change it if needed
      ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
      ' // Open the connection
      pConnection.Open ConStr
      ' // Set the ActiveConnection property of the Catalog
      pCatalog.putref_ActiveConnection = pConnection
      ' // Create the command representing the View
      ' // Get a reference to the Views collection
      pViews = pCatalog.Views
      ' // Delete the "AllAuthors view"
      pViews.Delete "AllAuthors"
      STDOUT "View deleted"
   CATCH
      ' // Display error information
      STDOUT AdoGetErrorInfo(pConnection, OBJRESULT)
   FINALLY
      ' // Release the collection
      pViews = NOTHING
      ' // Close the connection
      IF pConnection.State = %adStateOpen THEN pConnection.Close
   END TRY

   ' // Release the objects
   pCatalog = NOTHING
   pConnection = NOTHING

   WAITKEY$

END FUNCTION
' ========================================================================================
« Last Edit: August 21, 2011, 10:52:50 AM by José Roca »

Offline José Roca

  • Administrator
  • Hero Member
  • *****
  • Posts: 2481
  • User-Rate: +204/-0
ADOX Example: Views Collection
« Reply #65 on: August 20, 2011, 11:55:52 PM »

The following example enumerates the Views collection.

Code: [Select]
' ########################################################################################
' Microsoft Windows
' File: ADOXEX_Fields.bas
' Contents: ADOX example
' Retrieves the fields of the "All Titles" view.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

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

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

   LOCAL pConnection AS ADOConnection
   LOCAL pCatalog AS ADOXCatalog
   LOCAL pViews AS ADOXViews
   LOCAL pView AS ADOXView
   LOCAL ConStr AS WSTRING
   LOCAL nCount AS LONG
   LOCAL i AS LONG

   ' // Create a Connection object
   pConnection = NEWCOM "ADODB.Connection"
   IF ISNOTHING(pConnection) THEN EXIT FUNCTION

   ' // Create a Catalog object
   pCatalog = NEWCOM "ADOX.Catalog"
   IF ISNOTHING(pCatalog) THEN EXIT FUNCTION

   TRY
      ' // Connection String - Change it if needed
      ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
      ' // Open the connection
      pConnection.Open ConStr
      ' // Set the ActiveConnection property of the Catalog
      pCatalog.putref_ActiveConnection = pConnection
      ' // Get a reference to the Views collection
      pViews = pCatalog.Views
      ' // Get the number of objects in the collection
      nCount = pViews.Count
      IF nCount = 0 THEN EXIT TRY
      ' // Enumerate the objects
      FOR i = 0 TO nCount - 1
         pView = pViews.Item(i)
         PRINT "Name: " pView.Name
         pView = NOTHING
      NEXT
      pView = pViews.Item("All Titles")
      PRINT "Date created: " AfxVarToStr(pView.DateCreated)
      PRINT "Date modified: " AfxVarToStr(pView.DateModified)
      pView = NOTHING
   CATCH
      ' // Display error information
      STDOUT AdoGetErrorInfo(pConnection, OBJRESULT)
   FINALLY
      ' // Release the collection
      pViews = NOTHING
      ' Close the connection
      IF pConnection.State = %adStateOpen THEN pConnection.Close
   END TRY

   ' // Release the objects
   pCatalog = NOTHING
   pConnection = NOTHING

   WAITKEY$

END FUNCTION
' ========================================================================================
« Last Edit: August 21, 2011, 10:54:35 AM by José Roca »

Offline José Roca

  • Administrator
  • Hero Member
  • *****
  • Posts: 2481
  • User-Rate: +204/-0
ADOX Example: Demonstrates how to create a new index
« Reply #66 on: August 20, 2011, 11:56:52 PM »

The following code demonstrates how to create a new index.
The index is on two columns in the table.

Code: [Select]
' ########################################################################################
' Microsoft Windows
' File: ADOXEX_Index.bas
' Contents: ADOX example
' The following code demonstrates how to create a new index.
' The index is on two columns in the table.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

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

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

   LOCAL pCatalog AS ADOXCatalog
   LOCAL pTable AS ADOXTable
   LOCAL pTables AS ADOXTables
   LOCAL pIndex AS ADOXIndex
   LOCAL pIndexes AS ADOXIndexes
   LOCAL pColumns AS ADOXColumns
   LOCAL pIdxColumns AS ADOXColumns
   LOCAL pConnection AS ADOConnection
   LOCAL vConnection AS VARIANT
   LOCAL HRESULT AS LONG

   ' // Create a Catalog object
   pCatalog = NEWCOM "ADOX.Catalog"
   IF ISNOTHING(pCatalog) THEN EXIT FUNCTION

   ' // Create a Table object
   pTable = NEWCOM "ADOX.Table"
   IF ISNOTHING(pTable) THEN EXIT FUNCTION

   ' // Create an Index object
   pIndex = NEWCOM "ADOX.Index"
   IF ISNOTHING(pIndex) THEN EXIT FUNCTION

   TRY
      ' // Set the ActiveConnection property of the Catalog
      pCatalog.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
      ' // Set the name of the table
      pTable.Name = "myTable"
      ' // Append fields to the new table
      pColumns = pTable.Columns
      pColumns.Append "Column1", %adInteger
      pColumns.Append "Column2", %adInteger
      ' // Note: If you are using Jet 3.51 instead of 4.0 use %adVarChar
      pColumns.Append "Column3", %adVarWChar, 50
      ' // Append the new table
      pTables = pCatalog.Tables
      pTables.Append pTable
      ' // Define a multicolumn index
      pIndex.Name = "multicolidx"
      pIdxColumns = pIndex.Columns
      pIdxColumns.Append "Column1", %adVarWChar
      pIdxColumns.Append "Column2", %adVarWChar
      ' // Append the index to the table
      pIndexes = pTable.Indexes
      pIndexes.Append pIndex
   CATCH
      HRESULT = OBJRESULT
      ' // Display error information
      vConnection = pCatalog.ActiveConnection
      pConnection = vConnection
      STDOUT AdoGetErrorInfo(pConnection, HRESULT)
      vConnection = EMPTY
      pConnection = NOTHING
   FINALLY
      ' // Delete the table as this is a demonstration
      IF ISTRUE ISOBJECT(pTables) THEN
         pTables.Delete "MyTable"
         STDOUT "MyTable deleted"
      END IF
      ' // Release objects and collections
      pIdxColumns = NOTHING
      pColumns = NOTHING
      pIndexes = NOTHING
      pTables = NOTHING
   END TRY

   ' // Release the main objects
   pIndex = NOTHING
   pTable = NOTHING
   pCatalog = NOTHING

   WAITKEY$

END FUNCTION
' ========================================================================================
« Last Edit: August 21, 2011, 10:55:22 AM by José Roca »

Offline José Roca

  • Administrator
  • Hero Member
  • *****
  • Posts: 2481
  • User-Rate: +204/-0
ADOX Example: Indexes Collection
« Reply #67 on: August 20, 2011, 11:57:40 PM »

The following example parses the Indexes collection of a Table and shows the name of the Primary Key and the columns that make up the index.

Code: [Select]
' ########################################################################################
' Microsoft Windows
' File: ADOXEX_Indexes.bas
' Contents: ADOX example
' Parses the Indexes collection of a Table and shows the name of the Primary Key and the
' columns that make up the index.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

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

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

   LOCAL pConnection AS ADOConnection
   LOCAL pCatalog AS ADOXCatalog
   LOCAL pTables AS ADOXTables
   LOCAL pTable AS ADOXTable
   LOCAL pIndexes AS ADOXIndexes
   LOCAL pIndex AS ADOXIndex
   LOCAL pColumns AS ADOXColumns
   LOCAL pColumn AS ADOXColumn
   LOCAL ConStr AS STRING
   LOCAL vCommand AS VARIANT
   LOCAL IdxCount AS LONG
   LOCAL ColCount AS LONG
   LOCAL i AS LONG
   LOCAL x AS LONG

   ' // Create a Connection object
   pConnection = NEWCOM "ADODB.Connection"
   IF ISNOTHING(pConnection) THEN EXIT FUNCTION

   ' // Create a Catalog object
   pCatalog = NEWCOM "ADOX.Catalog"
   IF ISNOTHING(pCatalog) THEN EXIT FUNCTION

   TRY
      ' // Connection String - Change it if needed
      ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
      ' // Open the connection
      pConnection.Open ConStr
      ' // Set the ActiveConnection property of the Catalog
      pCatalog.putref_ActiveConnection = pConnection
      ' // Get a reference to the Tables collection
      pTables = pCatalog.Tables
      ' // Get the Table object
      pTable = pTables.Item("Authors")
      ' // Get a reference to the Indexes collection
      pIndexes = pTable.Indexes
      pTable = NOTHING
      ' // Retrieve the number of objects in the collection
      IdxCount = pIndexes.Count
      IF IdxCount = 0 THEN EXIT TRY
      FOR i = 0 TO IdxCount - 1
         pIndex = pIndexes.Item(i)
         IF ISTRUE pIndex.PrimaryKey THEN
            STDOUT "Index name: " & pIndex.Name
            pColumns = pIndex.Columns
            ColCount = pColumns.Count
            FOR x = 0 TO ColCount - 1
               pColumn = pColumns.Item(i)
               STDOUT "Column name: " & pColumn.Name
               pColumn = NOTHING
            NEXT
            pColumns = NOTHING
         END IF
         pIndex = NOTHING
      NEXT
   CATCH
      ' // Display error information
      STDOUT AdoGetErrorInfo(pConnection, OBJRESULT)
   FINALLY
      ' // Release the collections
      pIndexes = NOTHING
      pTables = NOTHING
      ' // Close the connection
      IF pConnection.State = %adStateOpen THEN pConnection.Close
   END TRY

   ' // Release the objects
   pCatalog = NOTHING
   pConnection = NOTHING

   WAITKEY$

END FUNCTION
' ========================================================================================
« Last Edit: August 21, 2011, 10:56:19 AM by José Roca »

Offline José Roca

  • Administrator
  • Hero Member
  • *****
  • Posts: 2481
  • User-Rate: +204/-0
ADOX Example: Key Object
« Reply #68 on: August 20, 2011, 11:58:18 PM »

The following code demonstrates how to create a new key.
The key is on two columns in the table.

Code: [Select]
' ########################################################################################
' Microsoft Windows
' File: ADOXEX_Key.bas
' Contents: ADOX example
' The following code demonstrates how to create a new key.
' The key is on two columns in the table.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

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

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

   LOCAL pCatalog AS ADOXCatalog
   LOCAL pForeignKey AS ADOXKey
   LOCAL pColumns AS ADOXColumns
   LOCAL pColumn AS ADOXColumn
   LOCAL pTables AS ADOXTables
   LOCAL pTable AS ADOXTable
   LOCAL pKeys AS ADOXKeys
   LOCAL pConnection AS ADOConnection
   LOCAL vConnection AS VARIANT
   LOCAL HRESULT AS LONG

   ' // Create a Catalog object
   pCatalog = NEWCOM "ADOX.Catalog"
   IF ISNOTHING(pCatalog) THEN EXIT FUNCTION

   ' // Create a Key object
   pForeignKey = NEWCOM "ADOX.Key"
   IF ISNOTHING(pForeignKey) THEN EXIT FUNCTION

   TRY
      ' // Set the ActiveConnection property of the Catalog
      pCatalog.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=nwind.mdb"
      ' // Define the foreign key
      pForeignKey.Name = "CustOrder"
      pForeignKey.Type = %adKeyForeign
      pForeignKey.RelatedTable = "Customers"
      pColumns = pForeignKey.Columns
      pColumns.Append "CustomerId", %adVarWChar
      pColumn = pColumns.Item("CustomerId")
      pColumn.RelatedColumn = "CustomerId"
      pForeignKey.UpdateRule = %adRICascade
      ' // Append the foreign key
      pTables = pCatalog.Tables
      pTable = pTables.Item("Orders")
      pKeys = pTable.Keys
      pKeys.Append pForeignKey
   CATCH
      HRESULT = OBJRESULT
      ' // Display error information
      vConnection = pCatalog.ActiveConnection
      pConnection = vConnection
      STDOUT AdoGetErrorInfo(pConnection, HRESULT)
      vConnection = EMPTY
      pConnection = NOTHING
   FINALLY
      ' // Delete the key as this is a demonstration
      IF ISTRUE ISOBJECT(pTables) THEN
         pKeys.Delete "CustOrder"
         STDOUT "CustOrder deleted"
      END IF
      ' // Release objects and collections
      pKeys = NOTHING
      pTable = NOTHING
      pTables = NOTHING
      pColumn = NOTHING
      pColumns = NOTHING
   END TRY

   ' // Release the main objects
   pForeignKey = NOTHING
   pCatalog = NOTHING

   WAITKEY$

END FUNCTION
' ========================================================================================
« Last Edit: August 21, 2011, 10:57:03 AM by José Roca »

Offline José Roca

  • Administrator
  • Hero Member
  • *****
  • Posts: 2481
  • User-Rate: +204/-0
ADOX Example: Keys Collection
« Reply #69 on: August 20, 2011, 11:58:58 PM »

The following example enumerates the Keys collection of the Titles table.

Code: [Select]
' ########################################################################################
' Microsoft Windows
' File: ADOXEX_Keys.bas
' Contents: ADOX example
' Enumerates the Keys collection of the Titles table.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

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

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

   LOCAL pConnection AS ADOConnection
   LOCAL pCatalog AS ADOXCatalog
   LOCAL pTables AS ADOXTables
   LOCAL pTable AS ADOXTable
   LOCAL pKeys AS ADOXKeys
   LOCAL pKey AS ADOXKey
   LOCAL ConStr AS WSTRING
   LOCAL nCount AS LONG
   LOCAL i AS LONG

   ' // Create a Connection object
   pConnection = NEWCOM "ADODB.Connection"
   IF ISNOTHING(pConnection) THEN EXIT FUNCTION

   ' // Create a Catalog object
   pCatalog = NEWCOM "ADOX.Catalog"
   IF ISNOTHING(pCatalog) THEN EXIT FUNCTION

   TRY
      ' // Connection String - Change it if needed
      ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
      ' // Open the connection
      pConnection.Open ConStr
      ' // Set the ActiveConnection property of the Catalog
      pCatalog.putref_ActiveConnection = pConnection
      ' // Get a reference to the Tables collection
      pTables = pCatalog.Tables
      ' // Get the number of objects of the collection
      nCount = pTables.Count
      IF nCount = 0 THEN EXIT TRY
      ' // Get a pointer to the table "Titles"
      pTable = pTables.Item("Titles")
      ' // Get a pointer to the Keys collection
      pKeys = pTable.Keys
      ' // Get the number of objects in the collection
      nCount = pKeys.Count
      ' // Enumerate the objects
      FOR i = 0 TO nCount - 1
         pKey = pKeys.Item(i)
         PRINT "Name: " pKey.Name " - ";
         PRINT "Type: " pKey.Type
         pKey = NOTHING
      NEXT
   CATCH
      ' // Display error information
      STDOUT AdoGetErrorInfo(pConnection, OBJRESULT)
   FINALLY
      ' // Release the collections
      pKeys = NOTHING
      pTables = NOTHING
      ' // Close the connection
      IF pConnection.State = %adStateOpen THEN pConnection.Close
   END TRY

   ' // Release the objects
   pCatalog = NOTHING
   pConnection = NOTHING

   WAITKEY$

END FUNCTION
' ========================================================================================
« Last Edit: August 21, 2011, 10:57:50 AM by José Roca »

Offline José Roca

  • Administrator
  • Hero Member
  • *****
  • Posts: 2481
  • User-Rate: +204/-0
ADOX Example: How to retrieve parameter information for the Procedure
« Reply #70 on: August 21, 2011, 12:00:08 AM »

The following example retrieves parameters information of a procedure.

Code: [Select]
' ########################################################################################
' Microsoft Windows
' File: ADOXEX_ParameterInfo.bas
' Contents: ADOX example
' The following code demonstrates how to use the Command property with the Command object
' to retrieve parameter information for the Procedure.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

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

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

   LOCAL pConnection AS ADOConnection
   LOCAL pCommand AS ADOCommand
   LOCAL pCatalog AS ADOXCatalog
   LOCAL pProcedures AS ADOXPRocedures
   LOCAL pProcedure AS ADOXProcedure
   LOCAL pParameters AS ADOParameters
   LOCAL pParameter AS ADOParameter
   LOCAL ConStr AS WSTRING
   LOCAL vCommand AS VARIANT
   LOCAL nCount AS LONG
   LOCAL i AS LONG

   ' // Create a Connection object
   pConnection = NEWCOM "ADODB.Connection"
   IF ISNOTHING(pConnection) THEN EXIT FUNCTION

   ' // Create a Catalog object
   pCatalog = NEWCOM "ADOX.Catalog"
   IF ISNOTHING(pCatalog) THEN EXIT FUNCTION

   TRY
      ' // Connection String - Change it if needed
      ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
      ' // Open the connection
      pConnection.Open ConStr
      ' // Set the ActiveConnection property of the Catalog
      pCatalog.putref_ActiveConnection = pConnection
      ' // Get a reference to the Procedures collection
      pProcedures = pCatalog.Procedures
      ' // Get the Procedure object
      pProcedure = pProcedures.Item("AuthorById")
      ' // Get the Command
      vCommand = pProcedure.Command
      pProcedure = NOTHING
      pCommand = vCommand
      vCommand = EMPTY
      ' // Get a reference to the Parameters collection
      pParameters = pCommand.Parameters
      pCommand = NOTHING
      ' // Retrieve the number of objects in the collection
      nCount = pParameters.Count
      IF nCount = 0 THEN EXIT TRY
      FOR i = 0 TO nCount - 1
         pParameter = pParameters.Item(i)
         STDOUT "Parameter name: " & pParameter.Name & $CRLF & _
                "Parameter type: " & STR$(pParameter.Type)
         pParameter = NOTHING
      NEXT
   CATCH
      ' // Display error information
      STDOUT AdoGetErrorInfo(pConnection, OBJRESULT)
   FINALLY
      ' // Release the collections
      pParameters = NOTHING
      pProcedures = NOTHING
      ' // Close the connection
      IF pConnection.State = %adStateOpen THEN pConnection.Close
   END TRY

   ' // Release the objects
   pCatalog = NOTHING
   pConnection = NOTHING

   WAITKEY$

END FUNCTION
' ========================================================================================
« Last Edit: August 21, 2011, 10:58:51 AM by José Roca »

Offline José Roca

  • Administrator
  • Hero Member
  • *****
  • Posts: 2481
  • User-Rate: +204/-0
ADOX Example: Procedures Collection
« Reply #71 on: August 21, 2011, 12:00:47 AM »

The following example enumerates the Procedures collection.

Code: [Select]
' ########################################################################################
' Microsoft Windows
' File: ADOXEX_Procedures.bas
' Contents: ADOX example
' Enumerates the Procedures collection.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

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

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

   LOCAL pConnection AS ADOConnection
   LOCAL pCatalog AS ADOXCatalog
   LOCAL pProcedures AS ADOXPRocedures
   LOCAL pProcedure AS ADOXProcedure
   LOCAL ConStr AS WSTRING
   LOCAL nCount AS LONG
   LOCAL i AS LONG

   ' // Create a Connection object
   pConnection = NEWCOM "ADODB.Connection"
   IF ISNOTHING(pConnection) THEN EXIT FUNCTION

   ' // Create a Catalog object
   pCatalog = NEWCOM "ADOX.Catalog"
   IF ISNOTHING(pCatalog) THEN EXIT FUNCTION

   TRY
      ' // Connection String - Change it if needed
      ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
      ' // Open the connection
      pConnection.Open ConStr
      ' // Set the ActiveConnection property of the Catalog
      pCatalog.putref_ActiveConnection = pConnection
      ' // Get a reference to the Procedures collection
      pProcedures = pCatalog.Procedures
      ' // Retrieve the number of objects in the collection
      nCount = pProcedures.Count
      IF nCount = 0 THEN EXIT TRY
      FOR i = 0 TO nCount - 1
         pProcedure = pProcedures.Item(i)
         STDOUT "Procedure name: " & pProcedure.Name & $CRLF & _
                "Date created: " & AfxVarToStr(pProcedure.DateCreated)
         pProcedure = NOTHING
      NEXT
   CATCH
      ' // Display error information
      STDOUT AdoGetErrorInfo(pConnection, OBJRESULT)
   FINALLY
      ' // Release the collection
      pProcedures = NOTHING
      ' // Close the connection
      IF pConnection.State = %adStateOpen THEN pConnection.Close
   END TRY

   '//  Release the objects
   pCatalog = NOTHING
   pConnection = NOTHING

   WAITKEY$

END FUNCTION
' ========================================================================================
« Last Edit: August 21, 2011, 10:59:41 AM by José Roca »

Offline José Roca

  • Administrator
  • Hero Member
  • *****
  • Posts: 2481
  • User-Rate: +204/-0
ADOX Example: Properties Collection
« Reply #72 on: August 21, 2011, 12:01:32 AM »
Code: [Select]
' ########################################################################################
' Microsoft Windows
' File: ADOXEX_Properties.bas
' Contents: ADOX example
' Enumerates the properties of the Name column of the BIBLIO.MDB database.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

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

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

   LOCAL pConnection AS ADOConnection
   LOCAL pCatalog AS ADOXCatalog
   LOCAL pTables AS ADOXTables
   LOCAL pTable AS ADOXTable
   LOCAL pColumns AS ADOXColumns
   LOCAL pColumn AS ADOXColumn
   LOCAL pProperties AS ADOProperties
   LOCAL pProperty AS ADOProperty
   LOCAL ConStr AS WSTRING
   LOCAL nCount AS LONG
   LOCAL i AS LONG
   LOCAL vValue AS VARIANT

   ' // Create a Connection object
   pConnection = NEWCOM "ADODB.Connection"
   IF ISNOTHING(pConnection) THEN EXIT FUNCTION

   ' // Create a Catalog object
   pCatalog = NEWCOM "ADOX.Catalog"
   IF ISNOTHING(pCatalog) THEN EXIT FUNCTION

   TRY
      ' // Connection String - Change it if needed
      ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
      ' // Open the connection
      pConnection.Open ConStr
      ' // Set the ActiveConnection property of the Catalog
      pCatalog.putref_ActiveConnection = pConnection
      ' // Get a reference to the Tables collection
      pTables = pCatalog.Tables
      ' // Get the number of objects of the collection
      nCount = pTables.Count
      IF nCount = 0 THEN EXIT TRY
      ' // Get a pointer to the table "Publishers"
      pTable = pTables.Item("Publishers")
      ' // Get a pointer to the Columns collection of the table
      pColumns = pTable.Columns
      pTable = NOTHING
      ' // Get the number of objects in the Columns collection for that table
      nCount = pColumns.Count
      IF nCount = 0 THEN EXIT TRY
      ' // Enumerate all the objects
      FOR i = 0 TO nCount - 1
         pColumn = pColumns.Item(i)
         PRINT "Column name: " pColumn.Name
         PRINT "Defined size: " pColumn.DefinedSize
         PRINT "Type: " pColumn.Type
         PRINT "Attributes: " pColumn.Attributes
         pColumn = NOTHING
      NEXT
      ' // Get a pointer to the "Name" column
      pColumn = pColumns.Item("Name")
      ' // Get a pointer to his properties collection
      pProperties = pColumn.Properties
      nCount = pProperties.Count
      pColumn = NOTHING
      ' // Enumerate the properties
      PRINT "-----------------------------------------------------"
      PRINT "Properties of the Name column: "
      PRINT "-----------------------------------------------------"
      FOR i = 0 TO nCount - 1
         pProperty = pProperties.Item(i)
         PRINT "Property name: " pProperty.Name
         vValue = pProperty.Value
         PRINT "Value: " AfxVarToStr(vValue)
         PRINT "Type: " pProperty.Type
         PRINT "Attributes: " pProperty.Attributes
         pProperty = NOTHING
      NEXT
   CATCH
      ' // Display error information
      STDOUT AdoGetErrorInfo(pConnection, OBJRESULT)
   FINALLY
      ' // Release the collections
      pProperties = NOTHING
      pColumns = NOTHING
      pTables = NOTHING
      ' // Close the connection
      IF pConnection.State = %adStateOpen THEN pConnection.Close
   END TRY

   ' // Release the objects
   pCatalog = NOTHING
   pConnection = NOTHING

   WAITKEY$

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

Offline José Roca

  • Administrator
  • Hero Member
  • *****
  • Posts: 2481
  • User-Rate: +204/-0
ADOX Example: How to refresh the Procedures collection of a Catalog
« Reply #73 on: August 21, 2011, 12:02:40 AM »

The following example shows how to refresh the Procedures collection of a Catalog. This is required before View objects from the Catalog can be accessed.

Code: [Select]
' ########################################################################################
' Microsoft Windows
' File: ADOXEX_RefreshProcedures.bas
' Contents: ADOX example
' The following example shows how to refresh the Procedures collection of a Catalog. This
' is required before View objects from the Catalog can be accessed.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

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

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

   LOCAL pConnection AS ADOConnection
   LOCAL pCatalog AS ADOXCatalog
   LOCAL pProcedures AS ADOXPRocedures
   LOCAL ConStr AS WSTRING

   ' // Create a Connection object
   pConnection = NEWCOM "ADODB.Connection"
   IF ISNOTHING(pConnection) THEN EXIT FUNCTION

   ' // Create a Catalog object
   pCatalog = NEWCOM "ADOX.Catalog"
   IF ISNOTHING(pCatalog) THEN EXIT FUNCTION

   TRY
      ' // Connection String - Change it if needed
      ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
      ' // Open the connection
      pConnection.Open ConStr
      ' // Set the ActiveConnection property of the Catalog
      pCatalog.putref_ActiveConnection = pConnection
      ' // Get a reference to the Procedures collection
      pProcedures = pCatalog.Procedures
      ' // Refresh the collection
      pProcedures.Refresh
      STDOUT "Procedures collection refreshed"

   CATCH
      ' // Display error information
      STDOUT AdoGetErrorInfo(pConnection, OBJRESULT)
   FINALLY
      ' // Release the collection
      pProcedures = NOTHING
      ' // Close the connection
      IF pConnection.State = %adStateOpen THEN pConnection.Close
   END TRY

   ' // Release the objects
   pCatalog = NOTHING
   pConnection = NOTHING

   WAITKEY$

END FUNCTION
' ========================================================================================
« Last Edit: August 21, 2011, 11:03:17 AM by José Roca »

Offline José Roca

  • Administrator
  • Hero Member
  • *****
  • Posts: 2481
  • User-Rate: +204/-0
ADOX Example: How to refresh the Views collection of a Catalog
« Reply #74 on: August 21, 2011, 12:03:23 AM »

The following example demonstrates how to refresh the Views collection of a Catalog. This is required before View objects from the Catalog can be accessed.

Code: [Select]
' ########################################################################################
' Microsoft Windows
' File: ADOXEX_RefreshViews.bas
' Contents: ADOX example
' The following example demonstrates how to refresh the Views collection of a Catalog.
' This is required before View objects from the Catalog can be accessed.
' Compilers: PBWIN 10+, PBCC 6+
' Headers: Windows API headers 2.03+
' Copyright (c) 2011 José Roca. Freeware. Use at your own risk.
' Portions Copyright (c) Microsoft Corporation. All Rights Reserved.
' THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER
' EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF
' MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
' ########################################################################################

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

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

   LOCAL pConnection AS ADOConnection
   LOCAL pCatalog AS ADOXCatalog
   LOCAL pViews AS ADOXViews
   LOCAL ConStr AS WSTRING

   ' // Create a Connection object
   pConnection = NEWCOM "ADODB.Connection"
   IF ISNOTHING(pConnection) THEN EXIT FUNCTION

   ' // Create a Catalog object
   pCatalog = NEWCOM "ADOX.Catalog"
   IF ISNOTHING(pCatalog) THEN EXIT FUNCTION

   TRY
      ' // Connection String - Change it if needed
      ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=biblio.mdb"
      ' // Open the connection
      pConnection.Open ConStr
      ' // Set the ActiveConnection property of the Catalog
      pCatalog.putref_ActiveConnection = pConnection
      ' // Get a reference to the Views collection
      pViews = pCatalog.Views
      ' // Refresh the collection
      pViews.Refresh
      STDOUT "Views collection refreshed"
   CATCH
      ' // Display error information
      STDOUT AdoGetErrorInfo(pConnection, OBJRESULT)
   FINALLY
      ' // Release the collection
      pViews = NOTHING
      ' // Close the connection
      IF pConnection.State = %adStateOpen THEN pConnection.Close
   END TRY

   ' // Release the objects
   pCatalog = NOTHING
   pConnection = NOTHING

   WAITKEY$

END FUNCTION
' ========================================================================================
« Last Edit: August 21, 2011, 11:04:45 AM by José Roca »