Monday, 25 June 2012

ADO programming examples in VBA

This page is a reference for developers, demonstrating how to use the ADO library to list and manipulate the objects in Access.

ADO (ActiveX Data Objects) is more generic than DAO (the one designed to handle the objects in Access), so supports features of databases other than Access. In the wider world beyond Access, ADO has largely been replaced by the quite different ADO.NET library.

In general, DAO is preferred over ADO, but there are some operations that work under ADO only. In general, these work in code only. They will not work if you try them in the Query window, since Access itself uses DAO. They also require JET 4 (Access 2000 or later.)

ADO provides only limited ways to manipulate the data structure (typically via DDL query statements), unless you also use the ADOX library which provides the extensions to get to the database catalog.

To use the ADO Library, choose References on the Tools menu in the code window, and check the box beside:
    Microsoft ActiveX Data Objects 2.x Library

There is no explanation of the code beyond in-line comments, and no error handling in most examples.

Index of FunctionsDescription
ShowSchema()List the tables
AdoRecordsetExample()Open a recordset
CreateViewAdo()Create a new query
ModifyViewAdo()Modify a query
ShowBand()Illustrate the BAND operator with literals. (ADO only.)
TestBnot()Illustrate BNOT (binary NOT) operator (ADO only.)
TestBand()Illustrate BAND (binary AND) operator. (ADO only.)
ShowUserRosterMultipleUsers()List the users currently connected to the database.
UserCount()Count the number of distinct users connected to the database.

No comments:

Post a Comment