Microsoft Knowledge Base

• Microsoft Visual Test for Windows 95 and Windows NT, version 4.0

SUMMARY

This article demonstrates how to use the OLE functions, OleCreateObject, OleDispatch, OleGetProperty, OleGetEnumeratedList, and OleReleaseObject, to read a Microsoft Access database file, and print its content to the Viewport.

NOTE: This article assumes you have the 32-bit edition of Visual Basic version 4.0 installed on your workstation. The script requires the database and JET Database engine version 3.0, as installed and registered by the Visual Basic setup program. The JET database engine installed and registered by the Visual Test setup program is used only for logging test results and is not redistributable.

To redistribute this sample, the destination machine must contain the JET database engine as installed by Visual Basic.

The OLE concepts addressed in this article may also be applied to other OLE Automation Servers and should still be of interest to the reader.

MORE INFORMATION

By using the Visual Test 4.0 OLE functions to automate the Data Access Object (DAO) server, you can read or write to a Microsoft Access database file. The DAO server also allows you to write to ISAM files and remote databases that support ODBC. This article demonstrates how to read from a Microsoft Access database.

The OleGetEnumeratedList function takes as its first parameter an object pointer that supports IEnumVARIANT. DAO supplies an object that implements IEnumVARIANT by calling its _NewEnum method. OleGetEnumeratedList will generate a run-time error 242 message, "Error enumerating object: No such interface support," if the object pointer passed as the first parameter does not support IEnumVARIANT.

Visual Basic 4.0 hides the use of IEnumVARIANT and _NewEnum with the use of For Each <element> In <group> VBA syntax. The References section at the end of this article lists some helpful books on OLE Automation.

Sample Code

The following example prints the contents of the sample Biblio.mdb Microsoft Access database file that is supplied with Visual Basic 4.0. The example script is flexible enough that simply changing the constant values szDatabase and szRecordset to string values that represent a different Microsoft Access database file and table or query, respectively, will not hinder its performance.

Visual Basic syntax precedes all Visual Test OLE function calls as comments in the example script:

   ' Variables of type Variant store the object pointers.
   Dim dbEngine As Variant
   Dim dbDatabase As Variant
   Dim dbRecordset As Variant
   Dim dbFields As Variant
   Dim dbField As Array Of Variant
   Dim dbEnum As Variant
   Dim cRecords As Long
   Dim cFields As Long
   Dim i As Long, j As Long

   ' Change szDatabase and szRecordset to reflect your needs.
   ' For this example, szDatabase must refer to a Microsoft Access
   ' database, and szRecordset must refer to a table or
   ' querydef.
   Const szDatabase = "c:\vb\biblio.mdb"
   Const szRecordset = "All Titles"
   Const dbOpenDynaset = 2

   Viewport On
   Viewport Clear

   ' Must get the database engine before opening
   ' the database through OLE automation.
   ' Set dbEngine = CreateObject("DAO.DBEngine")
   dbEngine = OleCreateObject("DAO.DBEngine")
   ' Set dbDatabase = dbEngine.OpenDatabase(szDatabase, False, False, "")
   dbDatabase = OleDispatch(dbEngine,       _
                            "OpenDatabase", _
                            szDatabase,     _
                            False,          _
                            False,          _
                            "")

   ' Set dbRecordset = dbDatabase.OpenRecordset(szRecordset, dbOpenDynaset)
   dbRecordset = OleDispatch(dbDatabase,      _
                             "OpenRecordset", _
                             szRecordset,     _
                             dbOpenDynaset)

   ' Read all records in from recordset.
   ' dbRecordset.MoveLast
   OleDispatch(dbRecordset, "MoveLast")
   ' dbRecordset.MoveFirst
   OleDispatch(dbRecordset, "MoveFirst")

   ' Get record count.
   ' cRecords = dbRecordset.RecordCount
   cRecords = OleGetProperty(dbRecordset, "RecordCount")

   ' Print every record in recordset.
   ' Note: the For loop iterates from 1 to 10 instead
   '  of 1 to cRecords by design. There are over 180
   '  records in BILIO.MDB's 'All Titles' table.
   '  Printing every record takes a bit of time.
   ' Note: the script must run to completion to
   '  allow proper releasing of the OLE object. If
   '  the script is stopped before completion, it
   '  may be necessary to restart the 'Microsoft
   '  Developer Studio' before continuing with
   '  further development.
   For i& = 1 To 10
      ' Get fields collection object.
      ' Set dbFields = dbRecordset.Fields
      dbFields = OleGetProperty(dbRecordset, "Fields")
      ' Get enumerating object.
      ' ** Visual Basic programmers do not use _NewEnum.
      '    See Visual Basic's For Each <element> In <group> section below.
      dbEnum = OleDispatch(dbFields, "_NewEnum")
      ' Get field count.
      cFields = OleGetProperty(dbFields, "Count")
      ' Get all field objects in current record.
      dbField = OleGetEnumeratedList(dbEnum, cFields)
      ' For each field in fields.
      For j& = 1 to cFields
         ' Print the value of field.
         Print OleGetProperty(dbField(j), "Value"); " ";
         // Release the 'Field' item.
         OleReleaseObject(dbField(j))

      Next j
      ' **
      Print
      ' Move to the next record in the recordset.
      ' dbRecordset.MoveNext
      OleDispatch(dbRecordset, "MoveNext")
      ' Release the enumerating object.
      OleReleaseObject(dbEnum)
      ' Release the fields collection object.
      'Set dbFields = Nothing
      OleReleaseObject(dbFields)
   Next i

   ' Close the recordset.
   ' dbRecordset.Close
   OleDispatch(dbRecordset, "Close")
   ' Set dbRecordset = Nothing
   OleReleaseObject(dbRecordset)
   ' Close the database.
   ' dbDatabase.Close
   OleDispatch(dbDatabase, "Close")
   ' Set dbDatabase = Nothing
   OleReleaseObject(dbDatabase)
   ' Release the database engine.
   ' Set dbEngine = Nothing
   OleReleaseObject(dbEngine)

   End

   For Each dbField In dbFields
      Debug.Print dbField
   Next

REFERENCES

"OLE 2 Programmer's Reference Volume Two", Microsoft Press.

"Inside OLE Second Edition", by Kraig Brockschmidt, Microsoft Press.