S7-200 S7-300 Data Collection to SQL with OPC and ASCII

Overview

Siemens S7-200 and S7-300 controllers do not store historical process data natively in a relational database. Engineers must select a transport path from the PLC's MPI, Profibus, or PTP port to a PC, and then a software layer that can write tags into Microsoft SQL Server, MS Access, or MySQL. The selection depends on which PLC generation is in service, what communication hardware is available, and whether an HMI/SCADA layer (WinCC) is already deployed.

This reference covers the three production-proven paths used in the field:

1. Free ASCII Protocol (FAP) on S7-200 with a VB front-end.
2. PC-Access OPC DA server for S7-200, consumed by VB or Excel.
3. CP340 / CP341 / CPU313C-2PTP ASCII drivers on S7-300, paired with VB, WinCC, or third-party OPC.
4. WinCC Connectivity Pack / WinCC/ODK for direct SQL export from S7-300.

Each path terminates in either MS Access (.mdb/.accdb) via the Jet/ACE OLE DB provider, or SQL Server via the SQL Native Client / ODBC driver. The Access SQL syntax and the SQL Server linked-server model are both covered at the end of this article.

Architecture and Topology

The reference architecture separates the acquisition layer (PLC + comms module), the buffer layer (OPC server or VB program), and the storage layer (Access / SQL Server). Drawing the three boxes clearly avoids the common mistake of running SQL transactions directly against the PLC scan cycle.

For an S7-200, the most common layout is:

S7-200 CPU  --(PPI/MPI, RS485)-->  PC serial port or PC-Adapter USB
                                       |
                                       v
                              S7-200 PC-Access (OPC DA)
                                       |
                                       v
                              VB6 / Excel VBA  -->  MS Access (Jet OLE DB)
                                                          |
                                                          v
                                                       SQL Server (linked tables)

For an S7-300, the layout becomes:

S7-300 CPU  --(MPI/Profibus)-->  CP5611 / CP5613 / PC-Adapter USB
       |
       +--(PtP, RS232/422/485)-->  CP340 / CP341 / CPU313C-2PTP
                                            |
                                            v
                                   ASCII frames (custom protocol)
                                            |
                                            v
                                   VB / WinCC  -->  SQL Server / Access

Prerequisites

Option 1 — S7-200 with Free ASCII Protocol (FAP)

The S7-200 Freeport mode (SMB30 / SMB130 configuration) lets a user program transmit and receive raw ASCII frames on Port 0 or Port 1. This is the lowest-cost method because no Siemens OPC licence is required; you only need a PC-Adapter, a serial cable, and VB.

PLC Side (Micro/WIN ladder or STL)

// Configure Port 0 for 9600, 8, N, 1, freeport
// SMB30 = 16#09   (9600 baud, no parity, 8 data bits, freeport)
// SMB87 = 16#B4   (receive message, idle line detect, timer enabled)
// SMB88 = 16#0A   (end character = LF)
// SMB89 = 16#0D   (start character = CR)
// SMW90 = 1000    (idle timeout 1 s in ms)
// SMB94 = 100     (max message length)

// Transmit block XMT TABLE0, 0
// TABLE0 layout:  T+0050.0+P 00123.4+H 00998.7+\r\n
NETWORK 1
LD     SM0.1
MOVB   16#09, SMB30      // Freeport, 9600 baud
MOVB   16#0D, VB200      // Start char = CR
MOVB   16#0A, VB201      // End char = LF
MOVB   0,    VB202       // String length (rebuilt by VB)
ATCH   INT_0, 9          // Transmit complete
ATCH   INT_1, 23         // Receive complete
ENI

PC Side (VB6) — Polling the S7-200

VB opens COM1 at 9600,8,N,1, then sends the poll frame every 1000 ms. The reply frame is parsed into the SQL buffer.

' --- VB6 sample: poll S7-200 FAP and write to Access ---
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sReply As String

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Logs\plant.mdb;"

MSComm1.CommPort = 1
MSComm1.Settings = "9600,N,8,1"
MSComm1.PortOpen = True

Timer1.Interval = 1000

Private Sub Timer1_Timer()
    MSComm1.Output = Chr(2) & "P" & Chr(3)  ' STX + 'P' poll + ETX
    ' (the S7-200 is configured to reply on receipt of 'P')
End Sub

Private Sub MSComm1_OnComm()
    If MSComm1.CommEvent = comEvReceive Then
        sReply = MSComm1.Input
        ' Example reply: "T+0050.0+P 00123.4+H 00998.7\r\n"
        Dim tempStr As String, presStr As String
        tempStr = Mid(sReply, 7, 7)     ' "0050.0"
        presStr = Mid(sReply, 18, 7)    ' "0998.7"
        cn.Execute "INSERT INTO Tags (Ts, Tag, Value) " & _
                   "VALUES (Now(), 'TEMP', " & Val(tempStr) & ")"
        cn.Execute "INSERT INTO Tags (Ts, Tag, Value) " & _
                   "VALUES (Now(), 'PRES', " & Val(presStr) & ")"
    End If
