Sending webform data to pc quickly

I wouldn't use Access for anything web related.

I have seen people use asp to connect to access.
Depending on the exact need, a form timer event could possibly be used.

But truthfully, if you want the event web form driven and not wasting resource then I'd use proper web technologies (PHP, .net, ...).

Hi Julian, re your qs:


> You are writing files as your primary source of data - how big are the files? The response time will be directly related to this.
the files are small  text files, a dozen or so lines long, so this won't be a bottleneck

>Are you saving the files in Access or adopting a more standard approach of saving the files to disk and storing the data in Access.
the files would probably be saved to a web server using PHP, and then sent somehow to the local PC

> Access is not a great idea - but I get the impression you are not using it to process the actual Web Requests - you are going to use it to poll some server resource looking for updates?
the simplest web request would be a search, so the result would also just be a few lines of text

> The question then is what will you use on the server to queue the request?
the queue will be on the PC (acting as an "offline" server), the web server (bluehost) will just capture requests

> One option is simply to have a script (PHP, VB.Net etc) that accepts the incoming form post and saves the file to a folder.
yes to capture, then how to get the files to the PC?

> Your "server" can then check this folder every N seconds for files - if it finds any it processes them.
yes that's on option, probably the one i'll go with

> This is obviously predicated on the data only comprising files - not other data. If you have other data then that could be written to a text file with a reference to the uploaded file - the process remains the same.
yes only text files

> You could also set up a folder watch on a specific folder that is triggered whenever an event happens on that folder - that way incoming data could trigger a process that pushes the data to Access.
yes that sounds good

>This is just shooting from the hip - we need more information about your setup and what you are trying to do.
thank you for your detailed input

Please describe your architecture in a bit more detail. What are the components that make this up.

We know you have
1. A Blue Host hosting package where your receiving script will live
2. A PC behind a firewall ?
3. On the PC there will be access - anything else that is important
4. Will your primary code be written in Access or will Access just be the data store?

2. A PC behind a firewall ?
There is usual windows security. I am assuming the text files cannot be a threat to the PC.

3. On the PC there will be access - anything else that is important
Let's assume the PC is standalone with only the MS Access app

4. Will your primary code be written in Access or will Access just be the data store?
Access just parses the text input file and generates a text output file

Flow will be
webform -->  PHP to web server ----- somehow  ---> to PC folder  -- watch (eg VBA) ---> MS Access ---> output file ----somehow ---> web server

PS the watch could be VBA watching a web folder, just thinking in terms of reducing any unnecessary traffic as VBA is quite clunky

THis whole process is just to provide a simple way to access a prototype database. After prototyping, a full web-based solution will be developed.

2. A PC behind a firewall ?

The PC is not visible to the Web Server - there is no route from the Web Server to the PC.

Access just parses the text input file and generates a text output file

Then why are you using Access - parse files can be done on the server using PHP - what is it that specifically requires Access?

If the final result ends up back on the Web Server why are you even bringing Access into the mix?

cost
i don't have the web skills so will need to get funding once the prototype works

testing is faster in Access ( logical testing, not coding)

The PC is not visible to the Web Server - there is no route from the Web Server to the PC.

Ok thanks good point. So the PC will need to fetch the data, eg the VBA can watch an index file on the server, and grab any new files (which will have been security checked on the server)

Having written many API systems (which is what you're describing)...

Use a public, dedicated LAMP server. Cost will be from $5-$200/month, depending on resources you required.

Check out Kimsufi, SoYouStart, OVH for hardware to suit your requirements.

Using LAMP - Linux + Apache + MariaDB + PHP on a public server will speed up your project development time + solve any problems which will come up over the lifetime of your project.

Thanks David, I'll consider that down the line, and likely will post a separate question.

For now cost issue is professional time/skill, not the server costs you mention which are negligible / not an issue.

I'll probably go with a VBA script to watch a web folder for new requests. I'll report back and close shortly. Meanwhile any other suggestions welcome.

Thanks for your inputs

Or an AutoHotKey script

Probably a good first question will be to consider how this system will be used.

Using Access... well... Access isn't really well suited for any type of Web based system.

You can use it... just like you could write your system in RPG, Smalltalk, Forth or COBAL.

Anything's possible. Whether it's a good idea, is an entirely different matter.

I think you're about to find out how slow network and internet connections can be.

Correct, and i've just abandonned my target of 1 second!

I believe that you would actually save development time by learning how to use PHP/MySQL to do your project on the web server.

Yes that's possible. I just started learning PHP and surprised how easy it is to get going.

If you use an API based system, you'll measure API calls in milliseconds, so all API calls should be <1 second.

I am not convinced by your response about using Access - this seems to be going the route of "I have a hammer let's make everything look like a nail"

Trust me, Access has its place in the world but this is not it.

You mentioned performance as an issue. Let's break down what you want to do.

1.  PHP script receives file
[Time C]
2. Save the file
[Time X]
3. Access application polls server every N seconds
[Time N + Y1 : Y is Network latency]
4. Access downloads files
[Time Z]
5. Access processes file
[Time A]
6. Access pushes file back to server
[Time B + Y2: Y is Network latency]

Total Time X + Y1 + Y2 + Z + A + B + C + N

Alternative
1.  PHP script receives file
[Time C]
2. PHP Writes final file
[Time D]
Total Time: C + D

So, in my view there is no argument for using Access in this scenario.

What processing do you need to do on the file? Let's maybe look at that before advising on how to build the first scenario behemoth.

The processing on the file is complex, there are multiple database processes and various dynamic programming sweeps to optimise the output. This of course can be coded on a cloud service, and it's my intention to do so in due course, but meanwhile the logic is being designed and testing in Access.

You're right about the hammer... but it's all i have at the moment!

Then I wouldn't be worrying about speed issues.

You are driving a tank and asking what you need to do to compete with a Ferrari.

My point is - that whatever optimisations you do with your current setup will pale into insignifcance compared to the performance gains of moving everything to the server.