Please support VBWM through our sponsors


Articles

Beginners' Pages

Online magazines

Vendors pages

Job Market

News Group Express

World of Sites

International Vendors

Tips and Tricks

Advertising

Mission

Reader Surveys

Register for reduced prices and FREE stuff

 An introduction to developing web database applications using Microsoft Data Access Components and Visual Basic 5.0

By Karthik Ravindran

 

This article presents an overview of a relatively new approach to design and develop web database applications using Microsoft Data Access Components and Visual Basic 5.0. These technologies have been around for some time, and are being gradually recognized as effective web application development tools. Currently available documentation on MDAC is very limited, and integrating VB with MDAC to design web database applications is a topic which has not been addressed well enough in any VB book till date. The objective of this paper is to introduce the readers to a powerful approach, which can be applied effectively to build Internet/Intranet solutions using these technologies.

The presentation will adopt a case based approach, wherein an example application [ RINFO ] will be used to illustrate the architecture & concepts which one must understand in order to design & develop web database systems using MDAC & Visual Basic 5.0

 

The Design & Implementation of RINFO :

RINFO [ Revenue Information ] is a Revenue monitoring application being designed & developed by the R&D department of ‘TechnoWizards’ [ a fictitious S/W firm ], for it’s top level management team.

It is a typical database application, which will be used to store data pertaining to the flow of resources & revenue across the different client sites of ‘TechnoWizards’. Project Account Managers will use the system to record resource and revenue information pertaining to their respective sites, while the top level management comprising of the Business Managers, Regional Directors, and the Vice President, will use it to review the entered information and make strategic decisions. In addition to this the system will also include several inbuilt reporting functionalities which will enable the users to view the information is different formats.

Considering the global nature of the application and the geographical locations of the users who will be using it, the decision to go in for a web based architecture was not a difficult one to make. The tough issue was the choice of technology, and after much R&D it was decided that Microsoft Data Access components, and Visual Basic 5.0 would be used as the deployment tools. The reason for this choice was two fold, the first being the ease with which one could write a web database application using these technologies, and the second of course was the opportunity to learn about a new set of components which have the potential to capture a significant share of the Internet/Intranet applications development market.

In order to get the functionalities approved by the intended users, it was decided that to begin with version 1.0 of RINFO would be a stand alone system which the users could install on their PC’s and play around with to get a feel of the application. This version was developed using Visual Basic version 5.0 for the front end screens & MS-Access as the database server. It was reviewed & approved by the top level management of ‘TechnoWizards’, who gave the go signal to proceed and web enable the application as planned.

The objective for release 2.0 is to take the stand alone version of the application, and migrate it to an Internet/Intranet environment. It was found that the changes needed to migrate one of the application’s screens to an Internet downloadable ActiveX document, and to make it connect to a database located on a web server required very minimal code change. Currently the remaining screens & functionalities are being web enabled, and it is estimated that the effort should be completed by early October 1998.

 

The Architecture of RINFO version 2.0 :

 In version 2.0 of RINFO, the application and its database will reside on the Internet server. Individual users can use Internet Explorer to connect to the server [ by specifying the appropriate URL ], and execute the application. The application’s front end screens will be implemented in the form of Internet downloadable ActiveX documents [ developed using VB 5.0 ] which can be downloaded and execute from within the browser window.

Once activated, the application can be configured to connect to either a local database on the user’s PC, or to the central database located on the web server.

Account Managers will use the application to record & modify the data in their respective local databases. Periodically they will execute a ‘Sync’ functionality to update the central database with their latest local data.

The top level management will use the application to connect to the central database, and review the resource & revenue information recorded by the AM’s from the different client sites.

Data on the central database cannot be modified directly. If the top level management folks wish to manipulate the central data to perform a ‘what-if’ analysis, they will have to download [ using the ‘Download’ functionality ] the same to a local database on their PC. The application can then be configured to connect to their local database, on which the desired data manipulations can be performed.

The only required installation is that of the Access database [ .mdb file ], which needs to be present on the PC’s of all intended users. A feature to download and install an empty application database from the web server is currently being incorporated.

 

Implementation technologies for Release 2.0 of RINFO :

The following technologies will be used in implementing version 2.0 of RINFO :

  • Microsoft’s Data Access Components : which includes the RDS [ Remote Data Services ], and the ADO { ActiveX Data Objects ] Object Libraries - These are required to establish a connection with a remote database server, and to manipulate remote data recordsets [ More about these later… ].
  • Microsoft Visual Basic version 5.0 : to implement the front end screens [ in the form of Internet downloadable ActiveX documents ] required to capture & display project resource & revenue information.
  • Microsoft Access version 7.0 : as the backend database.
  • Windows NT version 4.0 & Microsoft’s IIS : The Internet/Intranet server platform which will host RINFO, and the application’s database. Individual users will connect to this server using Internet Explorer to run the application, which will download & execute on their PC’s as an ActiveX document.

 

