Freetutes.com

VB6 beginners tutorial - Learn VB6

Advanced VB6 tutorial - Learn Advanced VB6

Systems Analysis - System analysis and Design tutorial for Software Engineering

Browse Topics

- Getting started
- Data Types
- Modules
- Operators in VB6
- VB6 Variable
- VB6 Procedures
- VB6 Control Structures
- Loops in VB6
- VB6 Exit Do & With End With
- Arrays in VB6
- User-Defined Data Types
- VB6 Constants
-
VB6 Built-in Functions
- Date and Time in VB6
- VB6 Controls
- TextBox Control
- ComboBox & OptionButton
- Label & Frame
- PictureBox & ImageBox
- Timer Control
- ListBox & ComboBox
- VB6 ScrollBar
- Control Arrays in VB6
- Files controls in VB6
- VB6 CheckBox
- Forms in VB6
- Menus in VB6
- MDI Form in VB6
- InputBox
- MessageBox
- Mouse events
- Mouse Move
- Error Handling
-
Error Handling (2)
-
VB6 Database

You are here: Visual Basic > VB6 (Beginners Tutorial)

Tutorial Main Page | Previous Page | Contents | Next Page

Creating a Virtual Table

• Many times, a database table has more information than we want to display. Or, perhaps a table does not have all the information we want to display. For instance, in Example 8-1, seeing the Title and ISBN of a book is not real informative - we would also like to see the Author, but that information is not provided by the Titles table. In these cases, we can build our own virtual table, displaying only the information we want the user to see.

• We need to form a different SQL statement in the RecordSource property. Again, we won’t be learning SQL here. We will just give you the proper statement.

Quick Example: Forming a Virtual Table

1. We’ll use the results of Example 8-1 to add the Author name to the form. Replace the RecordSource property of the dtaTitles control with the following SQL statement:

SELECT Author,Titles.ISBN,Title FROM Authors,[Title Author],Titles WHERE Authors.Au_ID=[Title Author].Au_ID AND Titles.ISBN=[Title Author].ISBN ORDER BY Author

This must be typed as a single line in the Command Text (SQL) area that appears when you click the ellipsis by the RecordSource property. Make sure it is typed in exactly as shown. Make sure there are spaces after ‘SELECT’, after ‘Author,Titles.ISBN,Title’, after ‘FROM’, after ‘Authors,[Title Author],Titles’, after ‘WHERE’, after ‘Authors.Au_ID=[Title Author].Au_ID’, after ‘AND’, after ‘Titles.ISBN=[Title Author].ISBN’, and separating the final three words ‘ORDER BY Author’. The program will tell you if you have a syntax error in the SQL statement, but will give you little or no help in telling you what’s wrong.

Here’s what this statement does: It selects the Author, Titles.ISBN, and Title fields from the Authors, Title Author, and Titles tables, where the respective Au_ID and ISBN fields match. It then orders the resulting virtual table, using authors as an index.

2. Add a label box and text box to the form, for displaying the author name. Set the control properties.

Label3:
Caption - Author

Text1:
DataSource - dtaTitles (select, don’t type)
DataField - Author (select, don’t type)
Locked - True
Name - txtAuthor
Text - [Blank]

When done, the form should resemble this:

3. Save, then rerun the application. The author’s names will now appear with the book titles and ISBN values. Did you notice you still haven’t written any code?

I know you had to type out that long SQL statement, but that’s not code, technically speaking. Notice how the books are now ordered based on an alphabetical listing of authors’ last names


  

Tutorial Main Page | Previous Page | Contents | Next Page

   

Home | About Us | Privacy Policy | Contact Us

Copyright © Freetutes.com | All Rights Reserved