Today you will learn everything you need to know about using one of the most valuable sample programs that is shipped with Visual Basic 4.0—The Visdata sample application. You learn how to compile the Visdata sample application, and how to turn it into a Visual Basic 4 Add-In. You also 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.
On Day 2, "Creating Databases," you learned how to use the Data Manager to build and manage databases. Although Data Manager is an excellent tool, Visdata contains additional features that make it more suitable for database development in the enterprise environment. Some of the key advantages of Visdata over the Data Manager include the following:
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 4 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 4 programs to back-end database servers using ODBC drivers.
Visdata is an excellent tool for constructing and managing databases for your Visual Basic 4 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 4 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 4 programs. You can also use Visdata to copy records from one table to another—even 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 4 data access engine.
Before you can take advantage of this great Visual Basic 4 program, you must first load and compile Visdata. To do this, you need to load and run Visual Basic 4 and then select File | Open Project from the main Visual Basic 4 menu. Now locate the SAMPLES\VISDATA subdirectory. This subdirectory is located directly under the home directory in which you installed Visual Basic 4.0. Find the VISDATA.VBP file and load it. Your screen should look like Figure 8.1.
Visdata Loaded into Visual Basic 4.
Once you load the project, select File | Make EXE File to compile the project into an executable file. Visual Basic 4 suggests a filename for you. If you are compiling using the 32-bit version of Visual Basic 4, you see the name VISDAT32.EXE. If you are compiling using the 16-bit version of Visual Basic 4, you see the name VISDAT16.EXE. It is recommended that you use the suggested filenames. Depending on your processor speed and available RAM, this might take a minute or two. Be patient because this is a large Visual Basic 4 project.
Once Visual Basic 4 completes its work, you'll have a fully executable version of Visdata on your system. Add it to the Visual Basic 4 program group so that you can access Visdata easily in the future. Now click the Visdata icon to start the program. An even easier way to start Visdata exists. You can make it a Visual Basic 4 Add-In application and add it to the Add-Ins menu in Visual Basic 4. You learn how to do this in the next section.
Visual Basic 4.0 has the capability to accept Add-In applications as part of its operating environment. Add-Ins are applications that are written to operate as if they are a part of the Visual Basic 4 development environment. These applications can also be designed to work independently of Visual Basic 4. The Visdata application operates in this way. Adding Visdata to the main menu of Visual Basic 4 can make it even easier to use the Visdata application to maintain your Visual Basic databases.
To make Visdata an Add-In application, you must first load and compile a small Visual Basic 4 project called VDADDIN.VBP. This short application registers Visdata as a class object and establishes it as an application that can be called by other OLE-compliant programs.
NOTE: The theory and practice of creating OLE-compatible class objects goes beyond the scope of this book. You can refer to the documentation that ships with Visual Basic 4.0 for more information on how to create OLE class objects.
Use Visual Basic 4 to load the VDADDIN.VBP project. The file appears in the same directory that contains VISDATA.VBP. Select File | Make EXE File. Visual Basic 4 supplies a default filename for the executable—do not change this name. When the compiler finishes, exit Visual Basic 4 completely.
CAUTION: You must exit Visual Basic 4 completely in order to make sure the Visdata class object gets properly registered and recognized as a valid Add-In the next time you load Visual Basic 4.
Load and run the compiled version of Visdata that you created in the previous part of this lesson. Select File | Make Visdata a VB Add-In from the Visdata main menu. You won't see anything happen on-screen, but by selecting this option, you instructed Visdata to call the vdaddin module you just created and use it to register Visdata as an OLE-compatible application. The next time you load Visual Basic 4, you can make Visdata an Add-In application.
Now re-load Visual Basic 4 and register Visdata as an Add-In. To do this, you must first select Add-Ins from the main Visual Basic 4 menu. Find the Visdata Add-In Stub entry and make sure the checkbox is filled in (see Figure 8.2).
Selecting the Visdata Add-In Stub.
By selecting this item, you tell Visual Basic 4 to add the Visdata application to the Add-Ins menu. Now, when you select the Add-Ins menu, you see the Visdata menu item (see Figure 8.3).
The New Visual Basic 4 Add-Ins Menu.
Now you can load the Visdata application by selecting it from this menu! Depending on the speed of your processor and the amount of RAM available on your machine, you may find running Visdata from the Visual Basic 4 menu a bit slow. If you want to speed up Visdata, you can load it directly from a Windows program group or from the Windows File Manager or Explorer shell.
Now let's start exploring Visdata.
If you don't already have Visdata running, start it now. You can start Visdata by selecting it from the Visual Basic 4 program group or, if you installed Visdata as an Add-In application, you can start it by selecting Add-Ins | Visdata.
NOTE: The rest of today's lesson discusses how to use the Visdata application. Although you do not have to have Visdata up and running to learn about it, you'll probably get more out of the lesson if you run the examples given here. If you have not already loaded and compiled the Visdata program, refer to the sections "Compiling the Visdata Project" and "Making Visdata an Visual Basic 4 Add-In" earlier in this chapter.
When you first start Visdata, the main startup screen appears (see Figure 8.4).
The Visdata Main Screen.
This MDI form is data central for the Visdata application. All database activity starts from this screen. Four major components to this screen deserve attention:
Now let's go through each of the four components of the Visdata main screen in a bit more depth.
The Visdata Main Menu contains four items before you load a database: File, Preferences, Window, and Help. After you load a database, additional items appear between the File and Preferences items. When you load a Microsoft JET format database, you see two additional items: Jet and Utility. If you load a non-Microsoft JET format database, you see only the Utility option added to the menu.
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 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). As an example, use Visdata to open an existing Microsoft JET database.
Select File | Open Database | Jet Engine MDB. The Visdata program presents you with an Open Jet Database dialog box (see Figure 8.5)
Opening a Jet Ending MDB Database.
Locate and select the CH801.MDB database that can be found in the \TYSDBVB\CHAP08 directory on the CD that ships with this book. Click the Open button to load the database. Once the database is loaded, Visdata updates the Table window to show all the primary data access objects in the currently opened database. Your screen should now look something like Figure 8.6
Visdata with an Open Database.
You can close the database by selecting File | Close Database from the Visdata main menu.
The Table window shows all the major data access objects in the currently opened database. The Table window is where you go to add new tables to the database (New button) and modify the design of one of the current tables (Design button). You can also use the Open button to add records to existing data tables. If you click the alternate mouse button within the Table window while you have a table highlighted, you see several other table management options.
NOTE: By using the term alternate mouse button, we're trying to avoid the left-handed/right-handed confusion. If you have your mouse set for left-handed use, choose the right button; if you have your mouse set for right-handed use, the alternate button is the left button.
The Properties option shows the various properties of the highlighted table. With the CH0810.MDB database open, highlight a table, click the alternate mouse button, and select Properties to view the data table's properties. Your screen should look like Figure 8.7.
Viewing the Table Properties.
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.
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; then click the OK button. Your screen should look like Figure 8.8 as you rename the Authors table.
Renaming a Data Table.
Before you continue with the project, change the MoreAuthors table back to Authors using the same technique previously described.
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 8.9.
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, 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.
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.
The Remove All Records option allows you to remove all the records from the highlighted table without deleting the table structure. To delete all records from a table, select NewAuthors from the list. Click the alternate mouse button to bring up the context menu and click Remove All Records. Visdata displays a confirmation message. Click OK to remove the records from the selected data table.
The Refresh option updates the window to reflect changes in the data access objects that are part of the database. Usually, Visdata refreshes the Table 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 Table window.
To refresh the Table window, you must first select one of the table items by clicking it once with the primary mouse button. Then, click once with the alternate mouse button to bring up the context menu. Select Refresh from the list. Visdata then refreshes the Table window to reflect the current state of the data access objects in the opened database.
The SQL window enables you to enter and execute standard SQL statements against the opened database. You can also save the SQL query for later use in your Visual Basic 4 programs.
Select the SQL window by clicking on the top border of the window one time. Now enter the following SQL query into the text window:
SELECT * FROM Authors
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 8.10.
Results of an SQL query.
NOTE: We will cover SQL SELECT Queries in depth in the lesson on Day 9, "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 4 programs by clicking the Save button in the SQL window and answering No when a dialog asks if this is an SQL PassThrough Query. Next, supply the query object name qryTest, and click OK in the dialog box that appears (see Figure 8.11).
Saving a query.
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 window.
Two sets of radio buttons appear near the top of the Visdata main screen. You use these buttons 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.
The first set of radio buttons controls the type of data access object Visdata uses to open the data table. The default data access object is the Visual Basic 4 Dynaset, the most flexible Visual Basic 4 data access object. You can use the Dynaset object to create updateable views of more than one table or open an existing table for read/write access.
You can 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 most all Visdata work, and they provide the most flexibility when dealing with multitable views.
The second set of radio buttons enables you to select the type of data form you see when you load your data access object. Visual Basic 4.0 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 DBGrid radio button to make this your default data form.
The other two radio buttons select two version of a standard data entry form. The first button, Data Control, loads the records from the data access object one at a time, using the Visual Basic 4 data control tool. The second radio button, No Data Control, presents a similar form, but without using the Visual Basic 4 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 DBGrid data form radio button. Now double-click the Authors table. This action brings up the Authors table in a grid display. Your screen should look like Figure 8.12.
Authors table using the grid data form.
Please note that the columns in this view can be re-sized. Simply select a column divider with your mouse and drag to the desired width.
Next, select the Data Control 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 8.13.
Authors table using the data control form.
Next, select the No Data Control button 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 No Data Control (as text) form and the Data Control form (checkbox). Figure 8.14 shows a tiled view of the three data forms side by side.
Three data forms side by side.
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 contains ten items. You can open and close databases from the file menu, log into a designated workspace, learn about the properties of the database, 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 can run a routine that registers Visdata as an Add-In Application, and you also exit the program from the File menu.
If you have used Visdata before, you see a list of the most recently used databases in this menu. You can reload one of those databases by clicking on its name in the File menu.
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 JET, dBASE, FoxPro, Excel, and text files using the 32-bit version. If you are running the 16-bit version, you can load Btreive format databases as well.
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 Attach 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 wish to access. The following sections cover each of the secondary menu choices and how you use them to open existing databases.
When you select the Jet Engine MDB option, Visdata brings up a File Open dialog box and prompts you to select the Microsoft JET database you wish to load (see Figure 8.15).
Loading a Microsoft JET database.
Visdata is able to load Microsoft JET version 1.1 and 2.0/2.5. If you are running the 32-bit version of Visdata, you can load the Microsoft JET version 3.0 databases. You do not have to tell Visdata which database format you are loading—it figures that out. If you are running the 16-bit version of Visdata and you attempt to load a database built using Microsoft JET version 3.0, you get an error message.
NOTE: Microsoft documentation is a bit fuzzy on the use of Microsoft JET 2.5 and Microsoft JET 2.0 databases. The 2.5 number refers to the Microsoft JET engine version number. This version of Microsoft JET was released as an interim upgrade to Visual Basic 3.0 to allow it access to Microsoft Access version 2.0 databases. Throughout the text the terms Microsoft JET 2.5 and Microsoft Access 2.0 are used interchangeably. Both names refer to the same physical database format.
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 or version IV format database.
CAUTION: 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 will 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.
Once you select the correct format, you see the File Open dialog box prompting you to locate and load a database. Once 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 8.16).
Viewing a loaded dbase database.
TIP: When you deal with non-Microsoft JET data formats, you get better speed performance if you access them via the Attach option. We cover the Attach menu option later in today's lesson.
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. Once 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.
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 \TYSDBVB\CHAP08 directory.
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 Table window (see Figure 8.17).
Using Visdata to directly load an Excel spreadsheet.
Figure 8.18 shows the sample Excel spreadsheet \TYSDBVB\CHAP08\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.
Viewing CH0802.XLS with Excel.
Figure 8.19 shows the same Excel file opened using Visdata. In Figure 8.19, the table object Sheet1$ has been opened as a Dynaset object.
Viewing CH0802.XLS with Visdata.
CAUTION: 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.
Once 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.
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 Table window and open it as a read-only data table. Figure 8.20 shows the file \TYSDBVB\CHAP\TXSAMPLE.TXT opened as a read-only data file.
Opening a text file from Visdata.
Visdata recognizes several types and formats of ASCII text files. The default format is comma-delimited fields with character fields surrounded by quotes. You can control this default with settings under the [Text ISAM] section of the VISDATA.INI file. You can also establish more complex text file interfaces by creating a SCHEMA.INI file in your default Windows directory.
The details of creating and maintaining .INI file settings for text files is beyond the scope of this book and is covered in the Visual Basic 4 Help file under the topic, "SCHEMA.INI."
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 8.21).
Using Visdata to open an ODBC data source.
Once you fill out the ODBC Dialog box, Visdata locates and opens the data source and updates the Table window.
Before you can open an ODBC data source, you must first define that data source using the ODBC (or ODBC32) program from the Control Panel. You learn about defining and accessing ODBC data sources in depth in the Week Three of the course. If you want more information on defining ODBC data sources, you can refer to the help available when you load the ODBC or ODBC32 programs from the Control Panel.
The Close Database menu option simply closes the open database. All tables are closed at the same time.
The Errors menu option shows the last error or set of errors reported to Visdata (see Figure 8.22).
Viewing 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.
The Properties menu item only appears after a database has been loaded into Visdata. This menu item has three submenu items:
Visual Basic 4.0 uses the Microsoft JET database engine for all database handling. Starting with Visual Basic 4.0, however, third-party vendors can design and install other database engines to work within Visual Basic . This screen allows you to view the basic properties of the current database engine. Figure 8.23 shows the dialog box that appears when you select the DBEngine menu item.
Viewing the DBEngine properties.
The Workspace menu item displays properties of the currently open workspace. Workspaces are collections of databases, users, and groups. You can log into a workspace to access related databases. When you select Workspace from the menu, you see a dialog box that lists the properties of the current workspace (see Figure 8.24).
Viewing the Workspace properties.
We cover workspace data objects in detail in the Microsoft JET chapter on Day 10.
The Database menu option shows all the properties of the currently opened database, including the database format, any connection strings that are in effect, the format version number, and several other parameters about the database. Figure 8.25 shows the Database Properties dialog box for a typical Microsoft Access database.
Viewing the Database properties (32-bit).
NOTE: The last three fields are not available in the 16-bit version.
We cover the meaning and use of these property settings in the Chapter on Microsoft JET on Day 10.
The New menu option enables you to use Visdata to create entirely new databases in several formats. This section concentrates on the Microsoft JET 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.
When you select the JET menu item, Visdata asks you to select one of three versions of Microsoft JET data format: 1.1, 2.0, or 3.0. (Please note that the 16-bit version of Visdata does not support the 3.0 JET data format). The 1.1 format can be read by all versions of Microsoft Access and Microsoft Visual Basic versions 3.0 and later. The version 2.0 format can be read by Microsoft Access version 2.0 and by Visual Basic versions 3.0 and later. Version 3.0 format databases can only be read by 32-bit Visual Basic 4.0 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 3.0 format is that it allows for additional database properties that are not available in the older formats.
CAUTION: Attempting to read a Version 3.0 Microsoft JET 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 3.0 files, you should select the version 3.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 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 prompting you to enter a filename for the new database (refer to Figure 8.26).
Creating a new Microsoft JET database
Creating a new database does not automatically create data tables; you must use the New command button in the Table window to create a new table. We will cover creating and modifying tables in the another section later in this lesson.
Creating dBase, FoxPro, and Paradox format databases is similar to creating Microsoft JET databases. When you select one of these formats, you are prompted to indicate the exact version of the database you wish to create. Once you select a version, Visdata presents you with a simple dialog box prompting you to enter a name for the database. This name will not be a data file; it will be a file directory (called a folder in Windows 95). You can include any valid drive designator and directory path you wish when you create the database. See Figure 8.27 for an example of creating a FoxPro database directory.
Creating a FoxPro database directory.
CAUTION: Remember that Visdata creates directories (or folders), not data files, when you create dBase, FoxPro, or Paradox databases. Make sure to use names that make sense as directories or folders.
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 that will be used by other applications.
When you select the text menu option, Visdata prompts you to enter a name for the database. This name will be used to create a directory (Windows 95 folder) on the designated drive. You can use any valid device designator and directory path you wish when you create the database.
You can use Visdata to compact existing Microsoft JET databases. Compacting a database removes empty space in the data file once occupied by records that have been deleted. Running the Compact menu option also reorganizes any defined indexes stored in the database.
You can also use the Compact Database menu option to upgrade the version of Microsoft JET database formats. For example, if you have a database built in the Microsoft JET 1.1 format, you can convert it to the Microsoft JET 3.0 format using the Compact Database menu.
NOTE: You can only use the Microsoft JET version 3.0 format if you are running applications in 32-bit mode. The Microsoft JET version 3.0 is not available in 16-bit mode.
When you select Compact Database, you have to select a database format. If you select 3.0 from this menu, the database you selected will be compacted and stored as a Microsoft JET version 3.0 database. If you select 1.1 from this menu, the database you select will be compact and stored as a Microsoft JET version 1.1 database.
NOTE: 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.
Once you select the target format, you see an File Open dialog box asking you to select the database you wish 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.
CAUTION: 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 loose 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 in Day 20, "User Security."
If you get a database corrupt error attempting 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 pates, and so on). You can repair an existing database by selecting Repair Database from the File menu. You then see an 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. 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.
The Make Visdata a VB Add-In menu option runs a short compiled program (VDADDIN.EXE) which makes updates to the Windows registry files. These updates make it possible to run Visdata from the Visual Basic 4 Add-Ins menu option.
If you have not already done so, you can make Visdata an Add-In application by selecting this option. You must first load and compile the VDADDIN project. For more information on making Visdata an Add-In application, refer to the sections "Compiling the Visdata Project" and "Making Visdata an Visual Basic 4 Add-In" discussed earlier in this chapter.
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 will be 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.
Once 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 JET database, add a new table, a new index, and then modify the table structure.
If you haven't already done so, load and start Visdata. Select File | New | JET | 2.0 from the main menu and enter CH08NEW.MDB in the Select Jet Database to Create dialog box (see Figure 8.28). Click the Save button to create the new database.
Creating CH08NEW.MDB.
To add a new table to the database, click the New command button in the Table window to bring up the Table Definition dialog box. Your screen should look like Figure 8.29.
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 8.30.
Adding a new field to the table.
Enter the field name Field1. Set the type to Text and the length to 10. Notice that you can set default values and validation rules here, as well. We'll cover these properties in Day 10 later this week.
After you have entered the information you need to define the field, click the OK button to save the field properties to the database.
CAUTION: Be sure you click the OK button after each field you define. If you 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, 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 8.31 as a guide.
Table Structure with two fields defined.
When you return to the Table Structure screen, you will 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 any one 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.
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.
CAUTION: If you click the Close button before you click the Build 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.
Once 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.
You can add indexes to existing tables using the Design command button of the Table window. This button brings up the same input form you used to add fields to the database. Now add a Primary Key index for the NewTable you just created.
CAUTION: 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. The 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. Double-click Field1 in the field list to make that field the source of the Primary Key index. Your screen should look like Figure 8.32.
Adding a new index to the database.
Be sure to click the OK button to add the index definition to the database. Once you have added the index definition, click Close to exit the dialog. Your screen should now look like Figure 8.33.
The Table Structure dialog after adding new index.
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.
If the currently open database is a Microsoft JET format database, you will see the JET item on the main menu. This item lets you establish attachments to external databases, define relationships between tables in your database, manage security groups and users, and set parameters for dealing with multiple-users for a single database.
Visdata allows you to attach external database files to an existing Microsoft JET format database. When you create an attachment, you actually create a link between your own Microsoft JET 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 JET tables. Attached tables appear in the Table window as local table objects in your database, even though they are only links to external data files.