Access over Internet

[spikepl]

A small business wishes to have a multi-user order processing system developed (2-4 users in total). One requirement is to be able to access the system from home or elsewhere.

Initially access via Remote Desktop or some remote-support software like gotoAssist, TeamViewer or perhaps some flavour of VNC can prove adequate. Or not (feel free to comment). The draw-back is that the office server/computer has to remain ON, and having been hit by a power outage once or twice they are keen on something more reliable.

I am thinking about developing the system locally, split FE/BE, and then perhaps migrating to MS SQL 2008 R2, located at some ISP, if that proves necessary. If no data is kept locally, then the users could connect to the BE either from office or form their laptops elsewhere.

I need to verify my understading of this.

The migration wizard can help migrate, and I expect some things will need to be sorted out manually. With no further changes, will the queries run locally, i.e. dragging all the data over the net, or will they run on the MS SQL DB-engine? If so, then presumably some Views could be created so that only the query results get passed via the net? Or Pass-through queries? What would be a sensible tactic here?

I am trying to ascertain what changes from a locally based solution to an ISP-based backend would be needed so as to preserve some semblance of response speed, or else this effort would be pointless (and perhaps a client-based solution should be considered, but that worry is for later).

Any comments/pointers? I haven't found any good article having an overview of these aspects, other than "MS SQL is much better backend".

 

[Galaxiom]

Generally Internet web servers don't provide direct connections to their databases. That would be too much of a security nightmare. Instead the server hosts asp or php webpages which connect to the database while sending and receiving data to and from html forms on the pages.

Basically you need to build a web application. Unfortunately going from Access to this is a near vertical learning curve.

 

[spikepl]

I am not scared of web applications - I have built some ASP.NET stuff before.

The problem is elsewhere - their workflow is based on emails, and strong integration with the system can be accomplished relatively simply at the frontend using Outlook Automation. To recreate this functionality at the backend would increase the development effort significantly.

But yes - I forgot that direct access to an ISP-based MS SQL server might not be easy to obtain.

Let's assume for a moment that I then install the free Express-version locally (and skip the power-outage concerns).

For the over-the-internet users, I still have the questions as above. And that then leads to the next question: If I make a frontend that connects to an external database (in this case hosted at the business concerned) and uses pass-through queries so as to use the local database's engine and minimise network traffic? And can one do that using a local a MS ACESS database as backend?
.

 

[Galaxiom]

The problem with using an Access backend is that it is just a file. The processing is done by the engine on the Front End machine. Consequently interruptions as can be encountered across the Internet are intolerable.

Just a thought but what about using a hybrid application? Use Office as the user interface on the front end but communicate with the backend ASP interface using hidden HTML forms.

 

[spikepl]

The problem with using an Access backend is that it is just a file. The processing is done by the engine on the Front End machine. Consequently interruptions as can be encountered across the Internet are intolerable.
.

I don't quite get it. I once made an ASP.NET application based on an Access.MDB that ran just fine. If I can do this at an ISP, and on my own machine, why cannot I obtain the same result from another Access-MDB? Eg, running a Pass-though query to an open Access MDB on some machine or other (i.e. not server) ? I understand that Acess is just a dumb file and not a server as such, and the processing is done by an installed component of the Office, but if the app is running?

As to "hidden forms" then I'd probably be better off making some Winforms-based client - but then again the question of hosting the db...

Gotta think

 

[vbaInet]

Sharepoint might be a good solution considering your requirements. You can cache data locally and allow users to work offline. Then when they're back in the office they can synch the data.

And the users can go live if they wish too.

 

[spikepl]

vbaInet - haven't thought of that ... I guess a Sharepoint solution could get hosted at Office365? Haven't looked into that yet. Any views on that?

The amount of money for development is limited, but they are willing to spend to integrate their entire workflow wiht mails and db, since they cannot their needs covered by any COTS..

 

[Galaxiom]

I don't quite get it. I once made an ASP.NET application based on an Access.MDB that ran just fine. If I can do this at an ISP, and on my own machine, why cannot I obtain the same result from another Access-MDB?

The ASP is serving the webpages and receiving data from the forms. This process uses protocols that are delay tolerant. The server is updating the mdb locally (or on an Ethernet) so is not subjected to the same potential for interruptions.

If your front end is connecting to an Access backend across the internet the queries stand the chance of failing part way through. This can easily corrupt the backend.

Eg, running a Pass-though query to an open Access MDB on some machine or other (i.e. not server) ? I understand that Acess is just a dumb file and not a server as such, and the processing is done by an installed component of the Office, but if the app is running?

I am certainly no expert on this and we are sliding into the highly technical mysteries of the JET/ACE engines themselves. One of our power posters like Banana or Leigh Purvis could probably give you a much more authoritive answer. Here is my understanding anyway.

When you pass the query to another mdb database it is still actually being processed on the local engine. Each engine is interleaving its requests to the database file with the engines on other machines. The engines each lock the page of data they are working on, update it in the file then release the lock. Each engine is free to lock any available page allowing multiple simultaneous updates without conflict so long as they are on a different page.

Real pass through queries (and Connections) to SQL Server queue the queries for processing by the one server engine. The processing is entirely local to the data.

As to "hidden forms" then I'd probably be better off making some Winforms-based client - but then again the question of hosting the db...

Gotta think

Remote use of Access is a conumdrum for sure. I do wonder how a hidden browser window churning data off to a server would appear to a PC's security system. Gotta look sus. As I said it was just a thought.

 

[vbaInet]

vbaInet - haven't thought of that ... I guess a Sharepoint solution could get hosted at Office365? Haven't looked into that yet. Any views on that?

The amount of money for development is limited, but they are willing to spend to integrate their entire workflow wiht mails and db, since they cannot their needs covered by any COTS..

I'm not a Sharepoint man but I've seen it done. It should be easy enough to set up too.

As per costs, yes Office365 could be a good option, but that is if they need the extras.

 

[spikepl]

I'm not a Sharepoint man but I've seen it done. It should be easy enough to set up too.

Exactement! I have heard etc etc, but have difficulties advising on stuff I don't know much about.

Conclusion? Scour the web for info.

If anyone has any links to a good intro to sharepoint - not how to make it work but, what to do in the context of a small business until now not running any own servers, and how to marry that and perhaps Office365 then please come forward.

 

[Simon_MT]

You could also look at a Terminal Server solution.

Simon