Tag Archives: data logging

Creating a Database in SQL Server Express 2014

Microsoft SQL Server Express 2014 is a free, edition of SQL Server this is ideal for learning and developing desktop, web and small server applications. The database size can be 10 gigabytes in size. SQL Server Express 2014 release includes the full version of SQL Server 2014 Management Studio. We will be using SQL Server 2014 Management Studio (SSMS) to create a database. The SQL Server database will be created so we can connect to it for data acquisition (data logging) from our PLC applications. Continue Reading!

Installing SQL Server Express 2014

Microsoft SQL Server Express 2014 is a free, edition of SQL Server this is ideal for learning and developing desktop, web and small server applications. The database size can be 10 gigabytes in size. SQL Server Express 2014 release includes the full version of SQL Server 2014 Management Studio. This is a method to read, write and maintain our database. (Tools) We will be installing this SQL Server database so we can connect to it for data acquisition (data logging) from our PLC applications.

Installing the software
The download file can be obtained from the website for SQL Express 2014.
https://www.microsoft.com/en-ca/download/details.aspx?id=42299
We will be installing the ExpressAdv 64Bit\SQLEXPRADV_x64_ENU.exe on a windows 7 64 bit machine.
This is the latest software that can be installed on the Windows 7 operating system. Continue Reading!

Modbus TCP Data Logging to Database

Using Visual Basic 2015 we will log three holding registers from the PLC along with time and date into a Microsoft Access Database. We will log every minute into the database with the information that we collect from the PLC via Modbus TCP (Ethernet). All code will be done and shown so you can implement this in your application with different parameters. The information collected in the database can then be distributed or analyzed in the future.ModbusTCP MSAccess 010-min

Visual Basic 2015 will be used with the EasyModbusTCP client/server library for .net. We will communicate to an Automation Direct – Do-More PLC. Using the free simulation software of the PLC Designer Software, we will retrieve three values of the Modbus Holding Registers using Modbus TCP. Once we have this information out of the programmable logic controller it will be placed in a Microsoft Access (2010) Database. This will be done by using a SQL command to insert the data.

Automation Direct has a powerful simulator with their Do-More PLC. The PLC software is available from Automation Direct as a free download.
http://support.automationdirect.com/products/domore.html

ModbusTCP MSAccess 040-min

We will start with the PLC program. The first  three holding registers will be used. That will be MHR1, MHR2 and MHR3 in the Do-More PLC. This will correspond to register numbers 40001, 40002 and 40003. The following is a table with all of the references for Modbus communications to the Do-More.

Coil/Register Numbers Data Addresses Type Do-More PLC Table Name
00001-09999 0000 to 270E Read-Write MC1 to MC1023 Discrete Output Coils
10001-19999 0000 to 270E Read-Only MI1 to MI1023 Discrete Input Contacts
30001-39999 0000 to 270E Read-Only MIR1 to MIR2047 Analog Input Registers
40001-49999 0000 to 270E Read-Write MHR1 to MHR2047 Analog Output Holding Registers

Note: The Do More PLC uses the Modbus area to communicate. This is because having direct access to the digital I/O can be dangerous when connected via Ethernet to the internet. Data must move in and out of this area via the PLC program.

Here is the PLC program that sets the three registers.
ModbusTCP MSAccess 050-min

The next thing to do is set up our Microsoft Access 2010 Database. Our database will be named ACC_Database and it will be located at the following location: “C:\AccLog”. We will have one table called ACC_Log. In the table we will have five fields; LogDate, LogTime, Register1, Register2, Register3.

ModbusTCP MSAccess 090-min
ModbusTCP MSAccess 100-min

Visual Basic 2015 is free and is part of Visual Studio 2015. It can be downloaded at the following location:
https://www.visualstudio.com/en-us/products/visual-studio-community-vs

ModbusTCP MSAccess 020-min

