Link Access Database to Server Backend

Question

I am having an Access Database, splitted in Front-End an Back-End (for a client, I just wrote ODBC PHP functions to run it over the internet). Now they want to put the backend on their virtual server (you can access C:\ over it, so a real server) to use it as backend for all their different front ends on various computers. How do I achieve this?

I only saq SQL Server in Access but I want to get sure what I need in order not to screw something up :)

Answer 1

You could consider Access web services. This would mean that you publish your database to SharePoint or now office 365.

That published data becomes useable via a web service and as such would work well in disconnected mode that is typical required of a smartphone applications or applications that run over the internet.

It also means you can use the application over very poor connections. In fact you can pull the network plug and your Access application will continue to run.

So for these published Access applications the Access desktop/client application can also link to this published data (and application). The big bonus part is now the application functions in disconnected mode.

So if you lose your connection then the application continues to run on the desktop clients.

And with the new web publishing feature then you can build and run forms from Access that run on any standard browser:

Here is a video of me running an Access application and I switch to running this application in a browser at the half way point:

http://www.youtube.com/watch?v=AU4mH0jPntI

(no ActiveX or Silverlight is required – you can even use these Access forms in your smartphone's web browser or on an iPad).

And here is another video showing how one can export "relational" data tables up to office 365 using Access.

http://www.youtube.com/watch?v=3wdjYIby_b0&fmt=22&hd=1

Note that because these published Applications run on a cloud OS, you not limited by the Access database engine and you are NOT using the Access data engine for the web published data. This means the user limit really is only that of the massive cloud servers, and they of course can handle millions of users. So Access Web services on office 365 would also be a possible solution here. You can now scale out to millions of users and your only user limit is those "massive" cloud servers – and they are really big!

Note that your Access web forms become .net XAML (zammel) forms, and reports are converted into RDL and use SQL server reporting services.

In your case you mention you can link to the back end since as you note the "drive" is exposed over the internet connection via VPN. Such a setup may work, but it will not work well and will be vulnerable to corruptions

I explain here why the connection can corrupt the data file:

http://www.kallal.ca/Wan/Wans.html

Answer 2

The way I see it you have two options, but only one should be really considered. Access does not support (as far as I know) connections over TCP/IP/other protocols like "proper" databases. It supports file system access which means that you must have a secured connection presenting the access database on a network drive or alike.

Option 1: Using a secure connection, define for each client a network drive mapping to your virtual server drive containing the access database. This will basically create a very similar environment to the one you have today. Please note that this is not considered best-practice and on some systems won't be achievable due to security/NFS/etc... reasons.

Option 2: I'm afraid you will need to rewrite your front/back-end to a more robust database, any will do (SQL Server, mySQL, etc...) I suggest you look into SQL Server Express, I'm pretty sure it will give you what you need without any licensing costs. The SQL Server Management Studio includes an option to import your data from Access (or you can export it from within Access), you'll need to tweak the Data Types, etc... but it's not the end of the world. From that point on, you will have a database instance that can be accessed over the network without any issues. At this point, you can choose to keep you Access front-end as is and use ODBC connections to the SQL instead of the previous local Access copy. I suggest you take the opportunity to drop Access altogether :)

This is a painful process (been there a few times), but a critical one that boosts your application to a new level with a complete new set of capabilities.

I'm here if you need any further assistance in the process. Good luck!