Using an ADO Query

The following code sample uses a query combining Historian data with ADO data. In the example, you convert a collected value, number of barrels per day (BarrelsUsedToday), to a dollar amount. The code then obtains the price per barrel (CostOfBarrel) from the SQL server, and finally stores the total dollars in an integer tag (TotalCostToday).

You can also do this with a linked server and the Historian OLE DB provider, but this example maintains a history of the results.

Dim CostOfBarrel, BarrelsUsedToday, TotalCostToday
'Calculate the total number of barrels used over
'the previous 24hours.
BarrelsUsedToday = Calculation("BarrelsUsedTag","Total","Now 1Day","Now",Quality)
'Retrieve cost per barrel used
Dim SQLExpression
Dim Cnxn
Dim rsCurrentValue
SQLExpression = "SELECT Barrel_Cost AS Value1 FROM RawMaterial_Costs WHERE Barrel_Type = CrudeOil and
samplingmode = CurrentValue"
'open connection
Set Cnxn = CreateObject("ADODB.Connection")
'connect to default server using current username and password
'establish connection
Cnxn.Open "Provider=SQLOLEDB.1;User ID=sa; Password=;Initial Catalog=Northwind"
'Create and open first Recordset using Connection execute
Set rsCurrentValue = CreateObject("ADODB.Recordset")
'Get the value from the other server
Set rsCurrentValue= Cnxn.Execute(SQLExpression)
'Set the result to the current value of other tag
CostOfBarrel = rsCurrentValue("Value1")
'Clean up
If rsCurrentValue.State = adStateOpen then 
rsCurrentValue.Close
End If
If Cnxn.State = adStateOpen then
Cnxn.Close
End If
Set rsCurrentValue = Nothing
Set Cnxn = Nothing
'Retrieve number of barrels used
BarrelsUsedToday = Calculation("BarrelsUsed","Count","Now 1Day","Now",Quality)
'Calculate total cost of barrels today
TotalCostToday = CostOfBarrel * BarrelsUsedToday