Once a new UserConnection object has been inserted into the project, you can add new Query objects to it. These query objects can be either stored procedure calls or user-defined SQL statements. Each query object defined at design-time will automatically be available as a method of the new UserConnection class.
To add a Query object, click the leftmost button on the toolbar. The new Query object is added hierarchically below the UserConnection object, and its Properties Window appears.
Select Insert Stored Procedure to view the available stored procedures on the database. The UserConnection designer attempts to establish a connection to the database, using the connection properties you entered. If successful, it enumerates the available stored procedures on the database, allowing you to select one to define the Query object:
Query Properties dialog box
Choose Based on User-Defined SQL to create a local query against the remote database. This enables the SQL property box, into which you enter SQL statements to define your query.
Alternatively, you can click the Build button, which opens the MS-Query query design tool. MS-Query provides a visual interface that allows you to drag and drop database tables and fields, define conditions and set relationships to define the query. When you exit from MS-Query, your visually-designed query is automatically translated into SQL code and inserted into the User-Defined SQL property box.
For More Information For more information about using the MS-Query query builder, see MSQry.hlp which is installed in the \Program Files\Common Files\Microsoft Shared\vba directory.
In addition to the defining properties discussed above, the Query Property Page has two additional tabs for defining Parameters and advanced properties.
The Parameters tab on the query property page allows the developer to adjust properties for each parameter in the current query.
Parameters properties
The parameters shown in the Parameters list box are determined automatically from the query source, and cannot be changed here. Parameters are generated for each parameter marker in the SQL statement for the query (or call statement for stored procedures). Parameter markers are designated by a "?" in accordance with the ODBC specification. For example, the following query would contain two parameters:
SELECT * FROM authors WHERE state = ? AND zip = ?
The parameter properties that can be changed here are the Name, Direction, ODBC Binding Data Type, and Visual Basic Data Type.
The Name property can be changed to allow your Visual Basic code to recognize the parameter by a familiar sounding name, if its actual name is complex or not intuitive.
Setting the Direction of a parameter may be necessary for some ODBC databases for which the driver is not capable of determining the direction (input, input/output, or return values). The same is true for the data types, and you may wish to override the default type conversion into Visual Basic data types for use with your code.
The Advanced tab of the Query Properties page allows for fine-tuning the query by setting limits and thresholds.
Advanced properties
The Call Syntax text box lets you change the call syntax that RDO will use to call the stored procedure. This can be edited to adjust the number of parameters and presence of the return value. Editing the call syntax is an advanced operation and should only be done by developers who understand the ramifications.
You can insert the UserConnection object into your Visual Basic code just as you would with any class, by creating an object variable and instantiating it with a new instance of the UserConnection class you have defined. For example:
Option Explicit
Private MyConnect As MyConnection
Set MyConnect = New MyConnection
You can write your own code "behind" the inserted UserConnection object to sink events raised from the connection or any of the queries defined under it. You can also implement your own methods, or write Property Get and Property Let procedures to implement your own properties, which are added to the type library for the new object.