EasyModbusTCP is free software. It will be referenced in our visual basic program.  It can be downloaded from the following location:
https://sourceforge.net/projects/easymodbustcp/
After downloading you must extract the files.

ModbusTCP MSAccess 030-min

EasyModbusTCP is a Modbus TCP, Modbus UDP client/server library for .NET and Client library for JAVA. .NET (.dll) Client/Server also supports Modbus RTU. It supports the following function codes: – Read Coils (FC1)
– Read Discrete Inputs (FC2)
– Read Holding Registers (FC3)
– Read Input Registers (FC4)
– Write Single Coil (FC5)
– Write Single Register (FC6)
– Write Multiple Coils (FC15)
– Write Multiple Registers (FC16)
– Read/Write Multiple Registers (FC23)

Now we will call up visual studio and create our project. The first thing that we must do is reference our EasyModbus.dll file.
ModbusTCP MSAccess 110-min
ModbusTCP MSAccess 120-min

Our program will log the three registers based upon a timer function. When the time expires we will set the interval to 1 minute and call a subroutine (Button1). This subroutine will communicate to the PLC via Modbus TCP and get our three registers. It will then insert the data from the registers along with the time and date into an access database.

Here is what our visual basic form will look like:
ModbusTCP MSAccess 060-min

Here is the visual basic code for our project. We use the Imports command for the EasyModbusTCP namespacing.

Imports EasyModbus 'Import the EasyModbus 

Here is the part of the subroutine that will communicate to the PLC via the EasyModbusTCP, You will notice that we have used the ‘Try’ command on the ModbusClient.Connect() statement. This way we can track if we are communicating or not to the PLC.

Public Class Form1
  Private Sub Button1_Click() Handles Button1.Click
 'This subroutine will communicate using the EasyModbusTCP to the PLC
 'This will execute when the user clicks the button or at specific intervals from the Timer1
 Dim ComError = 0 'Set communication error flag to 0
 'Specify the IP Address and Port Number that we are connecting
 Dim ModbusClient As EasyModbus.ModbusClient = New EasyModbus.ModbusClient(TextBox1.Text, 502)
 Try
 ModbusClient.Connect() 'Connect to the PLC
 Catch ex As Exception 'What to do when an error occurs
 Label10.ForeColor = Color.Red
 Label10.Text = "Communication Error!"
 ComError = 1 'Set communication error flag to 1
 End Try
 If ComError = 0 Then 'Do the following when communication is OK
 Label10.ForeColor = Color.Black
 Label10.Text = "Logging..."
 Dim Registers As Integer() 'Where to store the information
 Registers = ModbusClient.ReadHoldingRegisters(0, 3) 'Read three registers starting at the first one
 Label1.Text = Registers(0) 'Value of MHR1
 Label2.Text = Registers(1) 'Value of MHR2
 Label3.Text = Registers(2) 'Value of MHR3
 ModbusClient.Disconnect() 'Disconnect from the PLC

Here is the code to open the database connection and insert the data into our ACC_Database file. We use the ‘Try’ command to ensure that if errors occur it will not stop our program. The connection string ( Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\AccLog\ACC_Database.accdb) may be different then yours. In order to get your connection string, please review the following from a previous post and search for connection string.

'Log values into an Access Database
 'We will use a SQL instruction to insert a record into the table
 Try
 Dim SQL As String 'SQL Command String
 Dim objCmd As New OleDb.OleDbCommand 'Command
 'Connection String to the Access Database
 Dim Con = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\AccLog\ACC_Database.accdb")
 'SQL Statement - All values must be set for the table
 SQL = "INSERT INTO ACC_Log VALUES ('" & Now.ToString("yyyy/MM/dd") & "', '" & Now.ToString("hh:mm:ss") & "', '" & Registers(0) & "', '" & Registers(1) & "', '" & Registers(2) & "')"
 Con.Open() 'Open the database connection
 objCmd = New OleDb.OleDbCommand(SQL, Con) 'Set the command
 objCmd.ExecuteNonQuery() 'Execute the SQL command
 Con.Close() 'Close the database connection
 Catch ex As Exception 'What to do when an error occurs
 Label10.ForeColor = Color.Red
 Label10.Text = "Database Error!"
 End Try
 End If
 End Sub

