Knowledge base

ADO

ADO = ActiveX Data Objects. 

This section only explains how to interface the Connection Client ActiveX, from a programmer's perspective. Mainly, by calling a function in this client you will have to pass a ADO recordset to this function, containing the data to be transferred. If you do not understand what ADO is, and how to create recordsets with it, we recommend you to read the MSDN pages of Microsoft. 

We explain the mechanism with the next VB program:

Private Sub Mybutton_Click()
    'define the object that point to the object class module of PRODBX
    Dim PRODBX As ADORecordset
    'define the recordset to use to pass data to the PRODBX function
    Dim myADO As New ADODB.Recordset
    'define the connection to use to retrieve the data for the ADO recordset
    Dim myDBConnection As New ADODB.Connection
    'define other variables
    Dim ConnectString As String
    Dim SqlString As String

    'Build the connection string
    ConnectString = "Provider=MSDASQL.1;Password=password"
    ConnectString = ConnectString & ";Persist Security Info=True;User ID=demomaster"
    ConnectString = ConnectString & ";Data Source=DemoMaster"

    'Open the connection
    myDBConnection.ConnectionString = ConnectString
    myDBConnection.Open

    'Build the SQL to retrieve data
    SqlString = "select MATERIAL_ID,MATERIAL_DESC from ITEM_MASTER"

    'Retrieve the data and store it in the ADO
    myADO.Open SqlString, myDBConnection, adOpenDynamic, adLockReadOnly

    'Apply filter (if necessary)
    myADO.Filter = "MATERIAL_ID like '8%'"

    'Create the object to interface PRODBX
    Set PRODBX = CreateObject("Connection_Client_ActiveX.ADORecordset")

    'Call the function
    If Not PRODBX.InputADORecordset(myADO, "MYTESTTABLE", 2) Then
        MsgBox "Error executing PRODBX function"
    End If

End Sub

This routine was written under a button. It makes an ODBC connection with DSN=DemoMaster, and logs on the database with user demomaster who's password is simply password.

In this database there is a table ITEM_MASTER of which we want to pass 2 columns in the ADO: MATERIAL_ID and MATERIAL_DESC.

We filter the recordset to show that only the filtered recordset is interpreted by PRODBX.

Create the ADORecordset object. You may also create this on a different machine like:

    Set PRODBX = CreateObject("Connection_Client_ActiveX.ADORecordset","PROMESSERVER")

Make sure the PRODBX connection client ActiveX is running, before running the code. If you don't, you will get errors because the module is missing specific command line parameters.

If you have problems creating the object, or finding the 'ADORecordset' does not appear as a class in your VB, then check if you did:

• Installing the connection client ActiveX on the machine you want to run this code. Even if you want to run the object on a different machine.
• Add the 'PRODBX connection client ADO ActiveX' in the references section of your VB project

 
When calling the PRODBX function you pass the ADO recordset. You do not only pass the data with this, but also the field names and their corresponding field type. Remember this when you create ADO recordsets manually (without a database). In some cases ADO is not able to hold the table name. Therefore you have this additional parameter in the function call (mandatory field). Fill in the name you used in PRODBX Configuration manager. (So it can be different then the real table name you retrieved the data from).

The last parameter to give the function is the instruction type:

• 1 = INSERT
• 2 = UPDATE
• 3 = DELETE
• 4 = COMMAND

If you mixed up INSERT or UPDATE, PRODBX will automatically correct.

COMMAND type will switch the destination connection client to 'Polling time fast', which is bringing it in a mode it waits for an answer.

The function returns True when it was able to handle the ADO. This is not equal to 'successfully transferred the data'. When the function returns False it also logged an error #5087. Use the Logging manager to view the reason of your error.

Performance note

When passing your ADO to the connection client you will encounter that the function is not released unless all records are interpreted. This is because the ownership of the ADO object remains in your application. If you want to prevent this, you must use RDS (Remote Data Service). This will allow you to pass the object completely to the connection client. However, make sure you do not call the function any more before your previous recordset is interpreted. If you do, your previous recordset is overwritten, and you loose the not interpreted data.

Learn more about RDS in the MSDN of Microsoft.