Others

Design a Multi-Location Check Database in Microsoft Access

Chanuka GeekiyanageChanuka Geekiyanage Nov 22, 2026 868 views

Question details

Design a central check database that supports multiple locations, simultaneous users, and site-specific access.

Design a Multi-Location Check Database in Microsoft Access
Product
Microsoft Access
Device & OS
not provided
Scenario
Headquarters needs a central check database while each location adds or edits only its own records.
Observed behavior
The desired setup keeps site updates connected to central data and limits each location to its own checks.
Before you start

List the fields each location must maintain and decide how users will be identified and assigned to sites. Confirm whether locations connect over a local network or remotely, and whether site-level access must be enforced as a security requirement.

Solution 1Recommended

Build a Relational Access Front End With a Secure Central Database

Use related tables and a central server database when multiple locations need concurrent access and enforceable site restrictions.

Separate the Access forms and queries from the central data. Each user works in a local Access front end connected to the same server database, so saved changes are available to other users without distributing separate copies of the data.

Filtered forms improve usability but do not secure an Access table against direct access. For enforceable site isolation, use a server database with authenticated users and server-side row-level security or equivalent permissions.

1
Create the related tables

In Access, use Create > Table Design to define Sites, Vendors, and Checks. Give each table a primary key; include CheckNumber, CheckDate, CheckAmount, VendorID, and SiteID in Checks, and store site and vendor descriptions in their respective tables.

2
Define relationships

Open Database Tools > Relationships, add the tables, and relate Checks.SiteID to Sites.SiteID and Checks.VendorID to Vendors.VendorID. Enable referential integrity where appropriate.

3
Move data to a server

Use SQL Server Migration Assistant for Access or another suitable migration method to move the tables to a central server database. In Access, use External Data > New Data Source > From Database > From SQL Server to link the server tables through ODBC.

4
Enforce site access

In the server database, map authenticated users to their permitted SiteID and configure row-level security or equivalent permissions for reads, inserts, updates, and deletes. Give headquarters the separate access it needs, and test that users cannot change a record to an unauthorized SiteID.

5
Create site forms and deploy

Use Create > Form or Form Wizard to build check-entry forms based on the linked tables. Filter each form to the signed-in user's permitted site, distribute a local front-end copy to each user, and test simultaneous edits and visibility from different site accounts.

Build a Relational Access Front End With a Secure Central Database
Access-only back ends have limitations: A split Access database on a shared local network can support multiple users, but site-filtered forms alone do not provide secure row-level isolation. Avoid opening a shared Access back-end file over a wide-area network; use a server database or an appropriate remote application setup instead.
Free Microsoft Office alternative

Use WPS Office for Check Lists and Reports

WPS Office is a free, lightweight alternative for familiar spreadsheet and document work. Open compatible Office files and move existing check lists into a familiar interface. It does not replace an Access database or provide its multi-user database security.

Review and organize check lists in WPS Spreadsheets.Open and edit Microsoft Excel formats such as XLSX and XLS.Work in a familiar interface that makes migration from Office files easier.Use a lightweight office suite for everyday documents and reports.
microsoft office alternative - wps office

Frequently Asked Questions

Can I give each site a query that shows only its checks?

Yes. Create a query with a SiteID criterion and use it as the source for a form. This makes records easier to navigate, but the filter is not a security boundary if users can access the underlying tables.

Do site updates automatically reach the central database?

When a form uses linked central tables, saved edits are written to the central database rather than to a separate site copy. Other users may need to refresh or requery their forms to see the latest changes.

Can several people edit checks at the same time?

Yes. A central server database can support concurrent users, although simultaneous edits to the same record can cause conflicts. Test the Access form's record-locking behavior with representative users.

Should each location have its own check table?

Usually, no. Store checks in one Checks table with a SiteID foreign key, and keep site details in a separate Sites table. Use forms and server-side permissions to present and restrict the appropriate records.