Scripting ADODB:QTP

ADODB - Microsoft ActiveX Data Object 1. ADO is a Microsoft technology.
2. ADO stands for ActiveX Data Objects.
3. ADO is a Microsoft Active-X component
4. ADO is automatically installed with Microsoft IIS
5. ADO is a programming interface to access data in a database

ADO-ActiveX Data Objects
Microsoft® ActiveX® Data Objects (ADO) enable your client applications to access and manipulate data from a variety of sources through an OLE DB provider. Its primary benefits are ease of use, high speed, low memory overhead, and a small disk footprint. ADO supports key features for building client/server and Web-based applications.

RDS
Remote Data Service (RDS) is a feature of ADO, with 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.

OLEDB

OLEDB (sometimes written as OLEDB or OLE-DB) is an API designed by Microsoft for accessing different types of data stores in a uniform manner. It is a set of interfaces implemented using the Component Object Model (COM); it is otherwise unrelated to OLE. It was designed as a higher-level replacement for, and successor to, ODBC, extending its feature set to support a wider variety of non-relational databases, such as object databases and spreadsheets that do not
necessarily implement SQL. OLE DB separates the data store from the application that needs access to it through a set of abstractions, such as connections, record sets and attributes. This
was done because different applications need access to different types and sources of data and do not necessarily want to know how to access functionality with technology-specific methods. OLE DB is conceptually divided into consumers and providers. The consumers are the applications that need access to the data, and the provider is the software component that implements the interface. OLE DB is part of the Microsoft Data Access Components (MDAC) stack and is the database access interface technology. MDAC is a group of Microsoft
technologies that interact together as a framework that allows programmers a uniform and comprehensive way of developing applications for accessing almost any data store. OLE DB providers can be created to access such simple data stores as a text file or spreadsheet, through to such complex databases as Oracle, SQL Server and Sybase. However, because different data store technology can have different capabilities, OLE DB providers may not implement every possible interface available to OLEDB. The capabilities that are available are implemented through the use of COM objects - an OLE DB provider will map the data store technologies functionality to a particular COM interface. Microsoft calls the availability of an interface to be
"provider-specific" as it may not be applicable depending on the database technology involved. Additionally, however, providers may also augment the capabilities of a data store - these capabilities are known as services in Microsoft parlance.

ODBCThe Microsoft Open Database Connectivity (ODBC) interface is a C programming
language interface that makes it possible for applications to access data from a
variety of database management systems (DBMSs). The ODBC interface permits
maximum interoperability — an application can access data in diverse DBMSs
through a single interface. Furthermore, that application will be independent of any
DBMS from which it accesses data. Users of the application can add software
components called drivers, which interface between an application and a specific
DBMS.

The functions in the ODBC API are implemented by developers of DBMS-specific
drivers. Applications call the functions in these drivers to access data in a DBMSindependent
manner. A Driver Manager manages communication between
applications and drivers.

Applications that use ODBC are responsible for any cross-database functionality.
For example, ODBC is not a heterogeneous join engine, nor is it a distributed
transaction processor. However, because it is DBMS-independent, it can be used
to build such cross-database tools.

Connection.Open Method:
Syntax: object.Open (ConnectionString, UserID, Password, Options)

Connection.OpenSchema Method

Syntax: object.OpenSchema (QueryType, Criteria, SchemaID)

Example:
***************************************************************************

Option Explicit
Const adSchemaTables = &H14
Const adSchemaColumns = 4
Const adStateOpen = 1
Dim oConn, oRst, oRstSchema
Dim nRow
Dim sColumn
Set oConn = CreateObject("ADODB.Connection")
'--- Opening Database via DNS
oConn.Open "QT_Flight32"
'--- Open schema Tables
Set oRst = oConn.OpenSchema(adSchemaTables)
Do Until oRst.EOF
'--- Skip system tables
If StrComp(oRst("TABLE_TYPE").Value, "SYSTEM TABLE") <> 0 Then
sColumn = DataTable.LocalSheet.AddParameter( _
oRst("TABLE_NAME").Value, "").Name
'--- Querying Schema table columns
Set oRstSchema = oConn.OpenSchema(adSchemaColumns, _
Array(Empty, Empty, "" & oRst("TABLE_NAME").Value))
nRow = 1
DataTable.LocalSheet.SetCurrentRow nRow
Do While Not oRstSchema.EOF
DataTable(sColumn, dtLocalSheet) = _
oRstSchema("COLUMN_NAME").Value
oRstSchema.MoveNext
nRow = nRow + 1
DataTable.LocalSheet.SetCurrentRow nRow
Loop
End If
oRst.MoveNext
Loop
'--- Close the recordset schema if opened.
If oRstSchema.State = adStateOpen Then oRstSchema.Close
'--- Close the recordset if opened.
If oRst.State = adStateOpen Then oRst.Close
'--- Close the connection if opened.
If oConn.State = adStateOpen Then oConn.Close
Set oRst = Nothing : Set oConn = Nothing : Set oRstSchema = Nothing
***************************************************************************
OutPut:

OutPut: