[dba-VB] Access data across the internet

jwcolby jwcolby at colbyconsulting.com
Fri Aug 28 11:19:23 CDT 2009


Gustav,

I have been working with this client refactoring the existing application.  It is in Access, and 
works entirely local to his SOHO.  In fact ATM he only has one or two computers running the 
application.  It has a single form about 10 fields, about 10 tables feeding combos and such.

He scans documents received from his clients, using Omnipage.  The user manually selects areas of 
documents and converts those to tables, the Omnipage software does that.  The documents are 
converted to CSV files and stored in a rather elaborate directory structure, probably about 150 
directories total.  Each directory can hold scans of many different documents.  That is all "front 
end" processing and the application is not CURRENTLY involved in that.

The application then imports each document, corrects scan errors, the user potentially manually 
edits fields in specific records if there is an OCR error where the fixit code didn't fix it.  The 
user does some analysis of the data.  Once satisfied with the edits, the data is exported back to a 
fixed width file which is a government specified format.  Those files are returned to his clients.

The application is really simple.  But he does want to be able to have several scanners in remote 
locations doing the scanning, and several instances of the application doing the conversion of the 
CSVs, and writing back to this fixed width file format, all reads / writes to a central location.

I think a simple Hamachi VPN is going to do the trick.  Being a SOHO and satellites in his kid's 
homes, corporate firewalls are less of an issue.  What is an issue is cost and complexity.  He and I 
ARE the IT department, and in the future IT will mostly be me.

So far what I have done for him has impressed.  He has another application planned which could be 
for sale.  That would be larger, would have to be more robust and a web application might be in 
order.  Even there though it appears that his clients are often smallish companies that might not 
have IT staff or not a big IT staff.

So I am coming up to speed on .net, C# etc in order to meet the future.  I think that this first app 
doesn't really NEED C#, it could be done totally in Access (it currently is in Access).  I do think 
that C# would be more robust and easier to update, upgrade and would handle some of the possible 
issues with talking to a centralized SQL Server instance a little easier.  If I have to go unbound / 
disconnected I might as well do it in C# or a cousin.

So I thought I would port it "for free".  Once the C# version does what the Access version already 
does and the client signs off on it, then I would go back to paid work on future stuff.  I need a 
live app in .Net to get me in Visual Studio on a daily basis.  I have done a fair amount of VB.Net 
stuff but without a reason to keep at it, it tends to be dropped after awhile.

 > A well-proven method would be to read and write these files from/to an FTP server.

I could certainly do that.  In fact I have to determine that Hamachi is actually capable to begin 
with, otherwise I will be back to casting around for solutions, and FTP might be the solution.

 > Another method is to store the files as fields in an SQL Server.

This is a great idea.  A simple application could monitor the directories and automatically open the 
files and import them into SQL Server.  Once in SQL Server, the current application would process 
SQL Server records instead of CSV files.  Likewise exporting the records back to the fixed width 
files for return to the client.

I will definitely have to keep that in mind.

John W. Colby
www.ColbyConsulting.com


Gustav Brock wrote:
> Hi John
> 
> A well-proven method would be to read and write these files from/to an FTP server.
> Another method is to store the files as fields in an SQL Server.
> 
> Both of these methods, however, may run into trouble meeting clients' firewalls and won't work at all if the server is off-line.
> 
> A novel approach would be store them at Amazon Web Services. This is incredible cheap and frees you from running a central server of any kind.
> 
> Finally, like Eric tells, the probably fastest method to setup is a web service - could be hosted anywhere.
> The VPN method and shared drives I would only recommend for a corporate setup.
> 
> It could be interesting following your considerations and solution for the project.
> 
> /gustav




More information about the dba-VB mailing list