Forum Discussion
Create a data entry form based on prepared document
Hello Gerson.
Please refer to this most excellent Microsoft support documentation for details: https://support.office.com/en-us/article/ways-to-share-an-access-desktop-database-03822632-da43-4d8f-ba2a-68da245a0446
I think you want to split the database (see Share a Split Database in the documentation). You'll keep a "backend" database with all the tables on a network share that all your end users have access to, and a copy the "frontend" database with forms, queries, etc. on the end users computers. You'll probably want to restrict access to only those fields to be updated by the end users.
Microsoft has made splitting the database simple using the Split Database tool. It's called Access Database in the Move Data section of the Database Tools tab of the Access Ribbon. Using this method allows multiple users to efficiently access the same tables at the same time from local copies of the database.
There are other methods detailed in the documentation listed above, some involve using SQL server and SharePoint, which are great solutions, but maybe more than you need.
I hope this helps.
les_stuart - thanks very much for your quick response. The shared or split database won't work in this case, because the users (callers) don't all have Access (probably none of them do), and there's no Sharepoint or similar platform. Let me reformulate the question.
Let's forget about the data entry on the caller side. But let's say I just want to make a custom script for them, using selected fields. Logic says it should be possible to do that with either a report or a form. But Access seems to limit text input on form/report design to pre-existing data fields. I guess what I'm looking for, although maybe not using the right term, is the ability to create a text mask that would sandwich in selected fields.
On the other hand, maybe it's just too complicated. Pen and quill used to work very well. 🙂
Hmm... Maybe I don't completely understand the problem, but it sounds like what you might want is to create a form, like a Word form, or maybe a PDF form, and export the fields from the form to a CSV file (A.K.A. an Excel document) that can be imported into your database.
I'll consider a Word form.
There is a How To document called "How to Export Data From a Word Form to Excel" on Life Wire that may help you create the form and export the data. There is also a document for Form creation called "Create forms that users complete or print in Word" at Support.Office.com. I suspect you could record a Word Macro for the export and add it to a button on the form so the end user can export the data consistently.
To Import the CSV data into Access you'll want to go to External Data -> New Data Source -> From File, and select Excel or Text File. In the Get External Data dialog you'll want to select the Append a copy of the records to the table, and select a table. You can save the import steps and rerun them from Saved Imports.
The up-side to this approach is that there is no code to write; down-side to this approach is that the file name will need to be consistent for the saved import to work. It would be best if you could bundle all your data into one big file before importing. This could be done running a simple ECHO >> command from a batch file.
Too many moving parts perhaps.
It is also possible, although it would require some VB coding skills, to write a Module to concatenate the files, or simply import all the files from a folder into the database. For that there is a document called "VBA procedure to import a CSV file into Access" at Stack Overflow. There is also a document called "Loop through files in a folder using VBA" that might help with in selecting all files in a folder to import.
A Module can be created from the Create tab and assigned to a button on a form.
Hope this helps a little.