Show All

About working with data and database objects by using Visual Basic code

In addition to working with data and objects such as forms and reports in the Microsoft Access interface, you can use Microsoft Visual Basic to write procedures that dynamically create, delete, and modify data and objects. For example, you can write a procedure that places a caption on a form or that changes the color of a field on a form when a user performs a particular action.

You can refer to data and objects directly in your code, or you can declare object variables to represent them. After an object variable is declared and assigned, you can use it just as you would the name of the object it represents, and you can change its value, just as you can change the value of any variable.

Note   Before running the following example, make sure that you have set a reference to Microsoft ADO Ext. 2.1 (or later) for DDL and Security in the Microsoft Visual Basic Editor.

How?

  1. Open the Module window.
  2. On the Tools menu, click References.
  3. In the Available References box, select Microsoft ADO Ext. 2.1 (or later) for DDL and Security, and then click OK.

The following Sub procedure creates a table, Old Invoices, with a single field, OrderID. It provides an example of declaring objects, assigning a value, and referring to those objects in code:

Public Sub ADOXCreateTable()
' The ADOX Catalog object corresponds to the DAO Database
' object
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table

   ' Open the catalog
   cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=<full path to your database>\<your database name>.mdb;"

   ' Create a new Table object.
   With tbl
      .Name = "Old Invoices"
      ' Create a field and append it to the new Table
      ' object. This must be done before appending the
      ' Table object to the Tables collection of the
      ' Catalog.
      .Columns.Append "OrderID", adVarWChar
   End With

   ' Add the new table to the database.
   cat.Tables.Append tbl
'Clean up the references
   Set cat = Nothing
   Set tbl = Nothing
End Sub

Microsoft ActiveX Data Objects (ADO) provides the objects, such as tables, queries, relationships, and indexes, that handle data-management tasks in a Microsoft Access database. These objects are called data access objects. You can share Visual Basic code that uses data access objects with other applications that use Microsoft ADO, such as Microsoft Excel.

You can also use Jet and Replication Objects (JRO) to access and manipulate data in a database server through any OLE DB provider.

In addition, Microsoft Access defines a variety of objects that you use for working with data, such as forms, reports, and controls.