This timer is originally set for 100ms. When the program starts, it will log the first time and then set the timer interval to 1 minute. (60000ms)

 Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
   Label9.Text = Now 'Display time and date
 Timer1.Interval = 60000 'Set interval for 1 minute
 Timer1.Enabled = True
 Call Sub() Button1_Click() 'Call the routine to get the PLC information and store in a database
 End Sub

This will handle the LinkLabel on the form.

 Private Sub LinkLabel1_LinkClicked(sender As Object, e As LinkLabelLinkClickedEventArgs) Handles LinkLabel1.LinkClicked
   ' Specify that the link was visited.
 Me.LinkLabel1.LinkVisited = True
 ' Navigate to a URL.
 System.Diagnostics.Process.Start("http://www.accautomation.ca")
 End Sub
 End Class

ModbusTCP MSAccess 070-min

Running the program:
The status will show ‘Initializing..’ so that the imports can be loading in the program. This will happen each time the software starts.
ModbusTCP MSAccess 083-min

Logging will be displayed in the status to indicate that everything is working correctly.
ModbusTCP MSAccess 080-min

Error messages will show in the status when an error has occurred. We have programmed two errors:
Communication Error! – Information is not being received from the PLC. This could be due to the wrong IP address or communications links have been broken.
Database Error! – Information cannot be written into the database, the database is not present, etc.
ModbusTCP MSAccess 085-min

Here is what our database looks like after collecting a few samples.
ModbusTCP MSAccess 105-min

As you can see, collecting information from the PLC via ModbusTCP and inserting it into an Access database can be easily done. The next steps would be to ensure that the data is always collected. What happens when the communication to the PLC is cut? Robust logging is required. This is when we create a buffer in the PLC of the logged values. Please refer to our EBook ‘Robust Data Logging for Free’ .

Download the sample database, visual basic and PLC code for this project here.

Watch on YouTube : Modbus TCP Data Logging to Database
If you have any questions or need further information please contact me.
Thank you,
Garry



If you’re like most of my readers, you’re committed to learning about technology. Numbering systems used in PLC’s are not difficult to learn and understand. We will walk through the numbering systems used in PLCs. This includes Bits, Decimal, Hexadecimal, ASCII and Floating Point.

To get this free article, subscribe to my free email newsletter.


Use the information to inform other people how numbering systems work. Sign up now.

The ‘Robust Data Logging for Free’ eBook is also available as a free download. The link is included when you subscribe to ACC Automation.

Now You Can Have Data Logging Free

A data logger is also known as a data recorder or  data acquisition. It is a method to record data over a period of time and/or events.

The recorded information can come from sensors in the field. They can be digital or analog. With analog (voltage or current) we can measure temperature, pressure, sound, weight, length, etc. Digital data can be used for counts, times, events (motor overload), etc.

Data collecting can be time or event driven. Time based would be like collecting data every minute, shift, day etc. An event based collection would be from an error in the field such as an overload of a motor or a fault with a temperature controller.

stock-vector-analysis-magnifying-glass-over-seamless-background-with-different-association-terms-vector-69601843

Data mining / analysis is the most important part of the data logging.

Data mining / analysis is the way in which we look at the data and determine  what to do. Clustering is a method to look at the data in similar groups for comparison. An example of this would be the amount of material made on individual shifts in the plant.  Setting up the data logging in a way to examine the output over time is very helpful in determining methods to increase productivity in the manufacturing environment.

Time studies or observations are vital in the lean manufacturing world. Data logging can be useful in assisting with these studies. However, unlike the usual manual approach, this time study can be continuous.

Doing Time Observations

