FTVIEW SE client display the value from MSSQL server using VBA Code

Hello Every one,

I need a help on vba code for getting data from MSSQL server 2014 to display in FTview SE client version 12 . Thanks.

Please support on this.

I have tried the below code:

Sub ConnectSqlServer()

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String

' Create the connection string.
sConnString = "Provider=SQLOLEDB;Data Source=DESKTOP-HSKPA00;" & _
"Initial Catalog=MondelezDB;" & _
"Integrated Security=true;"

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

' Open the connection and execute.
conn.Open sConnString
Set rs = conn.Execute("Select AVG(val) from dbo.FloatTable where TagIndex=5;")

' Close the recordset
rs.Close
conn.Close
Set conn = Nothing
Set rs = Nothing

End Sub

in sConnString you may need to add the user and password for the database.
Here is one I use:
"Provider=SQLOLEDB;Data Source=192.168.1.250;Initial Catalog=CIPLog;UID=plant;PWD=plant;"

Thnak you. MSSQL Connection has been established

Private Sub Update_Trend_Press()

Dim ConnStatus As Boolean
Dim strStatement As String
Dim AVGTestTag As Tag


On Error GoTo ErrHandler



' SQL Script For Data Average
strStatement = "Select AVG(val) as AVGTestTag from dbo.FloatTable where TagIndex=5;"


Call SQL_Connection(strStatement, ConnStatus)

Set AVGTestTag = TagDB.GetTag("TestTag")

TrendPro1.Refresh



End
Exit Sub
ErrHandler:
MsgBox "VBA Error: Display (" & Me.Name & " - Sub Update_Trend_Pressed) - " & Hex(Err.Number) & " - " & Err.Description, ftDiagSeverityError
Application.LogDiagnosticsMessage "VBA Error: Display (" & Me.Name & " - Sub Update_Trend_Pressed) - " & Hex(Err.Number) & " - " & Err.Description, ftDiagSeverityError

End Sub

' SQL Connection Details
Private Function SQL_Connection(Query As String, ConnStatus As Boolean)
Const strConnection As String = "Provider=SQLOLEDB;" _
& "Server=DESKTOP-HSKPA00;" _
& "Database=MondelezDB;" _
& "Uid=ENPLLC;" _
& "Pwd=ENPllc123;"

Dim objCon As New ADODB.Connection
Dim objCom As New ADODB.Command
Dim SQL_Conn_Alarm As Tag
Dim objrs As New ADODB.Recordset
Dim strStatement As String


'Indentify the database being used.
objCon.ConnectionString = strConnection

'Open the connection.
objCon.Open objCon.ConnectionString

'Execute the statement.
If objCon.State Then

strStatement = Query

With objCom
.CommandText = strStatement
.CommandType = adCmdText
.ActiveConnection = objCon
.Execute
End With

End If

ConnStatus = objCon.State
'Close the connection.
objCon.Close
Application.LogDiagnosticsMessage ("Database connection closed at " & "" & Now & "")

'clean up
Set objCom = Nothing
Set objCon = Nothing

'End
Exit Function

End Function

How to move the SQL average value(AVGTestTag) to HMI tag(TestTag)
Any Idea? Please share your inputs.

Thanks.