Tuesday, 26 June 2012

ADOX Programming Code Examples in VBA

This page is a reference for developers, demonstrating how to use the ADOX library to programmatically create, delete, modify, and list the objects in Access - the tables, fields, indexes, and relations, queries, and databases - and read or set their properties.

ADOX is an extension to the ADO library, exposing the catalog of database objects. To use this library, open the code window, choose References on the Tools menu, and check the box beside:
    Microsoft ADO Ext x.x for DDL and Security

In general, the DAO library is better than ADOX. DAO is purpose-designed for Access, and exposes properties the other libraries don't. But there are some things DAO cannot do, such as setting the Seed of an AutoNumber field. The ADOX library is less stable, and more subject to version problems, so if you strike problems with the code in this page, an MDAC update might address the issue for you.

See the field type reference for a comparison of the field types in ADOX compared to the Access interface and other libraries.

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

Index of FunctionsDescription
SetSeed()Set the Seed of an AutoNumber
ShowAllTables()List the tables (and optionally their fields)
ShowPropsADOX()Show the columns in a table, and optionally their properties
CreateTableAdox()Create a table with various field types
ModifyTableAdox()Show how to add fields to a table, and delete them
ModifyFieldPropAdox()Show how to alter field properties
DeleteTableAdox()Delete a table
CreateIndexesAdox()Show how to create indexes
DeleteIndexAdox()Show how to delete indexes
CreateKeyAdox()Show how to create relationships
ShowKeyAdox()List relationships
DeleteKeyAdox()Delete relationships
CreateViewAdox()Create a query
CreateProcedureAdox()Create a parameter query or action query
ShowProx()List the parameter/action queries
ExecuteProcedureAdox()Execute a parameter query
DeleteProcedureAdox()Delete a parameter/action query
CreateDatabaseAdox()Create a database
DeleteAllAndResetAutoNum()Delete all records from the table, and reset the AutoNumber
GetSeedADOX()Read the Seed of the AutoNumber of a table
ResetSeed()Reset the Seed of the AutoNumber.

No comments:

Post a Comment