Distributed Database (1 Viewer)

[syswizard]

I have a client who has a complex data entry requirement to support some government compliance program. They have clients all over the world. In a previous version of this program, they had the clients complete paper forms and have them scanned and emailed to their central office for posting into an Access database app that I created.
Now they want their clients to enter the data directly. Initially I thought building-out intelligent PDF forms was the answer, but that proved to be much too messy for the type of data being entered. The validation requirements were too complex. So then the thoughts of an Access-Web presence arose, but once again, the lack of VBA scripting to support the validation killed that idea.
Finally I decided on a distributed database whereby I would create a subset of the database for each client and send that out as a backend MDB file and they would use the same front-end as the home office and return the completed MDB file via email.
The only thing that scares me about this approach is the installation of the app at the remote client site. I plan on using the AC2010 runtime but I don't think it's practical to use the runtime packager because of the size of the final file that would be created. So they will have to install the runtime themselves, but I think I need an installer package for the front-end. The big issue is the first-time relinking of the backend....and setting the Trusted location of the target drive/folder.
Any and all suggestions greatly appreciated.

 

[ButtonMoon]

Finally I decided on a distributed database whereby I would create a subset of the database for each client and send that out as a backend MDB file and they would use the same front-end as the home office and return the completed MDB file via email.

That's not a distributed database; it's just file-sharing by email. It sounds like a rather user-unfriendly solution with plenty of pitfalls. Why can't you just create some web-pages that they fill in via a browser? Not sure why you'd need to use PDF technology to do that.

 

[syswizard]

Why can't you just create some web-pages that they fill in via a browser?

You make it sound easy. This same client has been awaiting their cloud-based CRM for over 4 years now....it's still not finished.
The front-end data entry app is not trivial...it's totally integrated into their CRM....it has cascading lists, cross column validation, multiple domain look-ups, optional "other" list logic to support data not in the domain, etc, etc.

 

[Mr Smin]

You are facing a headache there. First pitfall that comes to mind: Some email systems refuse Access attachments deeming them a security risk. You should check that won't affect you before going any further.
edit to add that supporting this could end up taking far too much of your time to the detriment of your other clients.

 

[syswizard]

You are facing a headache there. First pitfall that comes to mind: Some email systems refuse Access attachments deeming them a security risk. You should check that won't affect you before going any further.
edit to add that supporting this could end up taking far too much of your time to the detriment of your other clients.

Thanks for that. I've solved that problem easily: rename the mdb file to an obscure type ".abc"....and then encapsulate in a zip file.
The support issue definitely has me worried...no question about that.
What ideas do you have for accomodating this data entry requirement ?
Note: The guys who did the dot-net version of the CRM told my client to "forget it" as far as rolling out this new set of functionality before the end of the year.

 

[spikepl]

What ideas do you have for accomodating this data entry requirement ?

What requirement? You haven't defined any. You know the requirements we don't.You know the context/environment, we don't. The only clue is your .NET-guy, who says "forget it this year" . Which is good, or bad, or inddifferent? We do not now. If you want advice then spill the beans.

 

[Mr Smin]

The best solution might be to stay on paper until the CRM is ready. Trying to version-control lots of databases is going to be fiddly and the only alternative I can think of is to build a web interface to the database using eg javascript to do the validation. This is not going to be easy either - I don't think you have experience of this or you would already have mentioned it.

 

[syswizard]

Guys - I appreciate the feedback, but unfortunately, a deadline looms. The regulatory agency who forced this data entry requirement only complete the detailed specs 35 days ago. The deadline for submission is November 1st !!!
I'm going to plough ahead and report back. I am sure there are going to be issues......I've just got to insure they are not insurmountable.