Design a Multi-Location Check Database in Microsoft Access
Question details
Design a central check database that supports multiple locations, simultaneous users, and site-specific 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.
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.
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.
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.
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.
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.
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.
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.

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.

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.




