Viz Pilot User Guide

Version 8.0 | Published May 30, 2017 ©

Database Components


Database components provide the means to connect to any database that can be used with the Open Database Connectivity (ODBC) API.

There are basically two ways of setting up database communication. Either by using the ActiveX Data Objects (ADO) components or by writing scripts without using database component. The latter is often a preferred solution as it enables users to find more elaborate and complete script examples on the Internet than what is covered in this user manual.

Note: A lot of examples on the Internet use the Server. notation as a lot of scripts are used for accessing a web server. This is not needed with Template Wizard.

The most common ADO components available are ADO Connection Component, ADO Query Component, ADO Command Component, ADO Table Component, and ADO DataSet Component.

Not all components are needed at once to establish a database connection and to retrieve or store data. To understand their purpose and area of use, please read the following subsections.

Note: An ADO provider represents one of a number of types of access, from native OLE DB drivers to ODBC drivers. These drivers must be installed on the client computer. OLE DB drivers for various database systems are supplied by the database vendor or by a third-party.

This section contains the following topics:

  • Setting Up a Basic Database Connection

  • ADO Connection Component

  • ADO Query Component

  • ADO Command Component

  • ADO Table Component

  • ADO DataSet Component

Setting Up a Basic Database Connection

Two ways of setting up a basic database connection are described below:

  • Example I - Using scripting

  • Example II - Using components
    Both scripting examples use a standard Microsoft Access database. The database contains a table (dbTable) that consists of the following fields; id, headline and source.

The template itself has three labels and listboxes corresponding to the database table fields.

Example I - Using scripting

In this example, database components are not used, as using them would not show all the possibilities of scripting.


  1. Initialize variables and values. Option Explicit requires that all variable names be defined (with the Dim statement). Const is similar to a variable, except that its value cannot be changed at runtime.

    Option Explicit
    Const adCmdText = 1
    Const adStateOpen = 1
    Dim cnVizrt
    Dim cmdQuery
    Dim rsVizrtStory
  2. Create a database connection using the Connection object.

    Set cnVizrt = CreateObject("ADODB.Connection")
  3. A minimum set of connection properties can be set. In this case the Provider and Data Source. In this case User ID and Password is also provided. If none is required the sentence can be removed.

    cnVizrt.ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "User ID= SomeUser; Password; SomePassword; " & _
    "Data Source=C:\test.mdb"
  4. Open the database.

  5. Create a query string using the Command object.

    Set cmdQuery = CreateObject("ADODB.Command")
    With cmdQuery
    Set .ActiveConnection = cnVizrt
    .CommandText = "SELECT id, headline, source " & _
    "FROM dbTable " & _
    "WHERE source = 'Vizrt'"
    .CommandType = adCmdText
    Set rsVizrtStory = .Execute
    End With
  6. Test to see if the database connection was successful.

    If rsVizrtStory.State = adStateOpen Then
    MsgBox("Recordset opened successfully.")
    End If
  7. Clear redundant data from the listboxes

  8. Add data from the database to text fields.

    Do While Not rsVizrtStory.EOF
  9. Destroy all objects from memory that no longer is needed.

    Set rsVizrtStory = Nothing
    Set cmdQuery = Nothing
    Set cnVizrt = Nothing

Example II - Using components

Setting up a database connection is also possible using database components. A simple setup can be described as follows:

  1. Add an ADO Connection component to the template and set the following properties:

    • ConnectionString: Information needed to connect the ADO Query component;

      Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\test.mdb;
    • Provider: This property is auto generated based on the connection string property.

  2. Add an ADO Query component to the template and set the following properties:

    • Connection: Select the ADO Connection name from the drop-list.

    • SQL: Click the browse button and enter the query string. For example:

      SELECT id, headline, source FROM dbTable WHERE source = 'Vizrt';
  3. Check the Connected property in the Object Inspector for the ADO Connection component. A Database login window appears asking for User Name and Password. If one is not set, click OK.

  4. Check the Active property in the Object Inspector for the ADO Query component.


  5. Open the ADO Query context menu by right clicking the component icon on the template editor. Click the Fields Editor ... menu option.


  6. Select fields by clicking the “arrow and plus” button in the Fields Editor window. If the connection is not active a dialog appears asking for User Name and Password.


  7. Add Unicode labels and listboxes to the template.

  8. The following script will list the result from the ADO Query component in the listboxes.

    Option Explicit
    Dim index
    ADOConnection1.Connected = True
    ADOQuery1.Active = True
  9. Test to see if the database connection was successful.

    If ADOQuery1.State = 1 Then
    MsgBox("Recordset opened successfully.")
    End If
  10. Test to see how many records found based on the SQL statement.

  11. Clear listboxes.

  12. Before iterating a record set it is recommended to reset the cursor in order to start from the top when running the script several times.

  13. The For statement iterates according to the RecordCount -1 adding data to the listboxes. Since the loop starts at 0 the RecordCount method must be set to -1. This prevents the loop from giving a copy of the last record.

    For index = 0 To ADOQuery1.RecordCount -1

    Note: The field names (e.g. ADOQuery1headline1) are auto-generated names. All database fields, selected from the Fields Editor, are added as template components.

  14. Disconnect the database connection and deactivate the query component.

    ADOConnection1.Connected = False
    ADOQuery1.Active = False

    The ADO Query component can establish a connection directly using its ConnectionString property, but it is often desirable to use an ADO Connection to share a single connection among several ADO components. This can reduce resource consumption, and allows for transactions to be created that span multiple data sets.

