FactoryTalk SE Datalogging and filtering for Display. VBA/SQL perhaps?

You can also just the built in DATALOG files and function within ViewSE - and set that to log to an ODBC source. You can have it run on a condition, timer, or whatever you want. This method would bypass all of the VBA (although very nice of the user to post all of that for you, and that is definitely an option).

Really though, instead of doing VBA logging or DATALOGs you might want to look at getting them a small Historian and putting things an a real Historian. It also comes with 1 named user for VantagePoint, so you would have reporting capabilities as well.
 
I think you might have a problem using ME for this. ME doesn't support VBA in any way. Is this ME running on a PV+ or a PC running ME Station? There is a way I believe to get the FT Diagnostics to a DB, but I'm not positive. But this won't give you much flexibility on what is recorded.

I'll have to ask my client to answer that question. Before I do that, though, do you mean that ME doesn't support the method used in the example detailed above, or that ME might not support me doing what I want to do here at all?
 
You can't do any VBA with ME, so if you are trying to log items to a database, either get a historian and do it the "right" way, or use something like an OLDI module to send values from a PLC to a SQL DB - you could run sprocs or whatever you need and get those tags back into PLC registers and then have tags on your HMI point to the proper values.

ME is very limiting.
 
You can't do any VBA with ME, so if you are trying to log items to a database, either get a historian and do it the "right" way, or use something like an OLDI module to send values from a PLC to a SQL DB - you could run sprocs or whatever you need and get those tags back into PLC registers and then have tags on your HMI point to the proper values.

ME is very limiting.
Thanks, xC0MMAND0x. Can you elaborate on "historian?"
 
Historian is essentially a way to log values of tags over long periods of time. Rockwell has Historian ME (machine edition) which is a physical device that plugs into a ControlLogix chassis.

They also have Historian SE (Software Edition) which is software based. You would need a server set up to install the Historian, and another to run Vantagepoint (reporting). You can install both on the same server, but it's not recommended unless you have specific considerations (there is a Rockwell KB article on this).

Basically you set up your Historian, license it for X # of tags, choose the tags you want historized, and then their values are constantly logged (and highly compressed) in a proper time series database.

You can you VantagePoint to report based off of the Historian data, or use the built in trend client in FactoryTalk View SE to visualize historical data (and not just live values).

http://www.rockwellautomation.com/rockwellsoftware/products/factorytalk-historian.page

Feel free to PM me if you want more info or if you are looking for contract type work - we set up and work with Historians all the time and a large number of sites and have a team mainly dedicated to writing custom reports.
 
