The ADO (ActiveX Data Object) data control is the primary interface between a Visual Basic application and a database. It can be used without writing any code at all! Or, it can be a central part of a complex database management system. This icon may not appear in your Visual Basic toolbox. If it doesn’t, select Project from the main menu, then click Components. The Components window will appear. Select Microsoft ADO Data Control, then click OK. The control will be added to your toolbox.
• As mentioned in Review and Preview, previous versions of Visual Basic used another data control. That control is still included with Visual Basic 6.0 (for backward compatibility) and has as its icon:
Make sure you are not using this data control for the work in this class. This control is suitable for small databases. You might like to study it on your own.
• The data control (or tool) can access databases created by several other programs besides Visual Basic (or Microsoft Access). Some other formats supported include Btrieve, dBase, FoxPro, and Paradox databases.
• The data control can be used to perform the following tasks:
1. Connect to a database.
2. Open a specified database table.
3. Create a virtual table based on a database query.
4. Pass database fields to other Visual Basic tools, for display or editing. Such tools are bound tools (controls), or data aware.
5. Add new records or update a database.
6. Trap any errors that may occur while accessing data.
7. Close the database.
• Data Control Properties:
Align -- Determines where data control is displayed.
Caption -- Phrase displayed on the data control.
ConnectionString -- Contains the information used to establish a connection to a database.
LockType -- Indicates the type of locks placed on records during editing (default setting makes databases read-only).
Recordset -- A set of records defined by a data control’s ConnectionString and RecordSource properties. Run-time only.
RecordSource -- Determines the table (or virtual table) the data control is attached to.
• As a rule, you need one data control for every database table, or virtual table, you need access to. One row of a table is accessible to each data control at any one time. This is referred to as the current record.
• When a data control is placed on a form, it appears with the assigned caption and four arrow buttons:
The arrows are used to navigate through the table rows (records). As indicated, the buttons can be used to move to the beginning of the table, the end of the table, or from record to record.
After placing a data control on a form, you set the ConnectionString property. The ADO data control can connect to a variety of database types. There are three ways to connect to a database: using a data link, using an ODBC data source, or using a connection string. In this lesson, we will look only at connection to a Microsoft Access database using a data link. A data link is a file with a UDL extension that contains information on database type.
• If your database does not have a data link, you need to create one. This process is best illustrated by example. We will be using the BIBLIO.MDB database in our first example, so these steps show you how to create its data link:
1. Open Windows Explorer.
2. Open the folder where you will store your data link file.
3. Right-click the right side of Explorer and choose New. From the list of files, select Microsoft Data Link.
4. Rename the newly created file BIBLIO.UDL
5. Right-click this new UDL file and click Properties.
6. Choose the Provider tab and select Microsoft Jet 3.51 OLE DB Provider (an Access database).
7. Click the Next button to go to the Connection tab.
8. Click the ellipsis and use the Select Access Database dialog box to choose the BIBLIO.MDB file which is in the Visual Basic main folder. Click Open.
9. Click Test Connection. Then, click OK (assuming it passed). The UDL file is now created and can be assigned to ConnectionString, using the steps below.
• If a data link has been created and exists for your database, click the ellipsis that appears next to the ConnectionString property. Choose Use Data Link File. Then, click Browse and find the file. Click Open. The data link is now assigned to the property. Click OK.
Once the ADO data control is connected to a database, we need to assign a table to that control. Recall each data control is attached to a single table, whether it is a table inherent to the database or the virtual table we discussed. Assigning a table is done via the RecordSource property.
• Tables are assigned by making queries of the database. The language used to make a query is SQL (pronounced ‘sequel,’ meaning structured query language). SQL is an English-like language that has evolved into the most widely used database query language. You use SQL to formulate a question to ask of the database. The data base ‘answers’ that question with a new table of records and fields that match your criteria.
• A table is assigned by placing a valid SQL statement in the RecordSource property of a data control. We won’t be learning any SQL here. There are many texts on the subject - in fact, many of them are in the BIBLIO.MDB database we’ve been using. Here we simply show you how to use SQL to have the data control ‘point’ to an inherent database table.
• Click on the ellipsis next to RecordSource in the property box. A Property Pages dialog box will appear. In the box marked Command Text (SQL), type this line:
SELECT * FROM TableName
This will select all fields (the * is a wildcard) from a table named TableName in the database. Click OK.
• Setting the RecordSource property also establishes the Recordset property, which we will see later is a very important property.
• In summary, the relationship between the data control and its two primary properties (ConnectionString and RecordSource) is:
No comments:
Post a Comment