Skip to content
Download
USE CASE

Self-service data entry, straight into your database

The people who own a vendor list, a price table, or a status flag should be able to maintain it themselves. Workbook Connect gives them Excel, with the columns, lookups, and rules locked down by you, so the data lands in your database without a single ticket.

Download free

Owners maintain their own tables

A business user edits the rows you let them edit and clicks commit. Workbook Connect validates the whole sheet first, then writes every row live to your database in one transaction. No export, no script, no nightly load to babysit.

EXCELDATABASE

The data engineer is not a data-entry clerk

Here is the loop you know too well. Procurement emails a list of fifteen new vendors. You paste it into a staging table, eyeball it for duplicate tax IDs, write an INSERT, fix the two rows where someone typed a payment term that does not exist, and deploy it through change control. A week later they want NET45 changed to NET30 on a dozen of those vendors, and the whole thing starts again. The people who actually know which vendor is active cannot touch the data, and the person with write access does not know the business. Every correction is a ticket, and your queue is full of changes a clerk could make if it were safe to let them.

Hand over the keystrokes, keep the keys

Workbook Connect points Excel at one table and clamps it down. You map the primary key on DimVendor, expose only VendorName, PaymentTermsID, and IsActive, and hide ModifiedBy and the surrogate keys the business should never see. You wire PaymentTermsID to a foreign-key lookup against RefPaymentTerms so the only selectable values are real codes. You set a filter so the vendor team sees their vendors and not the whole catalog. Then you save it. Roles decide who holds the Configurator job (you) and who is just a User (them), and that split is enforced on every connection. The business gets the keystrokes; you keep the keys.

The tables behind safe self-service

A business-owned table referencing a controlled lookup. The foreign key from DimVendor to RefPaymentTerms is what makes the term column self-validating: a user can pick NET30, but never invent PREPAY.

∞1PaymentTermsIDDimVendorVendorIDPKVendorNamePaymentTermsIDFKIsActiveModifiedByRefPaymentTermsPaymentTermsIDPKCodeNetDays

An illustrative data model, not your exact schema.

One setup, shared to everyone on connect

The hardest part of self-service is usually consistency, not access. When ten people each build their own workbook, you get ten subtly different ones. Workbook Connect removes that entirely: the table setup (lookups, filters, primary-key mapping, and which columns are visible) is configured once and shared to the team automatically the moment they connect. The new analyst who joins on Monday opens DimVendor and gets the identical guard-railed sheet the senior buyer has used for a year, because there is only ever one configuration, not ten copies drifting apart.

A controlled table as a business user edits it

Editable columns only, a foreign-key lookup on the term, and validation that catches the two rows a clerk would otherwise have slipped past you.

Vendors.xlsx, DimVendor
StatusVendorIDVendorNamePaymentTermsIsActiveNetDays
ChangedV-0142Northwind LogisticsNET30Yes30
V-0143Adriatic PackagingNET45Yes45
NewV-0288Helios Print Co.NET14No14
Needs fixV-0142Northwind LogisticsPREPAYYes0
Needs fixV-0301Borealis SteelNET60Yes60
ChangedV-0099Cascade SuppliesNET30No30
Commit Validated, then written to dbo.DimVendor
  • Row 4: term "PREPAY" is not in RefPaymentTerms, so the foreign-key lookup rejects it before commit. Nothing is written until this row is fixed, then every row commits together.
  • Row 5: NetDays 60 fails the rule "NetDays must be one of 14, 30, 45", so the commit is blocked until this row is fixed. Nothing is written until every row passes, then they all commit together.

An illustrative example of the editing workflow, not a product screenshot.

Validation runs before anything reaches the database

This is the part that lets you sleep. When the user clicks commit, your rules run in the sheet first and validate the whole sheet before anything is written. The two flagged rows fail: one references a payment term that is not in RefPaymentTerms, the other sets NetDays to a value your rule does not allow. Because any failing row blocks the commit, nothing is written until both are fixed, then every row commits together in one transaction. Both flagged rows stay in the workbook, marked in red with the reason. The user fixes the two rows and commits again. Nothing invalid ever touches DimVendor, and you never had to be in the loop to enforce it. A per-row status column marks each line as new, changed, or flagged for deletion the whole time, so the user always knows exactly what they are about to write.
testimonials

What our users think

"We used to write one-off scripts every time the business wanted to update reference data. With Workbook Connect they edit it themselves in Excel, and everything stays in sync with the database. It freed up an enormous amount of my time."
Benoit ZwartjesData Engineer

Works with your database

FAQ

Self-service data entry, answered

You configure access per table. For DimVendor you map the primary key, expose VendorName and PaymentTermsID, and hide the technical columns like ModifiedBy that the business should never touch. Connect a vendor-management role to that one table and leave every other table out of their setup. Granting access to one table is not granting access to the database.

No SQL, ever. They open Excel, click connect on the ribbon, and sign in with the authentication you already run (Entra ID with MFA, Service Principal, Windows Integrated, or SQL auth). Every read and write happens under those credentials, so they only ever see and change what their existing database permissions already allow.

Two layers. Foreign-key lookups mean a PaymentTermsID can only be a code that exists in RefPaymentTerms, so a typo like "PREPAY" is rejected before commit. On top of that, your validation rules run in the sheet (for example, NetDays must be one of 14, 30, 45). Workbook Connect validates the whole sheet in Excel first, and if any row fails, the commit is blocked and nothing is written. The failing rows stay in the sheet with their reasons, so you fix them and commit again, and then every row is written together in one transaction.

Roles split the two jobs. A Configurator authors the table setup and the validation rules; a User can only edit data within that setup. Authoring rules (the Validation Designer) and Manage Access are Pro features, but the roles and rules themselves are enforced for everyone on every connection, including the free plan.

You configure the table once. The lookups, filters, primary-key mapping, and hidden columns are saved and shared to the team automatically on connect, so the tenth person to open DimVendor gets the exact same guard-railed sheet as the first. Nobody rebuilds a workbook by hand.

Optimistic concurrency catches it. If someone changed V-0142 after you loaded it, the conflict is detected on commit and your write is held back rather than silently overwriting theirs. You re-read the current row and reapply your change deliberately.

No. Workbook Connect makes no server-side or schema changes. It reads and writes rows in the tables you already have, always encrypted over TLS 1.2 or higher, honouring your existing permissions. There are no triggers to install and nothing to deploy on the server.

Related use cases

Get out of the data-entry queue

Download Workbook Connect free, point it at one table, and lock it down. The team that owns the data starts maintaining it in Excel this week, inside the rules you set, and your ticket queue gets shorter.

Download free