Chapter
Seven
Chapter Seven
Using the
Visdata Program
Today 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.
NOTE: This lesson does not cover the source
code for Visdata or talk about how Visdata works. You can, however, learn a great deal by
bringing the Visdata project up within Visual Basic 5 and studying the modules and forms.
Studying Visdata in this manner is an excellent way to learn how to create dynamic data
entry forms, handle SQL processing, and link your Visual Basic 5 programs to back-end
database servers using ODBC drivers.
Using Visdata
to Maintain Databases and Tables
Visdata 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 Screen
If 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 guideS5.MDB
database that shipped with the CD included with this guide. Your screen should look like
Figure 7.1.
Figure 7.1. The Visdata
main screen.
This MDI form is "Data Central" for the Visdata application. All database
activity starts from this screen. Four major components of this screen deserve attention:
- The Main Menu: This menu gives you access to all the
features of Visdata. This menu also expands once you open a database.
- The Database Window: This window shows all the
properties and table objects present in the database you currently have open.
- The SQL Statement Window: This window allows you to
write and execute standard SQL statements against the database you currently have open.
- The Toolbar: You use this to determine the type of
data objects you want to work with.
Now let's go through each of the four components of
the Visdata main screen in a bit more depth.
The Main Menu
The 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).
Figure 7.2. Opening a
Microsoft Access database.
Locate and select the guideS5.MDB database that can be found in the \TYSDBVB5\SOURCE\DATA
directory on the CD that ships with this guide. Click the Open button to load the database.
Once the database is loaded, Visdata updates the Database window to show all the primary
data access objects in the currently opened database. Your screen should now look
something like Figure 7.3.
You can close the database by selecting File | Close
from the Visdata main menu.
The Database
Window
The 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.
Figure 7.3. Visdata with
an open database.
NOTE: We use the term "alternate mouse
button" to avoid any confusion between left-handed and right-handed users. If you
have your mouse set for left-handed use, the alternate button is the left button; if you
have your mouse set for right-handed use, the alternate button is the right button.
Properties The Properties object shows the various
properties of the opened database. With the guideS5.MDB database open, click the +
sign next to the Properties object. Your screen should look like Figure 7.4.
NOTE: Many of the properties listed on this
screen are available only in the Version 3.0 Microsoft Jet MDB format. Don't be alarmed if
your screen has several empty fields. You learn more about the difference between the
various MDB formats later.
Figure 7.4. Viewing the
database properties.
Open The Open alternate mouse option loads the selected table. It performs the same
function as double-clicking the table name. Design The dDesign option brings up the table
Structure design dialog. You can view, edit, and add fields and indexes from this screen.
Try this with a few tables so you can get a feel for the information available in the
Design dialog. Rename The Rename option allows you to rename the highlighted table without
deleting the data. Highlight the Authors table by clicking it once with the primary mouse
button. Now click the alternate mouse button to bring up the context menu. Select Rename
from the menu and enter MoreAuthors as the new name, and then press Enter. Your screen
should look like Figure 7.5 as you rename the Authors table.
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.
Figure 7.5. Renaming a
data table.
Copy Structure The Copy Structure... option lets you copy the highlighted table's field
layout and design, with or without existing data, to a different database. Select the
Authors table and click the alternate mouse button to bring up the context menu. Select
Copy Structure... from the menu list, and you see a dialog box like the one in Figure 7.6.
Figure 7.6. Copying a
table.
Notice that you can enter a new database name and connect string in the dialog box. This
capability means you can copy the structure to an entirely different database. Leave the
database name and connect string alone for now. Check the Copy Indexes and Copy Data
checkboxes, click on the Authors table in the Table list box, and click OK. You are then
prompted for a table name. Enter MoreAuthors and click OK. A message from Visdata appears,
telling you that the new table has been created. When you exit the dialog by clicking
Close, Visdata refreshes the Window List automatically. You should now see a new table in
the list--MoreAuthors. Refresh List The Refresh List option updates the window to reflect
changes in the data access objects that are part of the database. Usually, Visdata
refreshes the Database window each time you take an action that affects the contents of
the list. Some actions, however, do not automatically update the window. For example, if
you use the SQL window to enter SQL statements to create a new data table in the database,
Visdata does not automatically refresh the Database window.
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 Window
The 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.
Figure 7.7. Results of an
SQL query.
NOTE: We will cover SQL SELECT queries in
depth in the lesson on Day 8, "Selecting Data with SQL." For now, just remember
that you can write, test, and save your SQL queries using the Visdata SQL window.
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
Buttons
Icons 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.
Figure 7.8. Saving a
query.
Selecting the Default Data Access Object The first set of icons controls the type of data
access object that Visdata uses to open the data table. The default data access object is
the Visual Basic 5 Dynaset, the most flexible Visual Basic 5 data access object. You can
use the Dynaset object to create updatable views of more than one table or open an
existing table for read/write access.
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.
Figure 7.9. Authors
table using the grid data form.
NOTE: Please note that the columns in this
view can be resized. Simply select a column divider with your mouse and drag to the
desired width.
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.
Figure 7.10. Authors
table using the Data Control form.
Next, select the Don't Use Data Control on New Form icon and double-click the Authors
table a third time. Now, you see the Authors data presented in a slightly different data
entry form. Notice the differences in the way the Contracted field appears on the Don't
Use Data Control on New Form (as text) form and the Data Control form (checkbox). Figure
7.11 shows a tiled view of the three data forms side by side.
Figure 7.11. Three data
forms side by side.
Beginning, Rolling Back, and Committing Transactions A basic principle in database
management is the concept of begin, rollback, and commit transactions. This refers to the
theory that changes are temporarily made to the database and reviewed before they are made
permanent. If an error occurs as a result of the temporary change, the transaction can be
undone, or rolled back without causing permanent damage to the underlying data. This is a
particularly handy concept when making large changes to multiple data tables. We address
this issue in detail in Day 17, "Multiuser Considerations."
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 Menu
The 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
Database
Before 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.
NOTE: You can load only one database at a
time into Visdata. If you need to work on tables from more than one database, you need to
use the Utility | Attachments... menu option to attach the foreign data tables (the tables
that are contained within a database other than the one on which you are working) to the
database you currently have open. We cover the Attach option later in this lesson.
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).
Figure 7.12. Loading a
Microsoft Access database.
dBASE (III, IV, and 5.0) You can also use Visdata to load dBASE-format databases. When you
select the dBASE menu option, you see an additional menu that asks you to select version
III, IV, or 5.0 database format.
WARNING: You must tell Visdata what dBASE
format you are loading so that it knows what index files and memo field formats to expect.
If you load an incorrect format into Visdata, you do not see an error message right away.
You may receive error messages, however, when you attempt to read or write data to the
database. These errors may permanently corrupt your database. Be sure you load the FoxPro
and dBASE databases using the correct menu option to avoid problems.
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).
Figure 7.13. Viewing a
loaded dBASE database.
TIP: When you deal with non-Microsoft Jet
data formats, you get better performance speed if you access them through the Utility |
Attachments... menu option. We cover the Utility | Attachments... menu option later in
today's lesson.
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 guide 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.14. Using
Visdata to directly load an Excel spreadsheet.
Figure 7.15 shows the sample Excel spreadsheet \TYSDBVB5\SOURCE\DATA\XLDATA\EXSAMPLE.XLS
as it appears in Excel. The range name box is opened in the illustration so that you can
see how the range names in Excel compare to the table names in Visdata.
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.
WARNING: Visdata opens Excel data files for
exclusive use only. If you have an Excel spreadsheet open with Visdata, no other program
on your workstation, or any other program on the network, can open the same spreadsheet.
If some other program has an Excel spreadsheet open, you cannot open it using Visdata
until the other program closes that file.
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.
Figure 7.15. Viewing CH0702.XLS
with Excel.
Figure 7.16. Viewing CH0702.XLS
with Visdata.
Text Files Visdata can load various standard formats of ASCII text files for read-only
access. When you select a file to load (using the File Open dialog box), you actually open
the entire directory as a database. Visdata permits you to select any file with a .TXT
extension from the Database window and open it as a read-only data table. Figure 7.17
shows the file \TYSDBVB\SOURCE\DATA\TEXT\TXSAMPLE.TXT opened as a read-only data
file.
Figure 7.17. Opening a
text file with Visdata.
Visdata recognizes several types and formats of ASCII text files. The default format is
comma-delimited fields with character fields surrounded by quotes. ODBC The ODBC... menu
option is slightly different from the previously discussed Open commands. This option
enables you to use Visdata to open predefined ODBC data sources. When you select the
ODBC... menu option, you see a screen that asks you for the data source type, data source
name, user ID, and password for that data source (see Figure 7.18).
After you fill out the ODBC dialog box, Visdata
locates and opens the data source and updates the Database window.
Figure 7.18. Using
Visdata to open an ODBC data source.
Before you can open an ODBC data source, you must first define that data source using the
ODBC program from the Control Panel. You learn about defining and accessing ODBC data
sources in depth in Week 3. If you want more information on defining ODBC data sources,
you can refer to the help available when you load the ODBC programs from the Control
Panel.
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.
WARNING: Attempting to read a version 7.0
Microsoft Access database with Access version 2.0 or Visual Basic version 3.0 results in
an error that tells you your database is invalid or corrupt. If you know that you will be
working only with software that can read version 7.0
files, you should select the version 7.0 format because it provides additional features.
If, however, you plan to deploy your database in an environment that contains both 16- and
32-bit versions of the software (you use Visual Basic 3, or 16-bit Visual Basic 4), you
should stick with the version 2.0 data format.
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.19. Creating a
new Microsoft Access database.
Creating a new database does not automatically create data tables; you must use the New
command button in the Database window to create a new table. dBASE, FoxPro, and Paradox
Creating dBASE, FoxPro, and Paradox format databases is similar to creating Microsoft
Access databases. When you select one of these formats, you are prompted to indicate the
exact version of the database you want to create. After you select a version, Visdata
presents you with a simple dialog box that prompts you to enter a name for the database.
This name is not a data file; it is a file directory (called a folder in Windows 95). You
can include any valid drive designator and directory path you want when you create the
database. See Figure 7.20 for an example of creating a FoxPro database directory.
WARNING: Remember that Visdata creates
directories (or folders), not data files, when you create dBASE, FoxPro, or Paradox
databases. Be sure to use names that make sense as directories or folders.
Figure 7.20. Creating a FoxPro database directory.
Text You can use Visdata to create text data files. These files are comma-delimited ASCII
text files that you can open for read-only access from Visdata. Even though you can create
the database files and tables, you cannot add any data to the tables or create indexes on
the data tables. This might be useful if you want to create ASCII text data files for use
by other applications.
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
Database
The 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.
Figure 7.21. The
Import/Export dialog.
Next, select the Import command button. You are requested to select the database format
from which to extract data. See Figure 7.22 for details.
Figure 7.22. The Import
Data Format selection.
Select your database format and select OK. You are then presented with a dialog that
allows you to select a data table to import from the database you selected in the
preceding step. (See Figure 7.23.)
Figure 7.23. Selecting a
table to import.
Select the desired table and select the Import button to move the data into the currently
open database.
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.
Workspace
The 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).
Figure 7.24. Viewing the
Login dialog.
Workspace data objects are covered in detail on Day 10, "Creating Database Programs
with Visual Basic Code."
Errors
The Errors menu option shows the last error or set
of errors reported to Visdata (see Fig- ure 7.25).
Figure 7.25. Viewing the
errors collection.
Some data sources return more than one error message per transaction (usually ODBC data
sources), which is referred to as the errors collection. This menu option lets you review
the errors collection in a grid listing. If no errors have been returned, this grid is
empty.
TIP: Even if you have had several successful
database transactions since your last error, the most recent error remains in this grid
display.
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.
NOTE: Although not recommended, you can use
the Compact Database menu option to convert older database formats to newer ones, but you
cannot use the Compact Database menu option to convert newer formats to older ones. For
example, you cannot convert a 3.0 Microsoft Jet database to a 2.0 Microsoft Jet 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.
WARNING: Even though Visdata allows you to
compact a database file onto itself, this practice is not recommended. If anything happens
midway through the compacting process, you could lose some or all of your data. Always
compact a database to a new database filename.
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.
TIP: Remember to make copies of your database
on a regular basis. You should not depend on the Repair routine to recover all your data.
If you experience a program crash due to corrupted data, you can always restore the file
from the most recent backup. You should also use the Windows 95 or DOS defragment utility
on your hard drive after performing a Compact or Repair function to improve the overall
performance of your application.
Exiting
Visdata
The 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 Database
When 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 Database
If 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.
Figure 7.26. Creating CH07NEW.MDB.
Adding a New Table to the Database
To add a new table to the database, click the
alternate mouse button in an open space of the Database window and select New Table to
bring up the Table Definition dialog box. Your screen should look like Figure 7.27.
Figure 7.27. Defining a
new table.
Enter NewTable in the Table Name field at the top of the dialog box. Now you can add
fields to the data table. Click the Add Field command button to bring up the Add Field
dialog box. Your screen should look like Figure 7.28.
Figure 7.28. Adding a new
field to the table.
Enter the field name Field1. Set the type to Text and the Size to 10. Notice that you can
set default values and validation rules here, as well. We'll cover these properties on Day
9, "Visual Basic and the Microsoft Jet Engine."
After you have entered the information you need to
define the field, click the OK button to save the field properties to the database.
WARNING:: Be sure you click the OK button
after each field you define. If you just fill out the dialog box and then click the Close
button, the information you entered on the form won't be saved to the database.
Now that you have defined Field1, let's define one
more field. Enter Field2 as the name, and select Currency as the Field Type. Notice that
you cannot set the field size. Only Text type fields allow you to set a field size. Now
click the OK button to save this field definition; then exit the field definition dialog
by clicking the Close button. The Table Structure dialog box should now show two fields
defined. Refer to Figure 7.29 as a guide.
Figure 7.29. Table
Structure with two fields defined.
Editing an Existing Field
When you return to the Table Structure screen,
notice that the same set of properties you saw in the Add Field dialog box appears to the
right of the Fields list. You can edit some of these values for the field by highlighting
the field in the list on the left and editing the dialog values on the right. Make Field2
required by selecting the Required checkbox at the right side of the dialog box.
Building the
Table
Before you leave this screen, you must first click
the Build Table button to actually create the table in your database. Up to this point,
Visdata has stored the data table and index definitions in memory. Clicking the Build the
Table button is the step that actually creates the data table.
WARNING: If you click the Close button before
you click the Build the Table button, you lose all your table definition information. You
have to enter all the table definition data again before you can build the new table.
When you add data to an existing data table, you
cannot use Visdata to modify the table structure. You must first remove all records from
the data table before you can make any modification to the structure. You can, however,
add new fields to a table after data has been entered.
Adding a New
Index to the Database Using the Design Button
You can add indexes to existing tables by selecting
the table, clicking the alternate mouse button, and selecting Design... from the menu that
appears. This option brings up the same input form you used to add fields to the database.
Now let's add a Primary Key index for the NewTable you just created.
WARNING: Even though Visdata allows you to
enter New Index information during the New Tables process, you cannot build a new table
and a new index for the same table at one time. Visdata must see the data table that
already exists before it can create an index for that table. Use the Design mode of the
Table Structure dialog box to add indexes to existing tables.
Click the Add Index command button to bring up the
Add Index dialog box. Enter PKNewTable as the index name. Click Field1 in the field list
to make that field the source of the Primary Key index. Your screen should look like
Figure 7.30.
Be sure to click the OK button to add the index
definition to the database. When you have added the index definition, click Close to exit
the dialog. Your screen should now look like Figure 7.31.
Figure 7.30. Adding a new
index to the database.
Figure 7.31. The Table Structure dialog after adding
a new index.
Printing the Table Structure
While you are in the Design mode of the Table
Structure dialog, you can click the Print Structure button to get a hard-copy printout of
the selected table and index objects you have defined. Visdata sends the information
directly to the default printer defined for Windows and does not prompt you for any
options. Please note that the Print Structure button does not appear when creating a New
table; it appears only when you select Design after the table has been created.
TIP: If you want to save the structure to a
file, you can use the printer applet in the Control Panel to define a printer as a file,
and then set that print device as the default printer before you click the Print Structure
button in Visdata. Be sure to reset your default printer after you send your table
structures to a disk file.
The Visdata
Utility Menu
The Visdata Utility menu contains several options to
help you manage your data tables. You can create, test, and save query objects using the
Query Builder; build data entry forms with the Data Form Designer; perform global replace
routines on existing data tables; define attachments; define security; and define system
preferences.
Query
Builder...
The Query Builder serves as a good tool for testing
queries and then saving them to the database as query objects. You can later access these
objects from your Visual Basic 5 programs. The Query Builder enables you to perform
complex queries without having to know all the details of SQL syntax.
NOTE: We cover SQL SELECT queries in
detail on Day 8, "Selecting Data with SQL." For now, if you are not familiar
with SQL statements, just follow along with the example. The important thing to remember
is that you can use the Visdata Query Builder to create, test, and store SQL queries.
Let's build a query, test it, and save it in a
database. First, make sure you have guideS5.MDB open (found in the TYSDBVB5\SOURCE\DATA
directory on the CD included with this guide), and then select Utility | Query Builder...
from the main menu. You see a data entry form ready for your input (see Figure 7.32).
You have several options on this screen. It's easy
to get confused if you are not quite sure of what to look for. Instead of going through
all the possible options for a query, this example goes step-by-step through a rather
simple SELECT query and its results. Table 7.1 shows the values to select and
Figure 7.33 shows the completed form. Refer to these items as you build your query.
Figure 7.32. Using the
Query Builder.
Figure 7.33. The completed query.
Be sure to set the values in the screen in the order they appear in Table 7.1. After you
enter the Field Name, Operator, and Value settings, click the And into Criteria button to
force the settings into the Criteria box at the bottom of the window.
Table 7.1. Building a query.
| Property |
Setting |
| Tables |
guideSales |
| Field Name |
guideSales.Units |
| Operator |
> |
| Value |
14 |
| Fields to Show |
guideSales.Title |
|
guideSales.Units |
| Order by |
guideSales.Units, Desc |
After you have entered all the values, click Save and enter qryTest at the dialog prompt.
You have just saved the query for future use. Now try running it. Click Run to get Visdata
to execute the query. Click No when Visdata asks you if this is an SQL Passthrough query.
Visdata then executes the query and displays the results on your screen, as shown in Fig-
ure 7.34.
Figure 7.34. Results of
the executed query.
Data Form Designer...
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 guideS5.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!
Global
Replace...
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 guideS5.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."
Attachments...
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 guideS5.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 guideS5.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 |
If you are attaching to a data source that requires a password in the connect string, you
could check the AttachSavePWD checkbox to prevent a login dialog each time you open the
database. If you want to create an exclusive attachment, you could check the
AttachExclusive checkbox. Leave both of these fields blank for now.
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.
Groups/Users...
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.
SYSTEM.MD?
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.
Preferences
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 Visdata
Windows and Help Menus
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.
The Windows
Menu
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
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.
Summary
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:
- Open existing databases.
- Create new databases.
- Add tables and indexes to existing databases.
- Attach external data sources to existing Microsoft
Access databases.
- Access data using the three data access objects:
Table, Dynaset, and Snapshot.
- View data on-screen using the three data forms: form
view with the data control; form view without the data control; and grid view using the
data-bound grid.
- Build and store SQL queries using the Query Builder.
You learned to use Visdata to perform database
utility operations, including the following:
- Copying tables from one database to another
- Repairing corrupted Microsoft Access (Jet) databases
- Compacting and converting versions of Microsoft Jet
databases
- Performing global replace operations on tables
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.
Quiz
- 1. Where can you find a copy of the Visdata
source code?
2. How do you copy a table in Visdata?
3. When do you need to Refresh the Tables/Queries window?
4. Can you manipulate spreadsheet data with Visdata?
5. What information can be obtained from the Properties object in the Database
window?
6. Why would you compact a database?
7. Can you compact a database onto itself with the File | Compact MDB command?
8. Can you use Visdata to modify a table's structure once data has been entered?
9. Can you save queries in Visdata?
10. In what formats can you export data using the Visdata tool?
11. How would you use Visdata to convert an existing Access 2.0 database into an
Access 7.0 format?
Exercises
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.
- 1. Build a new database and name it Contacts.
This database should have a format that can be read by Microsoft Access 7.0.
2. Build a table of customers (tblCustomers). Include the following fields:
| 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 |
-
- 3. Build a primary key (PKtblCustomers) on the
ID field for the tblCustomers table.
4. Print the table structure for tblCustomers.
5. Create and enter five sample records into the tblCustomers table.
6. Because you also need to track those from whom you purchase, copy the structure
(no records) from tblCustomers to a new table, tblVendors.
7. Export the data in the tblCustomers table to a text file.
-
|
Learn HTML