The following sections will briefly describe the MDAC technology. Code snippets will follow the descriptions to introduce some of the basic programming required to connect to the database on a web server, and manipulate the remote data. Advanced concepts such as security, and concurrency are not addressed in this article. More information about implementing these features using MDAC can be found in the on-line documentation which can be downloaded from http://www.microsoft.com/data.

 

A Brief Introduction to MDAC [ Microsoft’s Data Access Components ] :

What are the Microsoft Data Access Components ?

The Microsoft Data Access Components provide an easy-to-use, programmatic access to a variety of data across the enterprise. Data driven client/server applications deployed over the Web or a LAN can use these components to easily integrate information from a variety of sources, both relational (SQL) and non-relational. Microsoft Data Access Components consist of ActiveX Data Objects (ADO) and Remote Data Service (RDS), the Microsoft OLE DB Provider for ODBC, and Open Database Connectivity (ODBC) which are released, documented, and supported together.

Of these the ADO & RDS components will be used in the implementation of RINFO version 2.0

 

What is RDS ?

Remote Data Service brings high-performance database connectivity to Web-based applications. You can use Remote Data Service to build intelligent multi-user Web and LAN-based applications that let users access and update data from any OLE DB Provider, including ODBC compliant database management systems. Remote Data Service can be implemented with familiar technology such as off-the-shelf visual controls, HTML, and Microsoft Visual Basic Scripting Edition (VBScript) to name a few. Remote Data Service integrates seamlessly with existing Visual Basic applications, letting you transport them to the Web.

 

What is ADO ?

ActiveX Data Objects (ADO) enables you to write an application to access and manipulate data in a database server through an OLE DB provider. In the development of RINFO it will be used to locally manipulate the recordsets obtained from querying the central database on the web server. It’s programming model is very similar to DAO [ Data access objects ] , which most Visual Basic programmers are bound to be familiar with.

 

ADO & Remote Data Service (RDS) together provide features which facilitate the implementation of data remoting, by which you can move data from a server to a client application or Web page, manipulate the data on the client, and return updates to the server in a single round trip.

In the implementation of RINFO, the RDS objects are used to establish the connection with the remote database located on the web server, and to fire the queries required to retrieve data from it. The data returned is in the form of an ADOR [ ActiveX Data Objects recordset ] which can be manipulated programmatically on the client side. Once the required additions, updates, and deletions have been performed on the ADOR, the changes can be submitted as a batch to the remote server using RDS. The Code examples in the following sections will help illustrate the process.

RDS was previously released as Advanced Data Connector 1.0, and more recently has been combined with the ADO programming model to simplify client-side data remoting.

For more details and an extensive documentation on MDAC, check out the web site

http://www.microsoft.com/data

 

 

Code examples from RINFO :

The following code examples will practically illustrate how to establish a connection to a remote web database, and manipulate the recordsets retrieved from it using RDS & ADO. Familiarity with VB coding will be required to understand these segments. For applications that use ADO & RDS, the following References will have to be added to the Visual Basic project :

  • Microsoft Remote data services 1.5 library
  • Microsoft Remote data services server 1.5 library
  • Microsoft ActiveX Data objects recordset 1.5 library

 

 


Example 1 : Establishing a connection & retrieving a recordset from a database located on a remote web server

 

Before studying the code, it is essential to gain an understanding of the DataSpace & Datafactory classes exposed by the RDS libraries.

‘Dataspace’ is a class provided by the Microsoft Remote data services 1.5 library to create client side proxies to custom business objects located on a server.

The DataFactory class is a component of the Microsoft Remote data services server 1.5 library. It is a generic server side business object that receives and process’s client requests to read/write data located on a server database.

In the following example, the RDS.Dataspace object in used to create a proxy to an RDSServer.DataFactory object on the remote server. This proxy is then used to retrieve a recordset from a datasource present in the server [ in this case the remote server is the web server on which the application’s database is located ] . The recordset retrieved will be an ADOR recordset which can manipulated on the client side.

 


‘Define a new DataSpace object

Dim objRDSDataSpace As New RDS.DataSpace

‘Define an ADOR.recordset object to receive the data retrieved from the Remote ‘server

Dim objADOR As ADOR.Recordset

 

‘ Define a generic object which will serve as a proxy for the generic server object

‘ RDSServer.DataFactory

Dim objRDSDataFact As Object

‘Dim strconnect, sqlstr As String

 

‘Create a proxy to the RDSServer.DataFactory object on the remote server.

Set objRDSDataFact = objRDSDataSpace.CreateObject("RDSServer.DataFactory",

"http://a338994")

‘Specify the connection information for the ODBC data source on the server ‘which you wish to access

strconnect = "dsn=RMS;UID=;PWD=;"

‘Retrieve an ADOR recordset from the remote server by using the ‘Query’ method ‘of the RDSServer.DataFactory object.

Set objADOR = objRDSDataFact.Query(strconnect, "Select * from Customer where CustomerId = 'ABC' ")


 

 

Example 2 : Modifying data in an ADO Recordset from within the client application

