Connecting a business app to Sage 100c: the two approaches

Any company running Sage 100c eventually asks the same question: how do we connect something to it that does what Sage does not — mobile order entry, a customer portal, a collections tool, an analysis assistant.

There are two ways to do it. They are not in competition: a well-designed integration uses both, each for what it does well.

Approach 1 — direct database access

Sage 100c stores its data in SQL Server. An application can connect and read the tables directly.

What it allows. Reading, and it is excellent at it. You reach the whole data model, with no volume limit and no intermediary. A three-year customer summary is one query.

What it rules out. Writing. Or more precisely: technically nothing stops you, and that is exactly the problem.

The Sage data model carries business rules that are not all expressed in the database schema. Counters, header-to-line consistency, analytical postings, document chaining. A direct write that ignores one of them produces a database that is consistent for SQL Server and inconsistent for Sage. The symptom appears weeks later, often at period close.

The rule to remember: read directly, never write directly.

Approach 2 — the dedicated web service

The second approach routes writes through a service that talks to Sage using its own components rather than its database.

What it allows. Writing, while respecting Sage’s business rules. A document created this way is identical to one keyed in the Sage interface: same checks, same counters, same postings.

What it costs. One more component to install and maintain, and lower throughput on bulk reads. It is not the right tool for pulling three years of history.

Why both rather than one

The split that works in production is simple:

Operation	Channel
Lookups, searches, reports, dashboards	Direct database access
Creating and changing documents, accounting entries	Web service

That is the architecture we use in OptiFlowPro. Reads go through direct access, which keeps screens instant even on large databases. Every write goes through the web service, which guarantees that a document created from a phone in the field is a full Sage document.

Three things to check first

The Sage version. Available components and the data model vary between versions. What works on one installation does not necessarily work on another.

Customisations. Custom fields, scripts, bespoke development: they are part of the real data model, and an integration that ignores them breaks them.

Multi-company. An installation with several companies means several databases. Every query must be qualified by the company concerned, without exception. An unqualified query eventually reads from or writes to the wrong database — and that class of error is very expensive to detect.

In short

Read directly, write through the service. It is the only split that gives you both fast screens and data your accounting can rely on.

If you are considering an integration on your installation, the preliminary audit exists precisely to answer those three questions before the first line of code is written.