Excel as front-end - Access as Backend - Only INSERTS

0
votes

I am working on an Excel to Access Application, where the front-end will be an Excel workbook provided to 60 users who will be filling and submitting a form. On submitting the form, the form data will be inserted into a table (only one table) in an Access mdb file using VBA and ADO. The users will be only inserting records; NO UPDATES. (Basically it is a data entry application and to speed up data entry, multiple users will be using Excel files to input records into a common backend mdb database.)

Will there be any locking or concurrency issues givent that only INSERTS are going to be done? If yes, are there ways to handle them?

(I am aware that an SQL Express or other solution would be better than the MS-Access solution. But business requires an mdb file as it can easily be mailed to another person periodically for analysis.)

1
excelms-accessvba

1 Answers

2
votes

Consider building the form within Access instead of Excel. Forms are an integral component of Access and can update, append, delete table data without VBA or ADO since the table will be the form's RecordSource. But still Access forms allow VBA coding and embedded macros with interactivity (i.e., OnClick, OnOpen, AfterUpdate, BeforeChange, AfterDelConfirm events) more advanced than Excel UserForms.

Plus, Access allows simultaneous users up to 255. You can even split Access into two files for frontend and backend and distribute multiple frontends to all 60 users but maintain one backend data file. Even more, Access backends can upsize to any standard RDMS (Oracle, SQL Server, MySQL, PostgreSQL, DB2) using ODBC/OLEDB drivers.

Please don't settle with Excel just because it is a very popular, easy-to-use application. To build a UserForm, connect to a database via ODBC, and run looped inserts with VBA recordsets will amount to quite a bit of code when all this is native to Access and can be built without one line of VBA. Use Excel as an end-use document like a Word or PDF that interacts with static, exported data for formula, statistics, reports, and tables/graphs.

CONCURRENCY

Depending on the DAO or ADO calls or general database settings, various locking mechanisms can be set in MS Access tables, stored queries, or VBA recordsets:

  1. Pessimistic locking - where individual record is locked while first user is in edit mode
  2. Optimistic or no locking - where individual record is locked only while first user is in save mode; usually this is used in environments with low chance of concurrency of SAME record
  3. All records locking - where entire table is locked while first user is in edit mode

Usually, pessimistic locking is the default (Access forms employ this setting) where users after first user receives an error message if attempting to edit the SAME record. For your situation of data inserting, locking would not pose an issue but only if your other users can browse and edit previous data.