This example is an extension of the previous one. It is assumed that the telephone number for the customer ABC was not entered when the record was added. In the previous example, the ‘objADOR’ is the ADOR.Recordset object which receives the resultset of the query executed to retrieve the record of customer ABC from the Customer table in the remote database. In the following segment the telephone number field will be populated, and the change will be applied back to the server.


‘Define a new DataSpace object

Dim objRDSDataSpace As New RDS.DataSpace

‘Define an ADOR.recordset object to receive the data retrieved from the Remote ‘server

Dim objADOR As ADOR.Recordset

 

‘ Define a generic object which will serve as a proxy for the generic server object

‘ RDSServer.DataFactory

Dim objRDSDataFact As Object

‘Dim strconnect, sqlstr As String

 

‘Create a proxy to the RDSServer.DataFactory object on the remote server

Set objRDSDataFact = objRDSDataSpace.CreateObject("RDSServer.DataFactory",

"http://a338994")

‘Specify the connection information for the ODBC data source on the server ‘which you wish to access

strconnect = "dsn=RMS;UID=;PWD=;"

‘Retrieve an ADOR recordset from the remote server by using the ‘Query’ method ‘of the RDSServer.DataFactory object.

Set objADOR = objRDSDataFact.Query(strconnect, "Select * from Customer where CustomerId = 'ABC' ")

‘The value in the Phone field of the retrieved recordset is updated.

ObjADOR.Fields("Phone").value = ‘813-738-3000’

‘The updated recordset is submitted to the server by calling the ‘SubmitChanges’

‘ method of the DataFactory object

ObjRDSDataFact.SubmitChanges strconnect,objADOR


 

 

Example 3 : Deleting a record in an ADOR recordset from within a client application

This example will once again be an extension of the Example 1. In this example the customer record retrieved will be deleted, and the changes will be applied back to the remote database.


‘Define a new DataSpace object

Dim objRDSDataSpace As New RDS.DataSpace

‘Define an ADOR.recordset object to receive the data retrieved from the Remote ‘server

Dim objADOR As ADOR.Recordset

 

‘ Define a generic object which will serve as a proxy for the generic server object

‘ RDSServer.DataFactory

Dim objRDSDataFact As Object

‘Dim strconnect, sqlstr As String

 

‘Create a proxy to the RDSServer.DataFactory object on the remote server

Set objRDSDataFact = objRDSDataSpace.CreateObject("RDSServer.DataFactory",

"http://a338994")

‘Specify the connection information for the ODBC data source on the server ‘which you wish to access

strconnect = "dsn=RMS;UID=;PWD=;"

‘Retrieve an ADOR recordset from the remote server by using the ‘Query’ method ‘of the RDSServer.DataFactory object.

Set objADOR = objRDSDataFact.Query(strconnect, "Select * from Customer where CustomerId = 'ABC' ")

‘The retrieved record is deleted. The flag adAffectCurrent ensures that only the ‘current record in the retrieved recordset is deleted. In this case only a single ‘record is retrieved, however when a group of records are obtained from a remote ‘database, specifying this flag will ensure that only the record to which the ‘recordset’s pointer is currently pointing will be deleted.

ObjADOR.Delete adAffectCurrent

‘The updated recordset is submitted to the server by calling the ‘SubmitChanges’

‘ method of the DataFactory object

ObjRDSDataFact.SubmitChanges strconnect,objADOR


Recordsets retrieved from a remote database as in the examples above can be programmatically bound to visual controls on a standard Visual Basic form, thereby making it possible to create front-end screens for web data. These forms can in turn be migrated to Internet downloadable ActiveX documents which can be installed on the web server, and downloaded & executed by the individual users using Internet Explorer.

 

Summary :

What I have touched upon here is only an introduction to these exciting technologies which can be used to develop Internet applications. The objective was to make more folks aware about the existence of these components which are alternate routes to writing web based systems, as opposed to using other widely publicized technologies like Java, HTML, and CGI scripts. For indepth information and documentation pertaining to ADO & RDS visit the website http://microsoft.com/data. You can also download MDAC 1.5 from this site. For more information on the ActiveX technology, and ActiveX documents, refer to the ‘Component tools guide’ which is a part of the documentation set that ships with Visual Basic 5.0. If you have any questions about this article feel free to mail me at Ravindk@tvratings.com


About the Author

Karthik Ravindran holds a Masters Degree in Information Technology from the University of East London, and Microsoft certifications in VB & SQL Server. He works for Cognizant Technology Solutions [headquarters in Manhattan, NY ], which is a reputed S/W consultancy firm with a large worldwide client base. Currently he is located at 'Nielsen Media Research' in the city of Dunedin, FL[ a leading Media Ratings organization, and a client of CTS ], where he is responsible for coordinating a series of C/S Year 2000 Impact Analysis & Testing projects. Apart from this he is also leading an internal web database application development effort which uses technologies like MDAC, ActiveX, and VB 5.0. His research interests include web databaseapplication development, and tools for automated S/W testing and QA. He can be reached by e-mail at RavindK@tvratings.com.


Shout! Earn money and Get hits
Shout! Sponsor your site and earn up to 15 cents per click!

Click Here to Visit Our Sponsor

Please visit our sponsors!