Hi Francois,

I have no example but:

Do you want archiving those data in a PcVue table?
Is it a workaround of the sampling data feature missing in Pcvue?
How many data do you want to write in 1 shot?

The same I have nothing in VBA to archive variables in SQL table. PcVue is able to archive in SQL tables as Nico said 🙂

I have something to read PcVue archived values to affect real tim variables.

Bellow is the part to read the data from the Database:
There is a loop to get 25 times 4 fields, so 100 informations.

	'******************************************************************************************************
	' Affectation des variables HIS en fonction d'une horodate 
	' Le parametre Horodate sera fourni par le timer
	' La reference Microsoft ActiveX DataObjects a ete ajoute pour manipuler des objets ADODB
	'******************************************************************************************************
	Public Sub AffectHis(Horodate As String)
	Dim cn As New ADODB.Connection          'L'objet Connexion … la base
	Dim cmd As ADODB.Command                'L'objet Command permettant de r‚aliser des requˆtes
	Dim rs As New ADODB.Recordset           'L'objet Recordset permettant de voir et manipuler les r‚sultats d'une requˆte
	Dim BrancheAff As String
	Dim BrancheHis As String
	Dim IndexTroncon As Integer
	Dim RequeteSelect As String
	
	    On Error GoTo AffectHisError
	    
	    BrancheAff = "BOUCHONS.AFF.TRONCON"     'Branche des variables archiv‚es
	    BrancheHis = "BOUCHONS.HIS.TRONCON"     'Branche des variables utilis‚es pour l'affichage des historiques bouchons
	    
	    cn.Provider = "SQLOLEDB"    'On specifie le provider pour acceder … une base SQL Server
	    '--- choisir le serveur actif ---
	    If [SYSTEM.ASSOCIATION1.SRV1] = True Then
	        cn.Open "Data Source=SRV1SVSQLEXPRESS;Initial Catalog=ANA", "sa", "Pcvue2011"  'On ouvre la connexion en pointant sur la base ANA avec le login sa
	    Else
	        cn.Open "Data Source=SRV2SVSQLEXPRESS;Initial Catalog=ANA", "sa", "Pcvue2011"  'On ouvre la connexion en pointant sur la base ANA avec le login sa
	    End If
	    If cn.State = adStateOpen Then      'On teste si la connexion a r‚ussi
	        Debug.Print Now & " Connexion ouverte."
	    Else
	        Debug.Print Now & " Erreur de connexion"
	        Set cn = Nothing    'On libŠre l'objet
	        Set rs = Nothing
	        Exit Sub            'Et on sort de la proc‚dure
	    End If
	    
	    Set cmd = New ADODB.Command     'On creer un objet Command
	    cmd.ActiveConnection = cn       'On initialise la propri‚t‚ ActiveConnexion grƒce … l'objet Connexion
	    
	    For IndexTroncon = 1 To 25      'Pour chaque tron‡on
	'        'On bati la requˆte SQL pour r‚cup‚rer le volume et le nom du lot … l'horodate pass‚e en argument
	'        RequeteSelect = "SELECT TOP 1 [Value],[TextAttr03] FROM [ANA].[dbo].[BOUCHONS] where [Name] = '" & BrancheAff & IndexTroncon
	'        RequeteSelect = RequeteSelect & ".VOLUME' and [Chrono] < (CAST(datediff(second, convert(datetime, '1970-01-01 00:00:00',121), convert(datetime, '"
	'        RequeteSelect = RequeteSelect & Format(mdChronoConvertion.LocalTimeToUTCTime(CDate(Horodate)), "YYYY-MM-DD hh:mm:ss")
	'        RequeteSelect = RequeteSelect & "',121)) as bigint)* 10000000)+ 116444736000000000 and ([Quality] = 216 or [Quality] =192) order by [Chrono] desc"
	'        cmd.CommandText = RequeteSelect     'On affecte la commande texte
	'        cmd.CommandType = adCmdText         'Et on sp‚cifie qu'il s'agit d'une requˆte SQL
	'        Set rs = cmd.Execute()              'On creer un RecordSet en executant la commande
	'        Variables(BrancheHis & IndexTroncon & ".VOLUME") = rs(0)                                'rs(0) contient la premiŠre colonne (Value)
	'        Variables(BrancheHis & IndexTroncon & ".VOLUME").ExProperties(3).Value = CStr(rs(1))    'rs(1) contient la deuxiŠme colonne (TextAttr03)
	        
	        'On batit la requˆte SQL pour r‚cup‚rer le volume … l'horodate pass‚e en argument
	        RequeteSelect = "SELECT TOP 1 [Value] FROM [ANA].[dbo].[BOUCHONS] where [Name] = '" & BrancheAff & IndexTroncon
	        RequeteSelect = RequeteSelect & ".VOLUME' and [Chrono] < (CAST(datediff(second, convert(datetime, '1970-01-01 00:00:00',121), convert(datetime, '"
	        RequeteSelect = RequeteSelect & Format(mdChronoConvertion.LocalTimeToUTCTime(CDate(Horodate)), "YYYY-MM-DD hh:mm:ss")
	        RequeteSelect = RequeteSelect & "',121)) as bigint)* 10000000)+ 116444736000000000 and ([Quality] = 216 or [Quality] =192) order by [Chrono] desc"
	        cmd.CommandText = RequeteSelect     'On affecte la commande texte
	        cmd.CommandType = adCmdText         'Et on sp‚cifie qu'il s'agit d'une requˆte SQL
	        Set rs = cmd.Execute()              'On cree un RecordSet en executant la commande
	        Variables(BrancheHis & IndexTroncon & ".VOLUME") = rs(0)                               'rs(0) contient la premiŠre colonne (Value)
	        
	        'On batit la requˆte SQL pour r‚cup‚rer le produit … l'horodate pass‚e en argument
	        RequeteSelect = "SELECT TOP 1 [Value] FROM [ANA].[dbo].[BOUCHONS] where [Name] = '" & BrancheAff & IndexTroncon
	        RequeteSelect = RequeteSelect & ".PRODUIT' and [Chrono] < (CAST(datediff(second, convert(datetime, '1970-01-01 00:00:00',121), convert(datetime, '"
	        RequeteSelect = RequeteSelect & Format(mdChronoConvertion.LocalTimeToUTCTime(CDate(Horodate)), "YYYY-MM-DD hh:mm:ss")
	        RequeteSelect = RequeteSelect & "',121)) as bigint)* 10000000)+ 116444736000000000 and ([Quality] = 216 or [Quality] =192) order by [Chrono] desc"
	        cmd.CommandText = RequeteSelect     'On affecte la commande texte
	        cmd.CommandType = adCmdText         'Et on sp‚cifie qu'il s'agit d'une requˆte SQL
	        Set rs = cmd.Execute()              'On cree un RecordSet en executant la commande
	        Variables(BrancheHis & IndexTroncon & ".PRODUIT") = rs(0)                               'rs(0) contient la premiŠre colonne (Value)
	        
	        'On batit la requˆte SQL pour r‚cup‚rer le nom du lot … l'horodate pass‚e en argument
	        RequeteSelect = "SELECT TOP 1 [Value],[TextAttr03] FROM [ANA].[dbo].[BOUCHONS] where [Name] = '" & BrancheAff & IndexTroncon
	        RequeteSelect = RequeteSelect & ".LOT' and [Chrono] < (CAST(datediff(second, convert(datetime, '1970-01-01 00:00:00',121), convert(datetime, '"
	        RequeteSelect = RequeteSelect & Format(mdChronoConvertion.LocalTimeToUTCTime(CDate(Horodate)), "YYYY-MM-DD hh:mm:ss")
	        RequeteSelect = RequeteSelect & "',121)) as bigint)* 10000000)+ 116444736000000000 and ([Quality] = 216 or [Quality] =192) order by [Chrono] desc"
	        cmd.CommandText = RequeteSelect     'On affecte la commande texte
	        cmd.CommandType = adCmdText         'Et on sp‚cifie qu'il s'agit d'une requˆte SQL
	        Set rs = cmd.Execute()              'On cree un RecordSet en executant la commande
	        Variables(BrancheHis & IndexTroncon & ".LOT") = rs(0): DoEvents                                'rs(0) contient la premiŠre colonne (Value)
	        Variables(BrancheHis & IndexTroncon & ".LOT").ExProperties(3).Value = CStr(rs(1)): DoEvents    'rs(1) contient la deuxiŠme colonne (TextAttr03)
	        
	    Next IndexTroncon
	    
	    rs.Close            'Fermeture du Recordset
	    Set rs = Nothing    'On libŠre l'objet
	    cn.Close            'Fermeture de la connexion
	    Set cn = Nothing    'On libere l'objet
	    Set cmd = Nothing
	    '--- M‚morise dans l'attribut Texte 4 l'horodate de l'enregistrement r‚cup‚r‚ ---
	    '--- Permet de dater l'‚tat affich‚ sur le pipeline ---
	    Variables("BOUCHONS.HIS.TRONCON1.VOLUME").ExProperties(4).Value = Format(Horodate, "DD/MM/YYYY hh:mm:ss"): DoEvents
	    On Error GoTo 0
	    Exit Sub
	
	AffectHisError:     'En cas d'erreur
	    Dim Errs As ADODB.Errors
	    Dim errLoop As Error
	    Dim strError As String
	    MOD_General.ErrorManagement "AffectHis", "Bouchons", Err.Description
	    On Error Resume Next
	    Set Errs = cn.Errors
	    'Parcours tous les objets Error de la collection Errors et affiche les propri‚t‚s
	    For Each errLoop In Errs
	        Debug.Print ""
	        Debug.Print errLoop.SQLState
	        Debug.Print errLoop.NativeError
	        Debug.Print errLoop.Description
	        Debug.Print ""
	    Next
	
	    If rs.State = 1 Then
	        rs.Close        'Fermeture du Recordset
	    End If
	    Set rs = Nothing    'On libŠre l'objet
	    If cn.State = 1 Then
	        cn.Close        'Fermeture de la connexion
	    End If
	    Set cn = Nothing    'On libere l'objet
	    Set cmd = Nothing
	End Sub
	'*********************************************************************************************************

Hi Nico
Yes it's a workaround of the sampling missing feature...
It's for a demo so I would say 10 data is enough
It would use the PcVue tables
Francois

Francois, we have a basic Manager Toolkit code doing the sampling. This code has been done by Raphael few years ago. I think it's better you use it.

I will send it to you today.

Hello,

For the sampling you can also see the KB article 593, I made a demo project from the file you sent me Nicolas K :

https://support.pcvuescada.com/index.php?option=com_k2&view=item&id=593:sampling-project&Itemid=83

Ho yes, I just sent the same thing to Francois. I totally forgot that we did this KB post :whistle:

Thank you guys, it's interesting even if I'm not sure to use it
THe customer would need to archive the data to SQLServer in the way it's done in factory link : 1 columns for the timestamp and then several columns for variable
They also need a grid or an activex to access the data of the database to read/write
Also they need a trendviewer to be able to display the data...

Ho yes, we have exactly the same issue with our old FL customers....

My solution is: we must do an application using Manager Toolkit to store data in FL format.