Hi rdrast!
we have some stored procedures and its correctly logs.
But we tried return some info from sql server, and its doesnt work.
Code:
USE [Praxair_trends]
GO
/****** Object:  StoredProcedure [dbo].[Add_Indexed_Sample]    Script Date: 05/12/2016 13:37:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Add_Indexed_Sample] 
	-- Add the parameters for the stored procedure here
	@timestamp datetime,
	@data1 varchar(20),
	@data2 varchar(20),
	@data3 varchar(20),
	@data4 varchar(20),
	@Index_C int out 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	INSERT IGNORE INTO Tests values(@timestamp, @data1, @data2, @data3, @data4)
	Set @Index_C = (SELECT  @@ROWCOUNT from Tests);
	RETURN  
END

and i dont know how i should accept it in VBA.
can you help as...
 
HI, If you have any sample code to do data logging for FT view SE tags in to SQL data base tables and to read data's from the same table to display? ( for FT view SE ) Please send the same to my email id: [email protected]

Thanks & regards,
Chaitra hebbar
 
Note that this sub here assumes that you have a stored procedure on the database that takes 4 values, inserts them into some table, and returns the row-index of the inserted row
'*******************************************************************
' Add_Sample_Cmd
' Called when display is first loaded, and creates the interface
' to the database stored procedure to add Reel Report Records, then
' attaches it to the global database connection.
'*******************************************************************

Public Sub Add_Sample_Cmd()

Dim pPar As ADODB.Parameter

If pPar Is Nothing Then
Set pPar = New ADODB.Parameter
End If

On Error GoTo ErrHandler

If EnableReportAndLogging = False Then
Exit Sub
End If

If oCmdAddSample Is Nothing Then
Set oCmdAddSample = New ADODB.Command
End If

oCmdAddSample.CommandText = "sp_Add_Sample"
oCmdAddSample.CommandType = adCmdStoredProc
oCmdAddSample.Name = "Add_Sample"


Set pPar = oCmdAddSample.CreateParameter("RV", adInteger, adParamReturnValue)
oCmdAddSample.Parameters.Append pPar

Set pPar = oCmdAddSample.CreateParameter("DBDataTag1", adInteger, adParamInput)
oCmdAddSample.Parameters.Append pPar

Set pPar = oCmdAddSample.CreateParameter("DBDataTag2", adVarChar, adParamInput, 20)
oCmdAddSample.Parameters.Append pPar

Set pPar = oCmdAddSample.CreateParameter("DBDataTag3", adVarChar, adParamInput, 20)
oCmdAddSample.Parameters.Append pPar

Set pPar = oCmdAddSample.CreateParameter("DBDataTag4", adVarChar, adParamInput, 20)
oCmdAddSample.Parameters.Append pPar

Set pPar = oCmdAddSample.CreateParameter("Identity", adVarChar, adParamOutput, 20)
oCmdAddSample.Parameters.Append pPar

Set oCmdAddSample.ActiveConnection = oConn

Exit Sub
ErrHandler:
LogDiagnosticsMessage Err.Number & " " & Err.Description & " Sub: Add_Sample_Cmd() ", ftDiagSeverityInfo


End Sub[/ladder]

How do I create this "sp_Add_Sample" stored procedure?
I'm almost there...any help would be pretty much appreciated.
 
Hello Guys, i want to share my test code about writing and reading values from/to SQL Database from FTV. It very simple, but works.

Code:
Option Explicit
Const sConnString As String = "Provider=SQLOLEDB;Data Source=\SQLEXPRESS;" & "Initial Catalog=TimeLog;" & "Integrated Security=SSPI;"
'Server name = SQLEXPRESS; Database name = TimeLog; Table name = testVBAtable; table has 3 columns: [DateTime], [Value1], [Value2]

Sub WriteToSqlServer()
On Error GoTo errHandler

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim dateandtime
    Dim Value1 As Double
    Dim Value2 As Double
    Dim command As String

    ' Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    ' Open the connection and execute.
    conn.Open sConnString
    
    dateandtime = Now()
    Value1 = 58456.546
    Value2 = 1415.1516
    
    command = "INSERT IGNORE INTO [testVBAtable] VALUES ('" & dateandtime & "', " & Value1 & ", " & Value2 & "); "
    
    Set rs = conn.Execute(command)
                       
    ' Clean up
    If CBool(conn.State And adStateOpen) Then conn.Close
    Set conn = Nothing
    Set rs = Nothing

Exit Sub
errHandler:
    LogDiagnosticsMessage ("WriteToSqlServer error " & Conversion.Hex(Err.Number) & " " & Err.Description)
End Sub


Sub ReadFromSqlServer()
On Error GoTo errHandler

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim dateandtime
    Dim Value1 As Double
    Dim Value2 As Double
    Dim command As String

    ' Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    ' Open the connection and execute.
    conn.Open sConnString
    
    ' Read the data from the last entry to the database
    command = "SELECT TOP (1) [DateTime], [Value1], [Value2]  FROM [testVBAtable] order by [DateTime] desc"
    
    Set rs = conn.Execute(command)
    
    dateandtime = rs("DateTime")
    Value1 = rs("Value1")
    Value2 = rs("Value2")
    
    rs.Close
                   
    ' Clean up
    If CBool(conn.State And adStateOpen) Then conn.Close
    Set conn = Nothing
    Set rs = Nothing

Exit Sub
errHandler:
    LogDiagnosticsMessage ("ReadFromSqlServer error " & Conversion.Hex(Err.Number) & " " & Err.Description)
End Sub
 
Last edited:
Update to my previous post.
If you have problems with ADODB not found, add in the VBA editor under Tools -> References -> Microsoft ActiveX Data Objects 6.1 Library

In the SQL Database, create the database "TimeLog", and create a table called "testVBAtable" with "DateTime" as datetime, "Value1" as real and "Value2" as real.

Sometimes you can have a problem with "IGNORE" then change the command to
Code:
   command = "INSERT INTO [testVBAtable] VALUES ('" & dateandtime & "', " & Value1 & ", " & Value2 & "); "
 
Last edited:
Help - Data Control ADO + DataGrid FactoryTalk

Could you set up Microsoft Data Control ADO with the SQL database and then use DataGrid to display the values? I can set up Adodc with the bank but it does not carry me anything, it seems that his arrows instead of being black showing that it is connected is a half faded color.

Already tried to also make this communication of Adodc1 via script but also it does not connect to the bank.


Could any of you help me for days since I'm trying everything that is solution and not one with success.

Script Grid Inicializaca 2.jpg Script Grid Inicializaca.jpg
 

Similar Topics

Hey guys how would you approach the spreadsheet screen part? I searched and everyone points out that using parameters to display this information...
Replies
0
Views
742
Hi folks, This looks like a long post but a lot of it it is just related/useful tech notes at the bottom. I'm trying to get data logging to work...
Replies
3
Views
3,626
Hello all I have a question, concerning datalogging in FTViewSE 8.0 I have 2 datalog models with 2 differents recording rates (30s for live...
Replies
0
Views
2,717
I have a client that is using an ODBC database to log a number of tags (110) every minute. This data is being displayed on a graph in the Client...
Replies
7
Views
2,597
Thanks for your time. I have added a new pen to a trend which works fine as long as you stay on the trend screen but resets when you navigate...
Replies
15
Views
25,506
Back
Top Bottom