End Sub

Option 2 — S7-200 with PC-Access (OPC DA)

SIMATIC S7-200 PC-Access V1.0 SP6 is a free-of-charge OPC DA 2.05a server from Siemens. It communicates with the S7-200 over PPI, MPI, or TCP/IP-Ethernet (with CP243-1) and exposes every V-memory and I/O point as an OPC item.

Configuration Steps

1. Install PC-Access on the data-collection PC. The default installation path is C:\Program Files\Siemens\S7-200 PC-Access\.
2. Right-click PC/PPI cable (COM1) in the project tree, set the PC-Adapter PPI address (default 0) and network baud rate (9.6 kbps / 19.2 kbps / 187.5 kbps).
3. Add a new Item for each tag. Format: V200 for VW200, VD500 for VD500, MB0 for MB0. Data type defaults to Word; right-click and change to Real (float) for temperature and pressure.
4. Click Test Client to verify each item reads a non-zero value. A yellow exclamation mark in the Quality column means the address does not exist in the CPU or the baud rate is mismatched.
5. Set OPC Server → Start. The ProgID exposed to clients is OPC.SimaticPCAccess.

VB6 OPC DA Client Sample

Dim WithEvents opcSrv As OPCAutomation.OPCServer
Dim opcGrp As OPCAutomation.OPCGroup
Dim opcItm(2) As OPCAutomation.OPCItem

Set opcSrv = New OPCAutomation.OPCServer
opcSrv.Connect "OPC.SimaticPCAccess"
Set opcGrp = opcSrv.OPCGroups.Add("PLANT")
opcGrp.IsSubscribed = True
opcGrp.UpdateRate = 1000

opcGrp.OPCItems.AddItem "V200", 1     ' temperature VW200
opcGrp.OPCItems.AddItem "VD500", 2    ' pressure VD500

Private Sub opcGrp_DataChange(ByVal TransactionID As Long, _
                              ByVal NumItems As Long, _
                              ClientHandles As Variant, _
                              ItemValues As Variant, _
                              Qualities As Variant, _
                              TimeStamps As Variant)
    Dim i As Long
    For i = 1 To NumItems
        If Qualities(i) <> 192 Then GoTo SkipBad  ' OPC_QUALITY_GOOD = 192
        Select Case ClientHandles(i)
            Case 1: WriteTag "TEMP", CDbl(ItemValues(i))
            Case 2: WriteTag "PRES", CDbl(ItemValues(i))
        End Select
SkipBad:
    Next i
End Sub

Private Sub WriteTag(tag As String, val As Double)
    cn.Execute "INSERT INTO Tags (Ts,Tag,Value) " & _
               "VALUES (Now(),'" & tag & "'," & _
               Replace(CStr(val), ",", ".") & ")"
End Sub

Performance tip: do not write one row per tag per scan. Buffer in a Collection object and flush every 5 s with a single INSERT INTO ... SELECT FROM bulk operation, which is typically 50× faster on Access.

Option 3 — S7-300 with CP340 / CP341 (ASCII or 3964R)

The CP340 (low-cost) and CP341 (high-performance) modules occupy one slot in the S7-300 rack and provide an isolated serial port. They support three modes:

• ASCII — printable character frames terminated by CR/LF; no handshaking overhead.
• 3964R — Siemens proprietary protocol with STX/ETX and BCC; suitable for partner-to-partner links.
• Modbus Master (RTU/ASCII) — CP341 only, requires the Modbus Master loadable driver (6ES7 870-1AA01-0YA0).

For SQL data logging, ASCII mode is the most common because the PC application can read the frames with any terminal, OPC server, or VB MSComm control.

CP340 Order Numbers

S7-300 Side (STEP 7 STL)

// FB2 P_SEND - send data block via CP340
// DB20 layout: 14 bytes ASCII "T=050.0,P=998.7\r\n"

CALL  FB2, DB50
     REQ   := M10.0              // rising edge triggers send
     LADDR := W#16#100            // I/O address of CP340 (256 decimal)
     DB_NO := 20
     DBB_NO:= 0
     LEN   := 14
     R     := M10.1               // reset request
     DONE  := M20.0
     ERROR := M20.1
     STATUS:= MW22

PC Side (VB6 with MSComm)

The code is structurally identical to the S7-200 FAP example; only the cable pin-out differs (CP340 ships with a 9-pin D-sub null-modem cable).

Option 4 — S7-300 with CPU313C-2PTP

The CPU 313C-2PTP (6ES7 313-6BF03-0AB0) integrates two PtP (point-to-point) interfaces on the CPU itself, eliminating the cost of a separate CP340/CP341. Functionally the same FB2 / FB3 (P_SEND / P_RCV) blocks are used.

Option 5 — WinCC with SQL Export

When a WinCC station is already on the plant network, you can avoid VB entirely and let WinCC write tags to SQL Server through one of three channels:

5.1 WinCC Tag Logging + Connectivity Pack

