Step 5 - The VBA Code, Continued.
Create a handler for the "Display_Load" event, and do all initialization. I'm showing mine, with diagnostics and such included...
'*******************************************************************
' Sub: Display_Load()
' Called when display is first loaded, initializes overall
' VBA Code, and sets up all tag groups and database commands.
'*******************************************************************
Private Sub Display_Load()
On Error GoTo ErrHandler
'--------------------------------------------------------------
' Globals which MUST be initialized:
'
Line_Num = 12345
DBSource = "MyODBC"
IOTopic = "MyIoTopic"
LogDiagnosticsMessage "*****INITIALIZING LOGGING SUBSYSTEM
LogDiagnosticsMessage "***** Logging SubSystem Version 7.3d"
If ComputerName() = "LoggingPC" Then
EnableReportAndLogging = True
LogDiagnosticsMessage "My Host Name is: " + ComputerName() + " And Logging is True"
Else: EnableReportAndLogging = False
LogDiagnosticsMessage "My Host Name is: " + ComputerName() + " And Logging is False"
End If
If EnableReportAndLogging = True Then
'----------------------------------------------------------
' Add all Tag's to monitor to the appropriate IO tag group:
'
Add_SampleGroup
'----------------------------------------------------------
' Open the database, and build up the commands to handle
' the interaction to the SQL Server stored procedures:
'
Open_Database
Add_Sample_Cmd ' Add Stored Procedure call for new Indexed Sample Command
End If
Exit Sub
ErrHandler:
LogDiagnosticsMessage Err.Number & " " & Err.Description & " Sub: Display_Load() ", ftDiagSeverityInfo 'Log error to the activity log
End Sub
Here is the function to get the computer name:
'*******************************************************************
' Function: ComputerName()
' Returns the network name of this computer as a string.
'*******************************************************************
Public Function ComputerName() As String
Dim sBuffer As String
Dim CName As String
Dim UpperCase As String
Dim lAns As Long
sBuffer = Space$(255)
lAns = GetComputerName(sBuffer, 255)
If lAns <> 0 Then
'read from beginning of string to null-terminator
CName = Left$(sBuffer, InStr(sBuffer, Chr(0)) - 1)
UpperCase = UCase(CName)
ComputerName = UpperCase
Else
Err.Raise Err.LastDllError, , _
"A system call returned an error code of " _
& Err.LastDllError
End If
End Function
Notice that I'm calling other functions to build up the tag groups, here there is only one, but you can have many. Let's build up the "SampleGroup". Here, we create a group of tags in the VBA app, that can interact with the PLC. This sample is based on the Control/Compact Logix platform, has 4 data tags, and the trigger tag (which isn't actually needed).
'*******************************************************************
' Sub: Add_CVSampGroup()
' Called when display is first loaded, to build up the global group
' of run sample Tracking Tags.
'*******************************************************************
Public Sub Add_CVSampGroup()
On Error GoTo ErrHandler
If EnableReportAndLogging = False Then
Exit Sub
End If
txt_DBStatus.Caption = "Adding Sample Group"
If SampleGroup Is Nothing Then
Set SampleGroup = Application.CreateTagGroup(Me.AreaName, 500)
If Err.Number Then
LogDiagnosticsMessage "Unable to create SampleGroup", ftDiagSeverityError
Exit Sub
End If
'-----------------------------------------------------------------------
' For all groups, the topic is built up of the global IOTopic value.
' This is to make it easy to update this VBA code for different
' machines and equipment. The actual tags still need to be hard-coded
'
SampleGroup.Add "{[" & IOTopic & "]CMD_Sample}"
SampleGroup.Add "{[" & IOTopic & "]DataTag1}"
SampleGroup.Add "{[" & IOTopic & "]DataTag2}"
SampleGroup.Add "{[" & IOTopic & "]DataTag3}"
SampleGroup.Add "{[" & IOTopic & "]DataTag4}"
SampleGroup.Active = True
End If
Exit Sub
ErrHandler:
LogDiagnosticsMessage Err.Number & " " & Err.Description & " Sub: Add_SampleGroup() ", ftDiagSeverityInfo
End Sub
Okay, so we've added the group of tags we want to log, now we should open the database and perform some actions. Again, this is a SIMPLE example. Here, we are just going to open a database connection, and leave it open forever. In practice, it is usually wiser to only open the connection when needed, use it, and close it again. Here is the database connection code:
'*******************************************************************
' Sub: Open_Database()
' Called when display is first loaded, and opens the global connection
' to the SQL Database
' This database reference should be set up as a SYSTEM DSN using
' 'Data Sources' under 'Administrative tools' in the Windows
' control panel. Authentication should be 'Windows Authentication'
' which requires that the users also be given rights to access
' the database, or all logging calls will fail.
'*******************************************************************
Private Sub Open_Database()
On Error GoTo ErrHandler
Dim DSNName As String
DSNName = "DSN=" & DBSource
txt_DBStatus.Caption = "Opening Database"
If oConn Is Nothing Then
Set oConn = New ADODB.Connection
End If
oConn.CursorLocation = adUseClient
oConn.Mode = adModeReadWrite
oConn.Open DSNName
DBState = oConn.State
txt_DBState.Caption = DBState
LogDiagnosticsMessage "Database " & DBSource & " Has Been Opened"
Exit Sub
ErrHandler:
LogDiagnosticsMessage Err.Number & " " & Err.Description & " Sub: Open_Database() ", ftDiagSeverityInfo
End Sub
Here is the code to attach a stored procedure for adding a sample (based on our sample groups) to the database connection above. 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
Well, that takes care of the basic preparation. Next post, we'll get into actually triggering writes to the database based on PLC events.
--- Continued once more ---