Logging S7-300 Data to MS Access via OPC and WinCC

1. Problem Overview and Engineering Context

The requirement is straightforward on paper but ambiguous in the SIMATIC toolbox: a Siemens S7-300 CPU 315-2DP must receive digital feedback signals from two pieces of test equipment (a flow tester and a leak tester), plus a marker machine that prints a serial number on each part, and produce a traceable production record containing:

• Object serial number (string from the marker machine)
• Date and time stamp of the test cycle
• Pass / Fail result of the leak test (Boolean)
• Pass / Fail result of the flow test (Boolean)
• Optional: operator ID, recipe number, station ID

SIMATIC Manager itself is a programming environment, not a runtime data archive. There is no built-in "Log to file" or "Log to Access" button in STEP 7 V5.x. The logging path must be constructed from runtime components outside the PLC. This article enumerates every practical path, with the parts, versions, and configuration calls that an integrator can actually execute in the field.

2. Hardware and Software Prerequisites

3. Architecture Options Compared

For the original poster's scenario (a small test cell with discrete digital signals and a marker machine), Option A with WinCC flexible Runtime is the most defensible engineering choice. It is also the path most likely to pass a FAT with the end customer because WinCC is the vendor-supported, documentable component.

4. PLC Programming: Build the Production Record DB

Create a global data block in STEP 7 that mirrors the columns of the eventual MS Access table. Keeping the structure 1:1 eliminates field-by-field mapping in the SCADA layer.

4.1 Data Block Layout (DB 100 - "ProdRecord")

DATA_BLOCK DB 100
TITLE =Production Record Buffer
VERSION : 1.0
  STRUCT
    SerialNumber : STRING[20];   // from marker machine, ASCII
    DateTime     : DATE_AND_TIME;// DT format, 8 bytes BCD
    LeakResult   : BOOL;         // 0 = pass, 1 = fail
    FlowResult   : BOOL;
    OperatorID   : STRING[6];
    Recipe       : INT;          // 0..99
    Station      : INT;          // 1..16
    Trigger      : BOOL;         // rising edge = record ready
    RecordID     : DWORD;        // monotonic counter, 0..2^32-1
  END_STRUCT;
END_DATA_BLOCK

Total DB size = 24 + 8 + 1 + 1 + 8 + 2 + 2 + 1 + 4 = 51 bytes. CPU 315-2DP has 128 KB of load memory and 128 KB of work memory; the DB fits trivially.

4.2 OB1 Fragment: Latch the Test Results

// Latch leak test on rising edge of TestDone_Leak
A     "DI_Leak_TestDone";
FP    "M_Leak_Edge";r>JCN   L_End;r>            

4.3 Time Stamp Source

Do not use a free-running timer in the PLC. Either:

1. Read the WinCC / OPC client's local PC clock and write it back into DB 100 each cycle (simpler), or
2. Use SFC 1 "READ_CLK" to fetch the CPU's real-time clock (CPU 315-2DP has a hardware RTC backed by a CR2032 battery).

CALL SFC 1
     RET_VAL := MW 100
     CDT     := DB100.DateTime;

5. WinCC flexible Configuration: Tag Logging and Database Link

WinCC flexible 2008 SP5 uses a Microsoft SQL Server 2005 Express instance installed silently with the runtime. The runtime databases are:

• <Project>_L — Tag Logging (process values)
• <Project>_A — Alarm Logging

5.1 Declare the External Tag

In WinCC flexible, open Communication → Tags and add an external tag for each DB 100 field. Set:

5.2 Configure the Tag Logging Archive

1. Open Logs → Tag Logging, add a new archive named ProdArchive.
2. Add each tag from section 5.1 as a logged value. Choose On change for boolean fields and Cyclic, 1 s for the date/time and serial number.
3. Set the archive backing to Database (SQL Server). WinCC flexible stores it under C:\ProgramData\Siemens\WinCC flexible\Logs\.

5.3 Connect MS Access to the WinCC SQL Store

From MS Access 2016/365 (32-bit):

1. Open Access, choose External Data → ODBC Database.
2. Select Link to the data source by creating a linked table.
3. Click Machine Data Source and add a new System DSN pointing at SQL Server named WINCC_FLEX pointing to LOCALHOST\WINCCFLEXEXPRESS with Windows authentication.
4. Select the ProdArchive table; Access creates a linked table mirroring the WinCC schema.

You now have live linked tables. Build Access queries and reports against ProdArchive exactly as you would against a native Access table.

6. OPC DA Path: Bypass WinCC, Use KEPServerEX

If the project does not justify a WinCC flexible Runtime license, a KEPServerEX (or Siemens SIMATIC NET OPC Server) channel can be read directly from a VBA macro inside MS Access.

6.1 KEPServerEX Channel Setup

1. Install KEPServerEX V6.4 or later.
2. Add a new Siemens TCP/IP Ethernet driver. Set IP = the CP 343-1 address, port 102.
3. Create device S7_300, rack 0, slot 2 (CPU 315-2DP lives in slot 2 of rack 0).
4. Add tags mapping to the DB 100 offsets defined in section 4.1. Use the DB address mode (e.g., DB100.B0 as a String of 20 chars; DB100.B24 as Date of 8 bytes; DB100.B32.0 and DB100.B32.1 as Boolean).

6.2 MS Access VBA: Read OPC and Append to Local Table

' --- Module: modLogS7 (in MS Access .accdb, 32-bit) ---
Option Compare Database
Option Explicit

' References: OPC Automation 2.0, Microsoft ActiveX Data Objects 6.1