WinCC V7.x stores all historical values in a compressed archive. The Connectivity Pack (6AV6 371-1DR00-0AX0) exposes this archive as an OPC HDA server or as a WinCC OLE DB provider. A separate SQL job can SELECT from the archive tables and copy rows into the production SQL database on a schedule.

5.2 WinCC/ODK (Open Development Kit)

WinCC ODK is a C/C++ API that allows INSERT statements to be issued directly from a WinCC action. Typical pattern:

// WinCC VBScript action, triggered every 10 s
Dim cn, rs
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Data Source=PLANTSRV;" & _
        "Initial Catalog=Process;User Id=wincc;Password=***;"
cn.Execute "INSERT INTO dbo.Tags (Ts,Tag,Value) " & _
           "VALUES (GETDATE(),'PIT_1001'," & _
           HMIRuntime.Tags("PIT_1001").Read & ")"
cn.Close

5.3 WinCC V11 (TIA Portal) and Beyond

WinCC Professional V11+ no longer ships ODK. The replacement is the SQL Connector V15+ (6AV2 167-0AA00-0AX0), which uses a configuration UI instead of C code.

Access SQL — Target Schema

The Access database acts either as the final store for small plants or as a staging area that is later linked into SQL Server. A minimal but production-ready schema is:

CREATE TABLE Tags (
    Id      AUTOINCREMENT PRIMARY KEY,
    Ts      DATETIME             NOT NULL,
    Tag     VARCHAR(40)          NOT NULL,
    Value   DOUBLE               NOT NULL,
    Quality SMALLINT             NOT NULL
);

CREATE INDEX IX_Tags_Ts  ON Tags (Ts);
CREATE INDEX IX_Tags_Tag ON Tags (Tag, Ts);

For Access SQL specifics — SELECT, WHERE, JOIN, aggregate functions, parameter queries — refer to the official Microsoft reference: Access SQL: basic concepts, vocabulary, and syntax.

Useful Access SQL Queries

-- Last 60 minutes of all tags
SELECT Ts, Tag, Value
FROM   Tags
WHERE  Ts >= DateAdd("n", -60, Now())
ORDER  BY Ts DESC;

-- Hourly average per tag (Access uses parentheses around joins)
SELECT Tag,
       Format(Ts, "yyyy-mm-dd hh:00") AS Bucket,
       Avg(Value) AS AvgVal
FROM   Tags
WHERE  Ts >= #2024-01-01#
GROUP  BY Tag, Format(Ts, "yyyy-mm-dd hh:00");

Linking the Access Database into SQL Server

For multi-user reporting, link the .mdb / .accdb file into SQL Server as a linked server. The full procedure is documented at Import or link to data in an SQL Server database. The reverse direction (Access → SQL Server) is the more common path:

1. In SQL Server Management Studio, expand Server Objects → Linked Servers.
2. Right-click and choose New Linked Server. Set the provider to Microsoft.ACE.OLEDB.12.0 (for .accdb) or Microsoft.Jet.OLEDB.4.0 (for .mdb).
3. Set the data source to the full path of the Access file, e.g. C:\Logs\plant.accdb.
4. On the Security page, map a local SQL Server login to Admin with no password (Access has no security model).
5. Test the connection with a 4-part query:
   SELECT * FROM PLANT...Tags WHERE Tag = 'TEMP'

Alternatively, for one-off data moves, use the Access External Data ribbon → ODBC Database → Link to the data source by creating a linked table, then select the SQL Server DSN. The Access file becomes a SQL Server front-end; reports, forms, and queries all read SQL Server tables transparently.

OPC Server Selection Matrix

Throughput Sizing

Three rough rules of thumb for sizing the database write rate:

• MS Access .mdb ≤ 30 INSERT/s sustained before Jet write conflicts.
• MS Access .accdb ≤ 100 INSERT/s sustained (ACE engine).
• SQL Server Express ≤ 2 000 INSERT/s sustained with bulk-insert and a clustered index on Ts.

If the polling rate is N tags × f Hz, and the target is MS Access, keep N·f ≤ 30. For higher rates, buffer in a memory queue and flush every 5–10 s as a single bulk insert.

Troubleshooting Matrix

Verification Procedure

1. Start the OPC server or open the COM port in the VB application. Confirm the link LED on the PC-Adapter or CP340 is solid green.
2. In the PLC, force a known value into the monitored tag (e.g. MOVW 1234, VW200). The value must appear in the VB debug window within 100 ms (OPC DA subscription) or 1 s (FAP poll).
3. Inspect the SQL/Access table: SELECT TOP 10 * FROM Tags ORDER BY Id DESC. The forced value must appear in the last row, with a timestamp within 1 s of the force.
4. Run the polling for at least 24 h. Check the Access .ldb file is recreated cleanly on each open; check the SQL Server sys.dm_exec_requests shows no blocking.
5. Verify the bulk path: temporarily set the timer to 1 ms and confirm that Access does not return error 3035 (Couldn't lock file). If it does, switch to bulk-insert buffering.

FAQ