![]() |
|||
![]()
|
![]() |
![]() |
![]() |
Day 7Using the Visdata ProgramToday you will learn everything you need to know about using one of the most valuable sample programs that is shipped with Visual Basic 5--the Visdata sample application. You'll learn how to use the Visdata sample application to maintain your database files, including creating and modifying database tables, performing simple data entry on existing tables, and using Visdata to make backup copies of existing databases.
Using Visdata to Maintain Databases and TablesVisdata is an excellent tool for constructing and managing databases for your Visual Basic 5 applications. You can use it to create new databases, add or modify tables and indexes, establish relationships, set user and group access rights, test and store SQL query statements, and perform data entry on existing tables. Visdata can present dynamic data entry forms in page format or grid layout format. You can add, edit, or delete records in any table using Visdata. You can connect to Microsoft Jet versions 1.1, 2.0, or 3.0 databases, as well as versions of dBASE, FoxPro, and Paradox. You can even access data from Excel spreadsheets, delimited text files and ODBC-connected databases. Visdata is a great tool for building sample tables and entering test data for your Visual Basic 5 applications. It is also a good tool for compacting, repairing, and managing user and group access rights for Microsoft Jet databases. Visdata allows you to test SQL queries and save them in your Microsoft Jet database as stored queries that you can access from your Visual Basic 5 programs. You can also use Visdata to copy records from one table to another--even to copy whole data tables from one database to another. This capability gives you the power to create backups of selected information from your existing databases. Finally, you can use Visdata to inspect the properties of Microsoft Jet data objects such as fields, relationships, tables, and indexes. You can learn a great deal about how the Microsoft Jet database engine operates by using Visdata to peek under the hood to see the heart of the Visual Basic 5 data access engine. The Visdata Opening ScreenIf you don't already have Visdata running, start it now. You can start Visdata
by selecting Visual Data Manager... from the Add-Ins menu. Once started, select File
| Open Database... | Microsoft Access..., and then open the BOOKS5.MDB database
that shipped with the CD included with this book. Your screen should look like Figure
7.1.
Now let's go through each of the four components of the Visdata main screen in a bit more depth. The Main MenuThe Visdata Main Menu contains four menu items: File, Utility, Window, and Help. The Utility menu item is enabled once a database is opened. The Visdata Main Menu gives you access to all the features and options of the program. You'll learn each menu option in depth later, but first, let's explore the File menu options just a bit. The File | Open Database... option, which we used in the preceding section, allows you to open an existing database. This database can be any one of several formats. The most common database format you'll probably deal with is the Microsoft Jet format (also known as the Microsoft Access database format). For practice, let's use Visdata to open an existing Microsoft Jet database. Select File | Open Database... | Microsoft Access. The Visdata program presents
you with an Open Microsoft Access Database dialog box (see Figure 7.2).
You can close the database by selecting File | Close from the Visdata main menu. The Database WindowThe Database window shows all the major data access objects in the currently opened
database. The Database window is where you go to add new tables to the database and
modify the design of one of the current tables. You can also open existing data tables
to add records to them. If you click the alternate mouse button within the Database
window while you have a table highlighted, you see several other table management
options.
Properties The Properties object shows the various properties of the opened database. With the BOOKS5.MDB database open, click the + sign next to the Properties object. Your screen should look like Figure 7.4.
Before you continue with the project, change the MoreAuthors table back to Authors using the same technique previously described. Delete The Delete option lets you delete the highlighted table and all its contents. To delete a table and all its contents, select the table you want to delete and click the alternate mouse button. Select the MoreAuthors table and click the alternate mouse button to bring up
the context menu. Select Delete from the list. Click Yes at the confirmation dialog
message to delete the MoreAuthors table. Your window list refreshes automatically.
To refresh the Database window, simply click anywhere in the Database window, and then click once with the alternate mouse button to bring up the context menu. Select Refresh List from the list. Visdata refreshes the Database window to reflect the current state of the data access objects in the opened database. New Table This option displays the Table Structure dialog, which can be used to construct a new table or index. We work on building new tables in the section entitled "Adding Tables and Indexes to the Database" later in this chapter. New Query This option displays the Visdata Query Builder, which can be used to help build SQL statements. We discuss the Query Builder when describing the Utility menu later in this chapter. The SQL Statement WindowThe SQL Statement window enables you to enter and execute standard SQL statements against the opened database. You can save the SQL query for later use in your Visual Basic 5 programs. Select the SQL Statement window by clicking the top border of the window one time. Now enter the following SQL query into the text window: SELECT * FROM Authors Now, make sure that the Use DBGrid Control on New Form icon, located at the top of the Visdata window, is selected, and then select the Execute button in the SQL Statement window to run the query. This is not an SQL Passthrough query, so answer No when prompted with this question. This statement selects all the data in the Authors table and presents it to the
screen. Your screen should look like the one in Figure 7.7.
You can save this query for later use within your Visual Basic 5 programs by first closing the screen that contains the result of your Select query and then clicking on the Save button in the SQL Statement window. Next, supply the query object name qryTest, and click OK in the dialog box that appears (see Figure 7.8). Again, this is not an SQL Passthrough query, so answer No when the SQL Passthrough dialog appears. Each time you load Visdata, the program remembers the last SQL query you entered in the SQL window. You can click the Clear button to clear out the text in the SQL Statement window. The Toolbar ButtonsIcons appear on a toolbar near the top of the Visdata main screen. You use these
icons to establish the type of data object Visdata uses to access the data and the
type of data entry form Visdata uses to present the selected data on the screen.
You can also use these icons to assist in making changes to your database, with the
option of committing the changes once made or rolling back (undoing) the change.
You can also use the Snapshot data access object to open a read-only view of one or more data tables. Snapshot objects are faster than Dynasets, but require more workstation memory. Finally, if you only need access to the physical base table in the database, you can select the Table radio button. Tables are fast and require little workstation memory. The disadvantage of the Table data access object is that you cannot use it to combine two or more tables into a single view. Even though most of the work you do from Visdata is with base tables, you should set this radio button to use the Dynaset data access object. Dynasets are fast enough for almost all Visdata work and they provide the most flexibility when dealing with multitable views. Selecting the Default Data Form The second set of icons enables you to select the type of data form you see when you load your data access object. Visual Basic 5 now ships with a very nice data-bound grid tool. This grid automatically loads all the fields in the selected data access object and scrolls data records into the table as needed. This grid object may be the most useful selection of the three. Click the Use DBGrid Control on New Form icon to make this your default data form. The other two icons select two versions of a standard data entry form. The first icon, Use Data Control on New Form, loads the records from the data access object one at a time, using the Visual Basic 5 data control tool. The second icon, Don't Use Data Control on New Form, presents a similar form, but without using the Visual Basic 5 data control tool. The advantage of the Data Control form is that it handles BIT and BINARY data type fields better than the No Data Control form. The No Data Control form, however, allows users to press F4 to display the entire contents of a data field whose contents overflow the control's display area. This zooming feature is handy when dealing with large text fields or memo fields. You can switch the Form Type radio button after each table is opened and displayed, which enables you to open one or more tables using different data forms. Let's open three tables, each using a different data form. First, select the Use DBGrid Control on New Form icon from the toolbar. Now double-click
the Authors table. This action brings up the Authors table in a grid display. Your
screen should look like Figure 7.9.
Next, select the Use Data Control on New Form button and double-click the Authors
table again. Now you see the same data presented in a standard data entry from. Your
screen should now look like Figure 7.10. To use this concept in Visdata, simply select the Begin a Transaction icon before you make a change to your database. If you like the change, select the Commit current Transaction icon and the change becomes permanent. If you don't like the change, press the Rollback current Transaction icon to undo the changes. Please note the use of the word "current" in the Commit and Rollback operations. This refers to all changes made since the last time the Begin icon was selected. Transactions cannot be rolled back once they are committed. Now that you have seen the major components of the Visdata main screen, let's review each of the menu items in greater detail. The Visdata File MenuThe Visdata File menu contains nine items. You can open, create, and close databases from the file menu, import and export data from and to the open database, log into a designated workspace, and review any errors that have been logged since you started Visdata. You can compact or repair Microsoft Jet databases from the File menu. You also exit the program from the File menu. If you have used Visdata before, you'll also see a list of the most recently used databases in this menu. You can reload one of those databases by clicking its name in the File menu. Open DatabaseBefore you can begin working on an existing database, you must first load it using the Open Database... menu option. This menu option enables you to load one of several database formats. Each format has a slightly different set of options in the menu tree. You can load Microsoft Access, dBASE, FoxPro, Paradox, Excel, text files, and ODBC data sources.
When you select Open Database..., you see several other menu choices. You select
one of the secondary items depending on the database format you want to access. The
following sections cover each of the secondary menu choices and how you use them
to open existing databases. Microsoft Access When you select the Microsoft Access...
option, Visdata brings up a File Open dialog box and prompts you to select the Microsoft
Access database you wish to load (see Figure 7.12).
When you select the correct format, you see the File Open dialog box prompting
you to locate and load a database. After the database is loaded, you see the list
of available tables. You also see a message at the bottom of the screen suggesting
that you use the Attach option to access the dBASE format data tables (see Figure
7.13).
FoxPro (2.0, 2.5, 2.6, and 3.0) Loading the FoxPro format databases works the same as loading the dBASE format databases. When you select FoxPro from the menu, you see an additional menu list that asks you to select the proper database format. When you select the format, you see the File Open dialog prompting you to locate and load the proper database. The same warnings mentioned in the preceding dBASE section apply here. Do not attempt to load a FoxPro 2.6 format database using the FoxPro 2.5 format menu option. Even if the file loads initially without errors, you will probably get unpredictable results and may even corrupt your database. Paradox (3.x, 4.x, and 5.0) Opening Paradox files with Visdata works much like opening FoxPro or dBASE format databases. You select the database version you wish to access, and then fill out the File Open dialog box to locate and load the database. The CD that ships with this book contains a Paradox 4.x format database called PDSAMPLE.DB. You can locate and load this file from the \TYSDBVB5\SOURCE\DATA\PARADOX directory. Excel Visdata can also directly load Microsoft Excel spreadsheet files and enable you to manipulate their contents. When you select Excel... from the Open Database menu, you see the File Open dialog box that prompts you to locate and load the Excel spreadsheet. Visdata locates all sheets and named ranges defined in the Excel file and presents
them as table objects in the Database window (see Figure 7.14). Figure 7.16 shows the same Excel file opened using Visdata. In Figure 7.16, the table object Sheet1$ has been opened as a Dynaset object.
After you open the Excel file, you can perform all data entry operations on that
file including creating new tables and editing data in existing tables in the spreadsheet. After you fill out the ODBC dialog box, Visdata locates and opens the data source
and updates the Database window. New...The New menu option enables you to use Visdata to create entirely new databases in several formats. This section concentrates on the Microsoft Access database format. Most of the rules for creating Microsoft Jet databases apply equally to non-Microsoft Jet formats. Although the Visdata application can create a non-Microsoft Jet database, you should not use Visdata to create non-Microsoft Jet databases very often. If you need to work in non-Microsoft Jet formats, use the native database engine to create the data files. You can then use Visdata to access and manipulate the non-Microsoft Jet databases. Access (Version 2.0 and 7.0) When you select the Microsoft Access menu item, Visdata asks you to select one of two versions of Microsoft Access data format: 2.0 or 7.0. The 2.0 format can be read by all versions of Microsoft Access and by Microsoft Visual Basic versions 4.0 and later. Version 7.0 format databases can only be read by the 32-bit version of Visual Basic 4 and by the 32-bit version of Microsoft Access. The advantage of the older formats is that the data can be read by most versions of the software. The advantage of the version 7.0 format is that it allows for additional database properties that are not available in the older formats.
After you select a database format from the submenu, Visdata presents you with
a dialog box that prompts you to enter a filename for the new database (refer to
Figure 7.19).
Figure
7.20. Creating a FoxPro database directory. When you select the text menu option, Visdata prompts you to enter a name for the database. This name is used to create a directory (Windows 95 folder) on the designated drive. You can use any valid device designator and directory path you want when you create the database. Close DatabaseThe Close Database menu option simply closes the open database. All tables are closed at the same time. Import/Export...The Import/Export... function allows you to move data into and out of the currently
open database. To bring data in from another database, simply select Import/Export...
from the File menu. When this option is selected, you are presented with the dialog
shown in Fig- ure 7.21. To export data, select Import/Export... from the File menu. Then, select the table from the dialog that appears and press the Export Table(s) button. You are then prompted to select a format and a file to hold the exported data. WorkspaceThe Workspace menu item displays a login dialog that allows you to log in to the
currently open database as a different user. This is handy if you want to test user
IDs and passwords. When you select Workspace from the menu, you see a dialog box
that requests a login ID and password (see Figure 7.24). ErrorsThe Errors menu option shows the last error or set of errors reported to Visdata
(see Fig- ure 7.25).
Compact MDB...You can use Visdata to compact existing Jet databases (MDB files). Compacting a database removes empty space in the data file once occupied by records that were deleted. Running the Compact menu option also reorganizes any defined indexes stored in the database. When you select Compact MDB..., you have to select a database format. If you select 3.0 MDB... from this menu, the database you selected is compacted and stored as a Microsoft Jet version 3.0 database. If you select 2.0 from this menu, the database you select is compacted and stored as a Microsoft Jet version 2.0 database.
When you select the target format, you see a File Open dialog box asking you to select the database you want to compact. The database you select cannot be opened by any other program while it is being compacted. After you select the source database, you have to enter the name of the destination database file. If you select the same name as the source, your current data file is overwritten with the new format. If you select a new database filename, all information is copied from the source database to the target database.
Before Visdata compacts your database, you will be asked if you want to encrypt the data. If you say Yes, Visdata copies all data and encrypts the file so that only those who have access to the security files can read the data. We talk more about data encryption on Day 21 "Securing Your Database Applications." Repair MDB...If you get a "database corrupt" error when you attempt to open a Microsoft Jet database file, you may need to repair your database. Database files can become damaged due to power surges during read/write operations or due to physical device errors (damaged disk drive plates, and so on). You can repair an existing database by selecting Repair MDB... from the File menu. You then see a File Open dialog box that asks you for the database filename. Once you select the filename, Visdata loads and repairs the database to the best of its capabilities. Unfortunately, you may receive a message saying some of the data could not be recovered.
Exiting VisdataThe Exit item does just what you expect. When you exit Visdata, your current database closes, along with all open database objects. If you have text in the SQL window, it is saved and restored the next time you load Visdata. Visdata also remembers the windows you had open, as well as their sizes and their locations for the next time you load Visdata. Adding Tables and Indexes to the DatabaseWhen you have created a new database, you can add new tables and indexes to the database. You can also add new tables and indexes to existing databases. To illustrate the process of managing database tables using Visdata, let's create a new Microsoft Access (Jet) database, add a new table, add a new index, and then modify the table structure. Creating the New CH07NEW.MDB DatabaseIf you haven't already done so, load and start Visdata. Select File | New... |
Microsoft Access... | Version 7.0 MDB... from the main menu and enter CH07NEW.MDB
in the Select Microsoft Access Database to Create dialog box (see Figure 7.26). Click
the Save button to create the new database.
|
Property | Setting |
Tables | BookSales |
Field Name | BookSales.Units |
Operator | > |
Value | 14 |
Fields to Show | BookSales.Title |
BookSales.Units | |
Order by | BookSales.Units, Desc |
The Data Form Designer builds a data entry form complete with a data control and command buttons for data administration. The form is saved to the currently active Visual Basic project. To demonstrate, let's build a sample form with the Data Form Designer.
First, make sure you have the BOOKS5.MDB (TYSDBVB5\SOURCE\DATA)
database open in Visdata. Next, select Data Form Designer... from the Utility Menu.
You should see the Data Form Designer dialog (see Figure 7.35).
Figure
7.35. The Data Form Designer.
Enter frmAuthors in the Form Name field. Next, select Authors as the RecordSource.
Note, when you select Authors, all of the fields within that table appear in the
Available Fields list box. Now, click the >> button to move all the fields
into the Included Fields list box. Your dialog should look like Figure 7.36.
Figure
7.36. The completed frmAuthors design.
Click the Build the Form button to save the form to the currently active Visual Basic
5 project.
Now, close the Data Form Designer and Visdata and return to your Visual Basic
5 project. Open frmAuthors. You should see a form similar to the one in Figure 7.37.
Figure
7.37. The completed frmAuthors form.
Notice how you have all the data fields, as well as a data control and command buttons.
This is a quick and easy way to build forms for data entry!
The Global Replace menu option enables you to perform a mass update of existing tables, which comes in handy when you need to zero values in test data or need to perform mass updates on a database.
For this example, set all the fields in a data table to the same value. Load the
BOOKS5.MDB database (TYSDBVB5\SOURCE\DATA), and then select Utility
| Global Replace... from the menu. You see the Global Replace dialog box, as shown
in Figure 7.38.
Figure
7.38. Entering a Global Replace command.
Select the NewAuthors table and the Contracted field. Set the Replace With value
to zero and leave the Criteria field blank. When you click the OK button, Visdata
resets all the NewAuthors.Contracted fields to zero. You can limit the number of
records affected by the Global Replace command by entering an appropriate logical
statement in the Criteria box. For example, if you wanted to update only the records
that have an Au_ID value of 30, you could enter the following line in the Criteria
box:
Au_ID=30
We cover Criteria more in depth in the lesson on Day 8, and you'll learn more about the global replace command in the lesson on Day 14, "Error Handling in Visual Basic 5.0."
Visdata allows you to attach external database files to an existing Microsoft Access (Jet) format database. When you create an attachment, you actually create a link between your own Microsoft Access database and another database. You don't actually import any data from the external database into your own MDB. By creating attachments, you can access and manipulate external data files as if they are native Microsoft Access tables. Attached tables appear in the Database window as local table objects in your database, even though they are only links to external data files.
TIP: Not only is the attachment method convenient, it provides the fastest way to access external data using Visual Basic 5 programs. You can load, index, and display attached external tables faster than you can if you use ODBC or directly open the external data files in their native format.
Now create an attached table in the BOOKS5.MDB database that we used earlier today.
If you like, you can create an attachment to any other Microsoft Jet format database you already have on hand.
First, if you don't have it loaded already, select File | Open Database... from
the main menu to load the BOOKS5.MDB (TYSDBVB5\SOURCE\DATA) database.
Then select the Utility | Attachments menu option. You will see a grid that shows
all the current attachments for this database. Because there are no attachments to
this database, this box should be empty. Click the New command button to open the
New Attached Table dialog box. Your screen should now look like Figure 7.39.
Figure
7.39. Adding an attachment to a Microsoft
Access database.
Table 7.2 shows the information you should enter into the Attachment dialog box.
Table 7.2. New Attached Table dialog box values.
Dialog Field | Value |
Attachment Name | Test Attachment |
Database Name | \TYSDBVB5\SOURCE\DATA\CRYSRPT.MDB |
Connect String | Access MDB |
Table to Attach | CompanyMaster |
After filling out the dialog form, click Attach to commit the attachment. After
you close the Attachment dialog box, you see that the grid is updated to show the
new attachment you just added to the database. Close the New Attached Tables dialog
and the Attachments grid. You now see a new entry in your Database window list. This
shows a new table object. Note how the icon for the attachment differs from the other
tables' icons. Your screen should look something like the one in Figure 7.40.
Figure
7.40. An attached table object.
You can now access this attached table just like you would any table you created
using Visdata.
Selecting Utility | Group/Users... brings up the Groups/Users/Permissions dialog
shown in Figure 7.41.
Figure
7.41. The Groups/Users/Permissions dialog.
This dialog can be used to set all of the permission rights for users and groups.
In order to use this function, you must have a security file (SYSTEM.MD?)
to which you belong. This function allows the setting of rights and passwords on
a user and on a group level.
Use the SYSTEM.MD? menu option to locate and load the SYSTEM.MD? security file. The SYSTEM.MD? file contains information about Microsoft Access file security, including defined users, groups, workspaces, passwords, and data object rights. You must create this file using the Microsoft Access utility WRKGADM.EXE.
The Utility | SYSTEM.MD? menu option presents you with a File Open dialog so that you can locate and load a SYSTEM.MD? file. Once it is loaded, Visdata adds this information to the Registry so that you won't have to reload it in the future.
The Preferences menu option lets you customize the way Visdata shows you information. Two toggle settings control the way Visdata displays data, and two parameter settings control the way Visdata performs database logins and queries. Open Last Database on Startup When you toggle on the Open Last Database option, Visdata remembers the last database you had open when you last exited Visdata and automatically attempts to open that file the next time you start Visdata. Include System Files When you toggle on the Include System Files option, you see several tables maintained by Microsoft Jet to keep track of table, user, group, relation, and query definitions. Users cannot access these tables, and the tables should not be altered or removed at any time. Query Time-Out Value You can use the Query Time-Out Value menu option to adjust the number of seconds Visdata waits before reporting a time-out error when attempting a query. If you work with slow external data files or ODBC connections, you can adjust this value upward to reduce the number of errors Visdata reports when you run queries. Login Time-Out Value You can use the Login Time-Out Value menu option to adjust the number of seconds Visdata waits before reporting a time-out error when attempting to log into a remote data source. Adjust this value upward if you get time-out errors when dealing with slow ODBC or external data sources.
The last two items on the Visdata main menu are the Windows menu and the Help menu. These two items contain the usual options that all good Windows programs have.
This menu helps you control how all the child windows are displayed within the main MDI form. You can Cascade, Tile, or Arrange Icons from this menu. You can also force the focus to one of the three default Visdata windows: Database window, SQL window, or MDI form.
The Help menu gives you access to the Visdata Help file included with your version of Visual Basic 5. You can also view the About box from this menu.
Today you learned how to use the Visdata sample application to perform all the basic database operations needed to create and maintain databases for your Visual Basic 5 applications.
You learned how to do the following:
You learned to use Visdata to perform database utility operations, including the following:
You learned to use Visdata to adjust various system settings that affect how Visual Basic 5 displays data tables and processes local and external database connections and parameters that control how Visual Basic 5 locks records at update time.
You have been asked to build a database to track entities that purchase from and sell to your organization. Complete the following tasks using Visdata as your development tool.
Field | Type | Size |
ID | Text | 10 |
Name | Text | 50 |
Address1 | Text | 50 |
Address2 | Text | 50 |
City | Text | 50 |
StateProv | Text | 25 |
Zip | Text | 10 |
Phone | Text | 14 |
Fax | Text | 14 |
Contact | Text | 50 |
Notes | Memo | NA |
![]() |
|
Use of this site is subject certain Terms & Conditions. Copyright (c) 1996-1999 EarthWeb, Inc.. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Please read our privacy policy for details. |