View Full Version : Help Architecting MSWord as Access Front-end

03-24-2018, 07:41 PM
My first post here; I'm nothing close to a seasoned MSOffice developer. Since this application doesn't seem unusual, I figured there are use-cases out there, but I couldn't find anything that seemed close enough. I titled this "MSWord as Access Front-end" but that might be mischaracterizing, as you'll soon be able to advise me. First the big questions to offer an initial sense of who wants to tackle this for me, then followed by the detailed description:

Will some dialogs be presented by Access VBA, and some by Word VBA?
Do free-text fields present an issue in Access?
Should I conceive this as solely an Access-controlled series of data-entry forms and then as a final step, through the use of some kind of Word template, have Access populate and generate that Word doc through OLE or DDE interprocess automation of Word?
If so, would that only work with a local instance of Access?
If the database could be remoted, would it require SharePoint?
If so, would Access handle it? Would it require SQL Server?
Is this a kind of application practicable online in a browser?
Is this app suitable only to the so-called split database approach?
Ideally the legacy doc at his elbow, so to speak, would be updated in near realtime as the dialogs pop and he navigates through them. Is that practical in a remote database scenario, or, even a local db scenario?

My client has been using a dense Word doc as a reporting mechanism, with a variety of controls with things like multiple strings of checkboxes, dates, a few free text fields, and text colorations depending on the value of a flag field. There are a couple Word doc tables whose number of rows will change based on the value of a another flag field in the site's Access dataset.

There could be a dozen Word docs per unit, multiple units per site, multiple sites per customer, and of course multiple customers. Clearly a relational database challenge that he's been trying to handle with gazillions of freestanding Word docs. There are about 70 customers, so we're hoping Access is still viable, although I wonder about the free-text data viz-a-viz Access.

My design objective requires retention of his current Word doc as the reporting/for-print mechanism, but gets him away from using it for data entry. Its ultimate role will be for printing reports only, but secondarily also as a backup data entry mechanism.

I'm conceiving of dialogs with user forms/content controls that he'd step through, not only field-by-field but unit-unit, site-site, etc. An initialized dialog form (if new customer, new site, new unit) or pre-populated dialog form would present itself when he wishes to be in sequential data-entry mode, the default. The master dialog must begin with a directory (and/or a search field) via which he could select a single customer, or down the hierarchy, a specific site or unit, etc. The final printed result is dictated by the legacy Word doc, which can be updated to use the latest content-controls if necessary.

In the initial unit dialog, some fields, fixed-length text and particularly dates (eg, of next scheduled service) would be calculated and automatically update the appropriate field of the current dataset (in the doc's datastore, or purely Access?) AND any relevant controls in a dialog not yet but soon-to-be-opened (eg., the next unit in the site's collection of units, etc). Don't think there'd be any caching available (correct?), so we'd hit the database for upcoming site names and master datasets as he pages through. My impression is that the conventional way to get entire Word docs like his legacy form into existence is an Access VBA that would create it all at once as a distinct VBA routine. True? Reasonable alternatives, or would latency kill us in the case of remoting the database?

Other considerations:

Database sharing via the cloud: By sharing, I don't mean simultaneous accessing by multiple users. I mean rotating users; rotations at a frequency of every few days or a week, but user workstations not necessarily on the same local net. Then there's dba-type maintenance by another person: me, also not ultimately on their local net. This is not an immediate requirement, but is being considered for a full deployment milestone (we could begin operating from a local instance of Access, a single workstation).

(I've a reputation for being long-winded -- hope this seems concise)

Thanks much in advance, Bob