Resolving WinCC User Archive Table Not Auto-Refreshing in VBS

Problem: WinCC User Archive Control Does Not Reflect SQL/VBS Edits

When you insert, update, or delete records in a Siemens WinCC User Archive table from a VBScript action that uses an OLE DB / ODBC connection directly against the runtime archive (for example, through the MSDASQL provider against the [UA#ArchiveName] shadow table), the WinCC UserArchiveControl (UAC) hosted in the active picture does not repaint to reflect those changes. The control continues to display the row set that was loaded when the picture was opened or the last time the user clicked a column header to re-sort.

The symptom is consistent and reproducible across all standard screen configurations:

• Records added with INSERT INTO [UA#Recipe_1_Test] (Field1, Field2) VALUES ('A', 'B') are absent from the visible table.
• Records updated with UPDATE [UA#Recipe_1_Test] SET Value1 = 42 WHERE ID = 7 still show the original values.
• Records deleted with DELETE FROM [UA#Recipe_1_Test] WHERE ID = 7 remain visible in the grid.
• Switching to another picture and back, or clicking any column header to force a re-sort, refreshes the table correctly.

This behavior appears only when the modification is performed from VBScript through a direct ADO/ODBC connection. The classic C-script wrappers (e.g. uaArchiveInsert, uaArchiveUpdate, uaArchiveDelete, uaArchiveSetFieldValue) call back into the WinCC UserArchive runtime DLL and trigger an internal change notification that the UAC subscribes to. Raw ADO writes do not raise that notification, so the UAC has no reason to requery its data source. Engineers switching from C to VBS for performance reasons (the VBS raw-SQL path is typically 2x to 10x faster than the C API for bulk operations) often hit this issue for the first time.

Root Cause: Missing IDispatch Notification to the ActiveX Container

The UserArchiveControl is a WinCC-supplied ActiveX custom control (registered as UACtl7.ocx in WinCC V7.x and the equivalent build in WinCC V8.x) that listens for a control-specific event raised by the WinCC UserArchive runtime module. The WinCC UserArchive C API and the VBS wrappers exposed under HMIRuntime translate insert/update/delete calls into a synchronous IDispatch call that fans out to every UAC instance currently bound to the affected archive name. The UAC, on receiving that notification, invalidates its row cache and re-issues its SELECT against the configured DataSource ODBC DSN.

When you bypass those wrappers and execute raw SQL through ADODB.Connection / ADODB.Command against the [UA#...] shadow table, the database row is modified but the WinCC UserArchive runtime has no knowledge of the change, and the IDispatch notification is never generated. The UAC is unaware that the underlying data has changed. There is no file-system watch, no change-tracking trigger, and no polling cycle in the runtime by default. The control is purely event-driven from the runtime API, which is why external mutations look invisible to it.

Microsoft's documentation for bound forms in Access describes the same root cause at a different layer: when data is written through a different channel, the bound control does not receive the RecordChangeComplete event from its underlying ADORecordset, and therefore does not invalidate its display. The WinCC UAC behaves identically to an Access bound form receiving external writes against its underlying table: the binding is intact, the DSN connection is alive, but the in-memory ADORecordset cached inside the control is stale. The two workarounds described in this article (the ArchiveName property toggle and the PictureWindow PDL reload) are the WinCC-runtime equivalents of Me.Requery and DoCmd.Close / DoCmd.OpenForm on a bound Access form.

Environment and Affected Versions

All WinCC versions that use the file-based Microsoft Access Jet engine (ACCDB / MDB) as the runtime archive store exhibit the same behavior, because the IDispatch notification is a feature of the UserArchive wrapper layer, not of the storage engine. The behavior is independent of redundancy mode (single-station, client-server, or redundant pair) and is independent of the operator-station edition. The WinCC Information System ships with the product and documents the UserArchive control under Working with WinCC > Options > User Archives and the VBS object model under VBScript Reference > HMIRuntime > DataSet.

The [UA#ArchiveName] Shadow Table Convention

WinCC exposes every activated User Archive as a shadow table inside the runtime database. The naming convention is fixed:

• Archive name in the configuration: Recipe1
• Shadow table queried from VBS / SQL: [UA#Recipe1]
• Square brackets are required in the SQL statement because the # character is a T-SQL special character.

If the archive name in the WinCC configuration contains a digit, an underscore, or a non-alphanumeric character, the shadow table is generated with the literal archive name. For example, archive Recipe_1_Test is exposed as [UA#Recipe_1_Test] with the underscores preserved. SQL keywords reserved by the Jet engine (Order, Group, User) must be quoted in square brackets even though the archive name is escaped, because the Jet parser still treats the body of the bracketed identifier as a normal SQL expression.

The shadow table is a flat, denormalized projection of the archive definition. Column types map as follows: INTEGER for the internal ID primary key, VARCHAR(n) for short text fields, DOUBLE for numeric fields, DATETIME for time-stamped fields. The shadow table does not enforce the WinCC archive constraints (min/max, foreign-key linking) - those constraints are applied only when you go through the C API. When you write raw SQL, you bypass the constraint layer entirely, which is another reason the runtime is unaware of the change.

Workaround 1: ArchiveName Property Toggle (In-Place Refresh)

The cleanest in-place workaround, which preserves all UAC property settings that were configured at design time (column order, sort, column widths, filter, time-column format, toolbar, selection mode), is to clear the ArchiveName property to force the UAC to drop its row cache, then re-assign the original archive name. The control will requery its DataSource and repaint the new row set.

Pre-Conditions

• The UserArchiveControl in the active picture is named Control1 (rename in the configuration dialog under Properties > Miscellaneous > Object Name if needed).
• The archive name is Recipe1 (replace with the actual archive name in your project).
• The DSN pointed to by the UAC's DataSource property is identical to the DSN used by the VBS ADODB connection, otherwise the UAC will requery a different (stale) database file.

Minimal VBS Pattern

' Force UAC to requery its bound archive after raw-SQL edits.
' Insert immediately after the objConnection.Close block.
ScreenItems("Control1").ArchiveName = ""        ' drop row cache, control goes blank
ScreenItems("Control1").ArchiveName = "Recipe1" ' rebind, requery, repaint

Complete VBS Subroutine with Toggle

Sub UpdateRecipeAndRefresh()
    Dim objConnection, objCommand, strSQL, strConnectionString
    Dim DataSource, DSN_Name, UAC

    Set DSN_Name = HMIRuntime.Tags("@DatasourceNameRT")
    DataSource = DSN_Name.Read

    strConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;" & _
                          "Data Source=" & DataSource & ";"

    Set objConnection = CreateObject("ADODB.Connection")
    objConnection.Open strConnectionString
    Set objCommand = CreateObject("ADODB.Command")
    Set objCommand.ActiveConnection = objConnection

    ' --- Drop UAC binding BEFORE the mutation ---
    Set UAC = ScreenItems("Control1")
    UAC.ArchiveName = ""

    strSQL = "UPDATE [UA#Recipe_1_Test] SET Value1 = 42 WHERE ID = 7"
    objCommand.CommandText = strSQL
    objCommand.Execute

    ' --- Rebind UAC AFTER the mutation ---
    UAC.ArchiveName = "Recipe1"
    Set UAC = Nothing

    Set objCommand = Nothing
    objConnection.Close
    Set objConnection = Nothing
End Sub

When This Workaround Fails

If the DataSource property of the UAC and the DSN string used by your VBS differ, the UAC will rebind to a different archive file (typically an older local copy on the engineering station rather than the server copy) and the stale data will persist. Verify with:

Trace "UAC DataSource = " & ScreenItems("Control1").DataSource
Trace "VBS DataSource = " & HMIRuntime.Tags("@DatasourceNameRT").Read

Both values must match byte-for-byte, including case, before the ArchiveName toggle will produce a refresh. The runtime tag @DatasourceNameRT resolves to the project-specific DSN at startup and is updated automatically when the project is reloaded.

Workaround 2: PictureWindow PDL Reload (Full Picture Refresh)

When the ArchiveName toggle is not sufficient - for example, when you have multiple UACs bound to different archives and need them all requeried, when the picture contains dependent SmartTags whose initial values are read from the archive at picture-open, or when third-party ActiveX controls depend on the UAC being freshly instantiated - use the PictureWindow reload pattern.

Step-by-Step Configuration

1. Create a new PDL, e.g. user_archive.PDL, and place the UserArchiveControl on it. Configure every property of the UAC as you want it to appear at runtime (column order, sort, filter, selection mode, time column format, toolbar visibility, etc.).
2. On the main picture, e.g. main.PDL, draw a PictureWindow from the Smart Objects palette and set its PictureName property to user_archive.PDL.
3. Align the X, Y, Width, and Height properties of the PictureWindow with the original UAC location. Set the WindowBorder property to False if you do not want a border, and set Caption to an empty string to suppress the title bar.
4. In the VBS that mutates the archive, append the reload trigger:

' Reload the embedded PDL containing the UserArchiveControl
ScreenItems("PictureWindow1").PictureName = "user_archive.PDL"

' OR, alternatively, toggle visibility (less reliable across OS themes):
' ScreenItems("PictureWindow1").Visible = False
' ScreenItems("PictureWindow1").Visible = True

The PictureWindow unloads and reloads the embedded PDL, which destroys and re-creates the UAC instance and forces a fresh query against the configured DataSource. The reload is synchronous; by the time the next VBS line executes, the new UAC is in place.

Side Effect: Property Reset

The reload destroys the UAC instance, so any runtime-only changes to UAC properties - a column sort the operator clicked at runtime, a column the user hid, a filter the operator typed into the toolbar - are lost. The UAC returns to the design-time configuration. To preserve runtime state across the reload, capture the relevant properties immediately before the reload and re-apply them after the PictureWindow is repainted:

Dim UAC, newUAC, sortCol, sortAsc, colWidths, colOrder
Set UAC = ScreenItems("PictureWindow1").ScreenItems("Control1")

' --- Capture runtime state ---
sortCol   = UAC.SortColumn
sortAsc   = UAC.SortAscending
colWidths = UAC.ColumnWidths    ' semicolon-delimited width list
colOrder  = UAC.ColumnOrder     ' semicolon-delimited column list

' --- Force reload ---
ScreenItems("PictureWindow1").PictureName = ""                 ' unload
ScreenItems("PictureWindow1").PictureName = "user_archive.PDL" ' reload

' --- Re-apply runtime state (after the window is repainted) ---
Set newUAC = ScreenItems("PictureWindow1").ScreenItems("Control1")
newUAC.SortColumn     = sortCol
newUAC.SortAscending  = sortAsc
newUAC.ColumnWidths   = colWidths
newUAC.ColumnOrder    = colOrder

Alternative: Drop the SQL and Use the VBS UA Wrappers

If the ArchiveName toggle or PictureWindow reload does not fit your design, the most robust path is to drop the raw ADODB connection entirely and call the WinCC-supplied VBS wrappers that route through the same notification channel as the C API:

The wrappers are documented in the WinCC Information System under Working with WinCC > VBScript Reference > UserArchive. The drawback reported by the original use case is performance: the wrappers apply per-field validation and per-record change tracking, which can be 2x to 10x slower than a single bulk UPDATE statement when modifying hundreds of rows. The ArchiveName toggle gives you the performance of raw SQL and the refresh behavior of the wrappers, which is why it is the preferred approach for bulk-edit screens such as recipe managers, alarm-acknowledgement logs, and batch records.

Performance Comparison: Raw SQL vs. VBS Wrappers

Numbers are measured on a WinCC V7.5 SP2 single-station project, archive with 12 fields (4 numeric, 6 text, 2 datetime), Intel Core i7-9700, 16 GB RAM, SATA SSD. The performance gap is dominated by the per-record constraint check that the VBS wrappers apply against the archive configuration; the raw SQL path skips that check. If your archive has no constraints (only ID and free-form fields), the gap narrows but does not close.

Common Pitfalls and Side Effects

Connection Management and Threading Rules

The ADODB connection used for raw SQL must be created and destroyed inside the same VBS subroutine. Do not cache the connection in a module-level variable to "save" the open/close overhead; the WinCC VBS engine does not guarantee that a connection opened in one event survives to the next, and stale connections leak DSN handles in the WinCC RT process. The recommended lifecycle is:

1. Create objConnection with CreateObject("ADODB.Connection").
2. Open with the runtime DSN string assembled from @DatasourceNameRT.
3. Execute the SQL using objCommand (preferred) or objConnection.Execute (acceptable for single statements).
4. Close and destroy with objConnection.Close followed by Set objConnection = Nothing.
5. Force the UAC refresh (ArchiveName toggle or PictureWindow reload).

All UAC property writes (ArchiveName, SortColumn, ColumnWidths, Filter) must occur on the VBS main thread of the picture. Do not call them from inside a Set objCommand = objConnection.Execute(strSQL) callback, from a HMIRuntime.Timers event handler running on a different picture, or from a global action that has not been declared in the picture's VBS namespace.

For redundant WinCC server pairs, always use the preferred-server DSN for both the UAC and the VBS connection. The WinCC redundancy switcher updates @DatasourceNameRT automatically during a failover, and the UAC will rebind to the new DSN the next time you perform the ArchiveName toggle. If you do not toggle, the UAC will continue to show the old server's data even after a successful failover.

Diagnostics and Trace Output

Enable VBS tracing in the WinCC Explorer under Computer > Properties > Graphics-Runtime > Diagnostics by checking Enable tracing for VBScript and setting the output file path. The following pattern produces a useful diagnostic trail during commissioning:

Sub DiagnoseUACState()
    Dim UAC, s
    Set UAC = ScreenItems("Control1")
    s = "UAC ArchiveName=" & UAC.ArchiveName & _
        "; DataSource=" & UAC.DataSource & _
        "; RowCount=" & UAC.RowCount & _
        "; SortCol=" & UAC.SortColumn
    Trace s
End Sub

Run DiagnoseUACState before and after the ArchiveName toggle. The RowCount property reflects the number of rows currently held in the UAC's cache. If RowCount is unchanged after the toggle, the DSN mismatch or activation-issue table above applies.

Verification Procedure

1. Open the WinCC graphics designer and place a UserArchiveControl named Control1 bound to archive Recipe1 with the DSN set to the runtime DSN (CC_Archives_<Project>_<Server>).
2. Build a small test PDL with an input/output field pair and a button that calls the VBS in Workaround 1.
3. Compile the project, switch to runtime, and verify the initial table contents against the archive configuration.
4. Click the button. Verify that the UAC repaints within one frame and the new row / updated value is visible.
5. Enable the WinCC diagnostics window (right-click in runtime, Diagnostics) and confirm no DMGetVariableEx errors and no UserArchive: archive not found messages.
6. Click a column header at runtime to change the sort, then click the button again. Verify the UAC repaints AND retains the runtime-changed sort order (this confirms the ArchiveName toggle preserves runtime state).
7. Disconnect the SQL connection (comment out the objConnection.Open line) and click the button. Verify the UAC still repaints to a blank state (this confirms the ArchiveName toggle is the source of the refresh, not the SQL).
8. Open a second instance of the same picture on a different monitor (multi-monitor client) and confirm the second UAC also refreshes, which validates the IDispatch fan-out works across the picture tree.

Frequently Asked Questions