VISDATA Example of Every Data Access Function in VB Prof 3.0

Last reviewed: August 7, 1995
Article ID: Q108145
The information in this article applies to:
  • Professional Edition of Microsoft Visual Basic for Windows, version 3.0

SUMMARY

The VISDATA.MAK file installed in the VB3\SAMPLES\VISDATA directory loads extensive examples of data access. The VISDATA sample program uses every data access function in Visual Basic. You can refer to the VISDATA source code for examples of how to use each data access function.

MORE INFORMATION

The following description of the VISDATA sample program is taken from the VB3\SAMPLES\SAMPLES.TXT file:

                  VISDATA EXCERPT FROM SAMPLES.TXT
                  ================================
...

6: ODBC

Program example using ODBC and the VT (Virtual Table) object layer.

NOTE: To access ODBC data sources with this sample you must

       first install ODBC using the ODBC setup program provided
       with Visual Basic Professional Edition.

BRIEF DESCRIPTION:

This sample program illustrates various programming techniques used to access data through the VT layer built into Visual Basic Professional. It behaves like a general purpose database utility capable of the following functions:

   1. Table Creation
   2. Table Modification (adding and deleting fields and indexes)
   3. Data Browsing/Modifying one record at a time using a
      dynaset or a table
   4. Data Browsing via the Grid control (non-updatable)
   5. Data Browsing/Modifying via the new Data Control
   6. Data Export to Tab Delimited text file
   7. Direct SQL statement execution for any SQL supported
      functions such  as Insert, Update, Delete, Drop, Create,
      and Dump
   8. AdHoc Query tool that helps users unfamiliar with SQL
      create complex queries with where clauses, joins, order
      by and group by expressions while limiting output to
      selected columns
   9. Transaction Processing
  10. Copying table structures and data to same or different
      server
  11. Support of Microsoft Access, dBASE 3, dBASE 4, FoxPro 2.0,
      Paradox 3.x, Btrieve, SQL and Oracle data, both DDL and DML

The code contains comments to help explain the use of the various methods in the data access layer. Code and forms may be copied from this application to other applications with minimal modification.

ODBC BACKGROUND:

ODBC (Open DataBase Connectivity) is a standard adopted by multiple vendors designed to enable users to connect to any data source with a single application. This is achieved through a layered approach including:

   1. Programming Layer -- embedded functions in the development
      tool, which in this case is Visual Basic Professional Edition.
   2. Driver Manager -- the basic ODBC library that routes calls
      to the appropriate driver.
   3. Data Driver -- the library of functions that acts upon a
      specific database backend such as SQL Server, Xbase,
      Excel, etc. (note that SQL Server is the first of many
      drivers to become available for ODBC)

These layers work together to enable data access from any source for which an ODBC driver exists. The sample application will work, without modification, on any new, level-one ODBC driver that becomes available. With multiple drivers, connections may be made to different data sources from the same application at the same time enabling seamless data access from disparate data sources.

FILES:

   ABOUTBOX.FRM......Standard "About box" for the application.
   ABOUTBOX.FRX......Icon for the "About Box".
   ADDFIELD.FRM......Form to add fields to Tables.
   CPYSTRU.FRM.......Form to copy Table structures.
   DATABOX.FRM.......General purpose list form.
   DYNAGRID.FRM......Form used to display data in a Grid control.
   DYNAGRID.FRX......Icon for DYNAGRID.FRM.
   DYNASET.FRM.......Form to display data in single record mode.
   DYNASET.FRX.......Icon for DYNASET.FRM
   FIND.FRM..........Form used to find records in a Dynaset.
   INDEXADD.FRM......Form used to add indexes to Tables.
   JOIN.FRM..........Form used to add Joins to the Query Builder.
   OPENDB.FRM........Form used to open a database.
   QUERY.FRM.........Form used to build Queries.
   QUERY.FRX.........Icon for QUERY.FRM.
   REPLACE.FRM.......Form to perform global replaces on a Table.
   REPLACE.FRX.......Icon for REPLACE.FRM.
   SEEK.FRM..........Form used to get input for Seek function on
                     Table form.
   SQL.FRM...........Form to enter and execute SQL statements.
   SQL.FRX...........Icon for SQL.FRM.
   TABLES.FRM........Form used to display table lists.
   TABLES.FRX........Icon for TABLES.FRM.
   TABLEOBJ.FRM......Form used to display data in Table object
   TABLEOBJ.FRX......Icon for TABLEOBJ.FRM
   TBLSTRU.FRM.......Form to display and modify table structures.
   VDMDI.FRM.........Main MDI form for the application.
   VDMDI.FRX.........Icon for VDMDI.FRM.
   VISDATA.BAS.......Support functions for the application.
   VISDATA.ICO.......Icon for the applicaiton.
   VISDATA.MAK.......Make file for applicaiton.
   ZOOM.FRM..........Form to zoom in on character data in the
                     dynaset forms.

TO RUN:

After starting the Visual Basic environment (VB.EXE), you can load files in this sample program by choosing Open Project from the File menu and selecting the VISDATA.MAK file in the SAMPLES\VISDATA directory.

If you want to open a local database, you need to choose the type of database and a file open common dialog will be provided with the file type set to the requested data file type.

If you choose ODBC from the File/Open menu, the next dialog you will see is the Open DataBase form. Because you probably have no servers entered, you will need to enter a name for an existing SQL server on your network. If you already know the user ID and password, you can add them as well. The Database name is optional. Once you have entered this data, choose Okay. Now, you should be able to log on to the server. You may get some more dialogs in the process. Answer any questions you can and ask the SQL administrator for help if you run into problems or don't know some of the parameters.

Once a database is open, double-click a table name to open the table in the selected mode (Single Record or Table View). Use the Query Builder to create dynasets with selected data from one or more tables at a time. If you choose Use Data Control or Use Grid, a dynaset will be created. However, the following objects will be created under the following circumstances when you choose No Data Control:

Data Type       Feature Chosen    Object Type Created
MS Access       Table Open        Table
MS Access       Query Open        Dynaset
MS Access       Execute SQL       Dynaset
ISAM            Table Open        Table
ISAM            Execute SQL       Dynaset
ODBC            Any               Dynaset

The table is always updatable and the dynaset will be updatable in most cases except on ODBC with no unique index, certain multiple table joins, and other SQL select statements such as count(*) or max().


Additional reference words: 3.00
KBCategory: kbprg
KBSubcategory: APrgDataOther


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: August 7, 1995
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.