See Also Objects Properties Events Language
Description
The ability to access and manipulate data in databases, and to manage databases and their objects.
Remarks
Using a data control is the easiest way to create applications that allow users access to data in a database. You can perform most data access operations without writing any code at all. Controls bound to a data control automatically display data from a specific field for the current record. If the data control changes to a different record, all bound controls automatically change to display data from the current record. If a user changes data or enters new information, the changes are automatically saved in the database when the data control moves to a new record. You can use the Validate event and the DataChanged property to fine-tune your application.
Visual Basic uses the Microsoft Access database engine for its local data access functionality, and Open Database Connectivity (ODBC) for remote data access functionality.
A Database object has a number of other data access objects, such as TableDef objects, Field objects, and so on. Each object has a set of properties and methods, and some objects have properties that are collections of other objects. In the same way that a form has a Controls collection, a Database has a TableDefs collection of TableDef objects, and a TableDef has a Fields collection of Field objects. For example, to set the value of the "PubID" Field in the current record of a data control's Recordset property, you could use this code:
Data1.Database.TableDefs("Publishers").Fields("PubID").Value = 99
In addition, like other controls, data access objects have a default collection and a default property. For example, the default collection of a recordset is the Fields collection, and the default property of a Field is the Value property. You can simplify your code by taking advantage of these defaults. For example, the following code also sets the value of the "PubID" Field in the current record:
Data1.RecordSource = "Publishers"
Data1.Refresh
Data1.Recordset("PubID") = 99
You can include most Visual Basic functions in SQL statements when querying a database, except when using the DB_SQLPASSTHROUGH option with CreateDynaset or CreateSnapshot. For example, you could use a SQL statement like this:
SELECT PubID, UCase(Left([Company Name], 10) As [Co Name]
FROM Publishers
You must be running SHARE.EXE (from your AUTOEXEC.BAT file) in order to use Visual Basic's data access functionality.
Professional Edition
With the Professional Edition, you can create object variables of any of the object types using the Dim and Set statements. For example, the following code creates object variables for a Database, a Dynaset, and a Field:
Dim MyDb As Database, MyDn As Dynaset, MyFd As Field
Set MyDb = OpenDatabase("BIBLIO.MDB")
Set MyDn = MyDb.CreateDynaset("SELECT * FROM Authors")
Set MyFd = MyDn.Fields("Au_ID")
You can use object variables and other Data Definition Language (DDL) features to modify the structure of your database. For example, you can add a new Field to an existing table with the following code:
Dim MyDb As Database, MyFd As New Field
Const DB_TEXT = 10, DB_UPDATABLEFIELD = 32 ' Set constants.
Set MyDb = OpenDatabase("BIBLIO.MDB") ' Open database.
MyFd.Name = "Address" ' Set Field properties.
MyFd.Type = DB_TEXT
MyFd.Attributes = DB_UPDATABLEFIELD
MyDb.TableDefs("Authors").Append MyFd ' Append to TableDef.
This code creates a New object variable for a Field, sets the appropriate properties, then adds it to a TableDef with the Append method. Since a TableDef contains the definition of a Table, the Table now has a Field named "Address" ready for entering data. In much the same way, you can create new tables and new indexes.
You can use the Delete method to remove objects from the structure of the Database; however, you cannot delete a Field from the Fields collection of a TableDef.