Microsoft KB Archive/108145

= VISDATA Example of Every Data Access Function in VB Prof 3.0 =

PSS ID Number: 108145

Article Last Modified on 1/8/2003

-

The information in this article applies to:


 * Microsoft Visual Basic Professional Edition for Windows 3.0

-



This article was previously published under Q108145



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 query words: 3.00

Keywords: KB108145

Technology: kbAudDeveloper kbVB300 kbVB300Search kbVBSearch kbZNotKeyword2 kbZNotKeyword6

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© 2004 Microsoft Corporation. All rights reserved.