Public Sub PollS7()
  Dim oPC As OPCAutomation.OPCServer
  Dim oGrp As OPCAutomation.OPCGroup
  Dim oItem As OPCAutomation.OPCItem
  Dim iHandles(1 To 5) As Long
  Dim vValues(1 To 5) As Variant
  Dim vQualities(1 To 5) As Variant
  Dim vTimes(1 To 5) As Variant
  Dim cn As ADODB.Connection, rs As ADODB.Recordset
  Dim sSQL As String

  ' 1) Connect to KEPServerEX
  Set oPC = New OPCAutomation.OPCServer
  oPC.Connect "KEPware.KEPServerEX.V6"
  Set oGrp = oPC.OPCGroups.Add("S7Read")
  oGrp.UpdateRate = 500
  oGrp.IsActive = True
  oGrp.IsSubscribed = False

  Set oItem = oGrp.OPCItems.AddItem("S7_300.DB100.STRING20.0", 1)
  iHandles(1) = oItem.ServerHandle
  Set oItem = oGrp.OPCItems.AddItem("S7_300.DB100.DATE.24", 2)
  iHandles(2) = oItem.ServerHandle
  Set oItem = oGrp.OPCItems.AddItem("S7_300.DB100.BOOL.32.0", 3)
  iHandles(3) = oItem.ServerHandle
  Set oItem = oGrp.OPCItems.AddItem("S7_300.DB100.BOOL.32.1", 4)
  iHandles(4) = oItem.ServerHandle
  Set oItem = oGrp.OPCItems.AddItem("S7_300.DB100.DWORD.44", 5)
  iHandles(5) = oItem.ServerHandle

  oGrp.SyncRead OPCAutomation.OPCDevice, 5, iHandles, _
                 vValues, vQualities, vTimes

  ' 2) Append to local Access table tblProduction
  Set cn = CurrentProject.Connection
  sSQL = "INSERT INTO tblProduction " & _
         "(SerialNo, DateTime, LeakResult, FlowResult, RecordID) " & _
         "VALUES (?,?,?,?,?)"
  Set rs = New ADODB.Recordset
  rs.Open sSQL, cn, adOpenKeyset, adLockOptimistic
  rs(0) = CStr(vValues(1))
  rs(1) = CDate(WinCC_BcdToDate(vValues(2)))
  rs(2) = CBool(vValues(3))
  rs(3) = CBool(vValues(4))
  rs(4) = CLng(vValues(5))
  rs.Update
  rs.Close

  oPC.OPCGroups.Remove "S7Read"
  oPC.Disconnect
End Sub

' --- Helper: convert S7 DATE_AND_TIME (BCD) to VBA Date ---
Public Function WinCC_BcdToDate(raw As Variant) As Date
  Dim s As String, p As Integer
  s = ""
  For p = 1 To LenB(raw)
    s = s & Right("0" & Hex(AscB(MidB(raw, p, 1))), 2)
  Next p
  ' Format: YYYYMMDDHHmmss.mmm
  WinCC_BcdToDate = CDate( _
    Mid(s, 1, 4) & "-" & Mid(s, 5, 2) & "-" & Mid(s, 7, 2) & " " & _
    Mid(s, 9, 2) & ":" & Mid(s, 11, 2) & ":" & Mid(s, 13, 2))
End Function

6.3 Local Access Table Schema

CREATE TABLE tblProduction (
  RecordID    LONG          PRIMARY KEY,
  SerialNo    TEXT(20)      NOT NULL,
  DateTime    DATETIME      NOT NULL,
  LeakResult  YESNO         NOT NULL,
  FlowResult  YESNO         NOT NULL,
  OperatorID  TEXT(6),
  Station     INTEGER
);
CREATE INDEX idx_DT ON tblProduction (DateTime);

7. System Topology

The following inline SVG shows a complete Option A + Option B hybrid layout for a single-cell test station.

8. Verification and FAT Checks

After commissioning, run the following verification matrix. Each row is a discrete check; sign off each before moving on.

9. Error Codes and Field Diagnostics

10. Field-Proven Caveats

• OP177B cannot archive. If the customer insists on a Siemens panel, the OP177B must be replaced with a TP177B PN/DP (limited, no SQL) or, more practically, a Comfort Panel KTP1200 or TP1500. Comfort Panels can write historical data to a network share or to a SQL Server via the "Audit" or "Log" option.
• 32-bit MS Access has a 2 GB cap. At 50 bytes/record, that is ~40 million rows. For a single test cell running 24/7 at 1 part per 5 s, this is 6.9 years. For a multi-cell plant, escalate to SQL Server Express from day one.
• Do not use the S7 date/time BCD as a primary key. Use a monotonic DWORD counter (DB100.RecordID) incremented each cycle. This guarantees uniqueness even if two records share a millisecond.
• Watch the CP 343-1 connection count. KEPServerEX, WinCC, and STEP 7 can simultaneously open connections. CP 343-1 Lean supports 4 S7 connections total, CP 343-1 supports 8. Run out of connections and the OPC server reports "Out of resources" with WSAENOBUFS (10055).
• Watchdog timeouts. When a CP is reachable but the PLC is in STOP, OPC quality transitions to Bad. Make sure the VBA macro does not insert a row on a Bad-quality read.
• Regional settings. Access and WinCC both expect YYYY-MM-DD or the system locale format. Mixing en-US and de-DE installations on the same project corrupts date queries. Set one locale and use ISO format in code.

11. Frequently Asked Questions