Microsoft KB Archive/142786

= Microsoft Knowledge Base =

Using Visual Test OLE Functions to Read Microsoft Access DB
Last reviewed: June 25, 1997

Article ID: Q142786

The information in this article applies to:


 * 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 In 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 In 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 Visual Basic's For Each In : Visual Basic programmers should implement the For loop that prints the fields of a record and the code surrounding it delimited by (**) in comments as follows:

For Each dbField In dbFields Debug.Print dbField Next