Advice for Hosting MS Access Database on the Internet

[sbrown106]

Hi Everybody,
I learning MS Access and ive have been asked me to build a database, there will only be a handful of users and maybe a few thousand record entries. The users of the database will be at different locations and their computers are not networked, so Ive been thinking of putting the backend on the internet (maybe Azure?) and the front end at the local PC's as a solution so multiple users can use it. Also, there are word documents files that need to be accessed on local pc's ( word docs downloaded from email) and data pulled into the database, names, addresses etc - I'm not sure how to do this in vba. Ive written something in python that does this when the database and file are on the same pc but dont know if this works if the backend is on the internet. Any advice/guidance on placing the database on the internet and handling word docs over the internet would be great.
Thank you very much for any help. Sorry if Ive placed this question in the wrong Forum!

 

[Ranman256]

Don't.

 

[Pat Hartman]

For small tables, you can get away with SharePoint for the BE. If you have FTP access to the network folder, you can address the folder as if it were local so Access can automate Word and Excel documents.

I have not had success using Azure as a BE. Probably because I tested with a third party service and couldn't configure it to optimize it to work with Access. It was like watching paint dry.

 

[sbrown106]

For small tables, you can get away with SharePoint for the BE. If you have FTP access to the network folder, you can address the folder as if it were local so Access can automate Word and Excel documents.

I have not had success using Azure as a BE. Probably because I tested with a third party service and couldn't configure it to optimize it to work with Access. It was like watching paint dry.

Thanks Pat, what would you class as small tables? Sharepoint would be the easiest option at the moment, is there a db size in which I should start considering other options?

 

[theDBguy]

Hi. Just FYI, SharePoint is not a real database system. I would say anything less than 5k records in a List would qualify as a "small" table. You should be able to use Azure SQL as the BE with Access, in each Desktop, as an FE.

 

[Pat Hartman]

@theDBguy,
Have you gotten Azure to work this way? I've tried and it was too slow to be viable. Did you use your own Azure installation or one hosted by a third party?

 

[theDBguy]

@theDBguy,
Have you gotten Azure to work this way? I've tried and it was too slow to be viable. Did you use your own Azure installation or one hosted by a third party?

Yes, I have used Azure before with an Access FE for one client, and it worked okay, without too much problems. It was hosted at Microsoft Azure.

Azure SQL | Microsoft Azure

 

[Pat Hartman]

Do you have any idea what the specs were? I sure couldn't get it to work on any of the low cost options.

 

[NauticalGent]

Yes, I have used Azure before with an Access FE for one client, and it worked okay, without too much problems. It was hosted at Microsoft Azure.

Azure SQL | Microsoft Azure

DBG, with this setup, where did the FE actually reside?

 

[Minty]

We use Microsoft Hosted Azure as a BE to Access FE all the time.
FE hosted on end user's machines.

It can get a bit slow if the Server location is a very long way away from the end-user.
( We have a case where the Host is based in the SW UK and the end-user is in India.
On a dreadful connection.
With Power issues...)

@Pat Hartman - I'll look up the specs we are using.
We are hosting 10-15 backends on an elastic pool, some large some small.

 

[NauticalGent]

FE hosted on end user's machines.

I assume this is necessary? Would this work with a Thin Client computer - or does Azure not work that way?

 

[CJ_London]

If you are going to invest in something like sql azure also consider using terminal server or Citrix. Benefits are you don’t need much bandwidth, performance is almost as good as having the BE on your local pc and in the event of a service interruption you are unlikely to cause a corruption. Further you don’t have to optimise your queries which you would need to for any remote BE although it is good practice to do so anyway

Other benefits are users can use any device that supports Remote Desktop which you use to connect to the server

you can also store your word docs etc on the server - each user has their own profile so can have their own docs folder

 

[NauticalGent]

So it appears that I am laboring under a false understanding. I thought the VDI WAS Citrix and that moving to Azure would mean no more Citrix.

If I have it right, VDI and Azure (DaaS) are virtual machine technologies and NOT remote user?

 

[Minty]

I assume this is necessary? Would this work with a Thin Client computer - or does Azure not work that way?

It's not necessary but a majority of the smaller businesses we work with have no formal infrastructure and don't want to shell out for a remote client-based solution. It's quite expensive by comparison. £25-£30 per user per month.

 

[theDBguy]

DBG, with this setup, where did the FE actually reside?

On the local machine.

 

[Steve R.]

@sbrown106: As a follow-up to @eliasthomas suggestion, a pseudo cloud base implementation can be achieved by using an opensource database, such as MariaDB in conjunction with Apache. Implementing this approach would require learning a variety of tools, such as (but not limited to): PHP, Python, HTML, JavaScript, and CSS. The learning curve will be very steep at first. In the end it should be very rewarding.

 

[The_Doc_Man]

I will suggest you to consider a cloud database for better scalability & security.

EDITED by The_Doc_Man to correct misuse of quotes and thus make it easier to read. Content was NOT changed; only format was changed.

Using a cloud database usually doesn't work well if you retain the Access Front-End file on the user's PC. Access/cloud = catastrophic protocol compatibility issues.

However, if you use something like RDP (Remote Desktop Protocol) - which DOES work remotely - viable solutions exist.

 

[Minty]

@The_Doc_Man That's a bit of a sweeping generalisation, and should be clarified to mean only if you are trying to use an Access backend.
We use Azure hosted (Cloud) SQL Databases and local Access FE all the time connected with an up-to-date MS ODBC driver and have no issues.

 

[The_Doc_Man]

Thank you, @Minty. I stand corrected. Clouds and native Access back-ends have protocol problems because native back-ends do not use a special driver (such as the ODBC driver). Non-Access back-ends are not affected in the same way.