ADO Connection Component

The ADO Connection component connects to an ADO data store. It encapsulates the ADO connection object. The connection provided by a single ADO Connection component can be shared by multiple ADO command and data set components through their Connection properties. This can reduce resource usage, and allows for transactions to be created that span multiple data sets.

ADO Connection allows for control of the attributes and conditions of a connection to a data store. Use the ADO Connection properties to control attributes such as record locking scheme (optimistic versus pessimistic), cursor type, cursor location, isolation level, and connection time-out.

Notable Properties

  • Connected: Specifies whether the connection is active.

  • ConnectionString: Specifies the connection information for the data store.

  • DefaultDatabase: Indicates the database the ADO connection uses by default.

  • LoginPrompt: Specifies whether a login dialog appears immediately before opening a new connection.

  • Provider: Specifies the provider for the ADO connection. Entering a valid ConnectionString usually generates this input.

ADO Query Component

ADO Query provides the means for issuing SQL against an ADO data store. As ADO Query is a query-type data set it can encapsulate an SQL statement, enabling applications to access the resulting records.

Notable Properties

  • Active: When checked sets the query component active. If the SQL query fails it will not be set to active.

  • Connection: Use Connection to specify an ADO connection component to connect to a data store.

  • SQL: SQL query parameter.

ADO Command Component

ADO Command represents an ADO command object. Use ADO Command for issuing commands against a data store accessed through an ADO provider.

The ADO Command component executes the command specified in its CommandText property. One command may be executed at a time. Parameters, if the command includes them, are specified in its Parameters property. The command is executed by a call to the Execute method.

ADO Command can either use a ADO Connection object to connect to a data store (through its Connection property) or connect directly to the data store if the connection information is specified in the ConnectionString property.

ADO Command is most often used for executing data definition language (DDL) SQL commands, or to execute a stored procedure that does not return a result set. For SQL statements that return a result set, ADO DataSet, or ADO Query is better suited. The Execute method of ADO Command is, however, capable of returning a record set. To use that record set, however, you will need a separate ADO data set component.

ADO Table Component

ADO Table is a table-type data set that represents all of the rows and columns of a single database table. The component encapsulates a table accessed through an ADO data store. It can access data in a single database table using ADO. It provides direct access to every record and field in an underlying database table, but can also work with a subset of records within a database table using ranges and filters.

Notable Properties

  • Active: When selected, the component is active.

  • Connection: Specifies the ADO connection component to use.

  • Name: Specifies the name of the control as referenced in code.

  • ReadOnly: When checked the database connection is set to be read only.

  • RecordCount: Indicates the total number of records in the record set.

  • Recordset: Provides direct access to the ADO record set object.

ADO DataSet Component

ADO DataSet is a generic ADO data set control, and can be used in place of ADO Query. It enables data representation from one or more tables in a database and allows data-aware components to manipulate data by connecting with a DataSource (non-Unicode) component. The data source component is the link between the visual components displaying the data and the database.

Unlike the other ADO data set components, ADO DataSet is not a table-type, query-type, or stored procedure-type data set. Instead, it can function as any of these types.

As a table-type data set (ADO Table), ADO DataSet is able to represent rows and columns of a single database table. To use it like this, set the CommandType property to cmdTable and the CommandText property to the name of the table.

As a query-type data set (ADO Query), ADO DataSet can specify a single SQL command that is executed when a data set is opened. To use it like this, set the CommandType property to cmdText and the CommandText property to the SQL command to be executed.

Notable Properties

  • Active: When checked it sets the query component active. If the SQL query fails, it is not possible to set it active.

  • Connection: Use Connection to specify an ADO Connection object to use to connect to a data store.

  • ConnectionString: Set ConnectionString to specify the information needed to connect the ADO Connection component to the data store.

  • DataSource: Links the edit control to the data set that contains the field it represents.