ebook_RobustDataLoggingforFreeData logging does not have to be expensive. It is also not as intimidating as it may sound.

The ‘Robust Data Logging for Free’ eBook is available in a free download. Just subscribe to ACC Automation to get the link for the free download.
 
This eBook will walk you though step by step on getting information into a database so you can start analysing the data. With traditional loggers, software will read the memory of the PLC and store in a local computer. If the network stops or the PLC communication fails then the logging will stop.
Creating a robust PLC data logger allows the communication to be stopped for a period of time without losing any of the data for collection. This is accomplished by storing the data locally on the PLC until communication is restored. All of the data is then read without loss. The amount of time that the connection can be lost will be dependent on the memory size of the PLC and the frequency of the data collected.
This series will walk you through the steps to create and implement a robust PLC data logger using the following equipment and hardware.
  • Automation Direct – Do-More – H2-DM1E PLC (Ethernet Modbus TCP)
  • Do-more Designer 1.3 (Simulator instead of PLC mentioned above)
  • Windows based computer running IIS
  • Visual Basic 6

Additional information on Omron Host Link Protocol and Indirect Addressing can be found in the eBook.

The ‘Robust Data Logging for Free’ eBook is available for a free download. Just subscribe to ACC Automation on the left side menu of the website to get the link for the free download.

Watch on YouTube : Now You Can Have Robust Data Logging For Free
If you have any questions or need further information please contact me.
Thank you,
Garry



If you’re like most of my readers, you’re committed to learning about technology. Numbering systems used in PLC’s are not difficult to learn and understand. We will walk through the numbering systems used in PLCs. This includes Bits, Decimal, Hexadecimal, ASCII and Floating Point.

To get this free article, subscribe to my free email newsletter.


Use the information to inform other people how numbering systems work. Sign up now.

The ‘Robust Data Logging for Free’ eBook is also available as a free download. The link is included when you subscribe to ACC Automation.

… and I also do PLC programming.

Programmable Logic Controller (PLC) programming is often thought as something everyone can do easily. We often design the system, install the hardware and then start to think about the PLC program and programmers. This approach can be improved. The landscape of PLC programming is changing and we must also change.

Computer Programming / PLC Programming
I believe that with all of the new functions of the PLC processor, you would be better served by someone that can do additional computer programming. Ladder logic has been taught in our schools for about 20 years or more. The basic bit programming in ladder is easily understood. Connecting to the manufacturers dedicated software  shows the power flow from left to right and shows the logic solved from top to bottom.

Some applications are fine if you are just replacing a few relays, however today’s manufacturing floor must be integrated. The existing hardware in the plant must also be connected to the PLC system. This will allow connection of data logging, email, vision system, motion control, HMI, computer servers, etc.

Ladder logic and the standardization of PLC’s on the plant floor has long been a topic for discussion and debate. The benefits were that anyone can look and understand the PLC logic to troubleshoot the system. Today the PLC can do allot more. Visually it can indicate /display its own troubleshooting and diagnostics to the engineer, electrician and/or operator in a variety of ways. You no longer need the skills on the plant floor all of the time. Using HMI (Human Machine Interface), computer screens, indication lights and email, just to name a few, information can be passed for troubleshooting and diagnostics. The investment in the program and integration of the system in your plant will pay for itself time and time again.

I believe that PLC programmers need more than just this programming language. They must have network and high level language skills to be capable of integrating the entire plant floor.

Let me know what you think? Are we teaching the new generation the right way?
Garry



If you’re like most of my readers, you’re committed to learning about technology. Numbering systems used in PLC’s are not difficult to learn and understand. We will walk through the numbering systems used in PLCs. This includes Bits, Decimal, Hexadecimal, ASCII and Floating Point.

To get this free article, subscribe to my free email newsletter.


Use the information to inform other people how numbering systems work. Sign up now.

The ‘Robust Data Logging for Free’ eBook is also available as a free download. The link is included when you subscribe to ACC Automation.