The best remote database backend for an Access front end?

[GrandMasterTuck]

Hi folks. I have been working on making improvements to my employer's Access database application. It does scheduling, payroll and inventory, and it's got some pretty nice features, not to mention the upgrades I have made. Here is my issue:

I want to get the data repository (it's a split database app with front and back ends, and all the tables are in the back end) off the company server (it's slow, overloaded and has issues too numerous to mention) and onto cloud hosting.

The question is, how do I do this? What sort of back end would you guys recommend for this kind of thing. I have asked this before in one or two of my other posts (troubleshooting connection issues) but never got a good answer, so I'm posing it as a question by itself.

What back end (table data) repository would you recommend for an Access front end? Again, this will be COMPLETELY REMOTE, and NOT on a server I have physical access to. I'm talking CLOUD hosting of our data. I understand this may incur hosting costs, and the company is willing to foot those, but I need to know where to start.

Can I use MySQL? Do I have to use MS SQL? Is there another database type that's commonly found on cloud hosting accounts that I can use? Would I need to install software on the company's PC's to accomplish the connection (this might be a problem.. looking for turnkey solutions).

Thanks so much!

 

[CJ_London]

there are a number of options:

office 365
Linux/MySQL
Azure (web based sql server)
windows/sql server
sharepoint
there are also hosts that will host an access db - see this link for an example http://eqldata.com/ - I do not know the company and am not making a recommendation
You can also consider using citrix or terminal server

no doubt more.

however it is not as simple as relinking your tables to a hosted backend, many hosts will only allow access to a hosted db via a front end hosted on the same server i.e. a web based front end.

 

[GrandMasterTuck]

however it is not as simple as relinking your tables to a hosted backend

This is EXACTLY what I want to do. That's it. I don't need any other service or product. I just need to link to tables that reside on a server that IS NOT PHYSICALLY in my building by any rational definition.

Is there NOBODY that offers this kind of service? Can't I get some web hosting package from some company that offers MS SQL, convert my tables to SQL and upload them, then link to them from my app? There MUST be a way!

 

[CJ_London]

not so far as I am aware

I found this link

http://stackoverflow.com/questions/11962689/options-for-cloud-based-ms-access-backend

note in particular this comment - you'll need find a suitable provider

Yes, I used standard ODBC to the hosted web site SQL server. Not all providers allow such external connections (and in fact fewer and fewer allow this due to security - opening a web site server to the wild internet can be a security issue and hole). It really depends on the provider, but some do allow ODBC to MySQL or SQL server. You have to check on a provider by provider basis. – Albert D. Kallal Dec 10 '15 at 2:53

and another

http://stackoverflow.com/questions/...necting-to-back-end-sql-db-through-web-server

 

[GrandMasterTuck]

Update on this issue... the answer, as folks informed me above, is NO. Out of the box, Access isn't suited to connect to any kind of remote server in the way I was hoping to (at least, not that I'm aware of). Which means I MUST install a new ODBC driver at the very minimum. I chose the Access SQL Native Client v.11, because it was the most frequently suggested in other forums I visited when researching this. I use that to connect to a Microsoft Azure account, and it works perfectly. So... solved, I guess. Thanks to everyone for their suggestions and hints. This community is amazing in so many ways, and has helped me so much as I work on these little projects of mine. You guys rock!

 

[NauticalGent]

GMT,

It has been just over a year since you posted last on this subject. How goes it?

 

[GrandMasterTuck]

Hey NauticalGent, thanks for checking in with me. It's been running pretty smooth, actually, been using the system for the past several months without major issues.

What I ended up doing was getting a SQL server through Amazon Web Services, which had the best pricing structure of all the ones I looked at. My database connects with the standard Microsoft SQL settings that are baked into Access out of the box, so I didn't need to install any other ODBC types, and Amazon allows both IP Range whitelisting and unrestricted connections, so that makes it super easy for the app users to download the front-end and connect to the database. The app itself is very restricted on how it exchanges data with the server, so users don't have a way to directly affect any tables, all data-input-output is handled with query strings concatenated from filtered TempVars that filter out potential injection code prior to final concatenation. Plus, the app front-end is compiled as an ACCDE with Access Hotkeys disabled, so no opening in developer mode. Runtime only. It's nice and secure, and nice and quick.

I've had such good success with it that I'm working on a new inventory tracking system for them, as well, which will handle asset management, stock tracking, reorders, invoices, and statistical sales analysis.

In short, the more I get to know Access and SQL, the more in love I become. Thanks again for all your help in here, this is my absolute go-to for ideas and assistance with these DB projects I take on, and I've made recommendations to others to check the site out if they need help, too. THIS is what the internet is all about.

 

[Lightwave]

Grandmaster

Thanks for letting us know your experiences - I haven't tried what you are doing with production applications (yet) but I've certainly individually linked to remote servers with SQL Azure and MySQL instances to work directly on data through MS Access. I have also linked to Oracle but that was on a LAN.

At the end of the day with improving internet connectivity if it works for you now its probably going to be even better 5 years down the line which suggests its a long term good strategy.

You do sound like you have really thought through the structure of your application which generally is half the battle.

Here's some notes I wrote on linking to MySQL - as previously stated its worth contacting the person or organisation managing the MySQL instance becaust the default of many providers is that they don't allow this kind of connection.

If you set up the instance yourself you obviously write the rules so its not an issue but in any case you will need to know ;
Name of Server
Port number to connect on
User
Password
And the name of the database you are connecting to.

How to link MS Access 2003 to MySQL

M

 

[CJ_London]

@GrandMaster

What I ended up doing was getting a SQL server through Amazon Web Services

I'm about to take a look at that - is the firewall protection the same as for SQL Azure - based on allowed (i.e. the client) IP's?

 

[Lightwave]

MS Access an the cloud

Grandmaster knows whats in this link but for those still to try linking to a third party cloud database (and particularly SQL Azure here's a link to a video) which talks to a MVP who regularly uses SQL Azure as the backend. Like Grandmaster he's throttled the data the UI pulls to optimize speed.

Juan Soto talking about leveraging MS Azure and MS Access together

I thought the AI bit was quite interesting.

 

[GrandMasterTuck]

@GrandMaster

I'm about to take a look at that - is the firewall protection the same as for SQL Azure - based on allowed (i.e. the client) IP's?

Amazon allows for client whitelisting, just as Azure does, which was good for me because I could limit the direct access to the DB using a range of IP Addresses for each of the computers at the shop. Amazon's server management tools are nicely robust, and in fact are far more comprehensive than I need for my relatively simple app, so I'd imagine that, should you be looking for access-restriction controls that I haven't mentioned, there's a really good chance they have what you're looking for. Best part is that you don't have to sign a contract for an extended period of time, you can start out with the 'free' version (which restricts connections and bandwidth pretty harshly) to make sure it's got everything you need, then simply spin up a few extra nodes or whatever they call them to bring the DB into production-level performance.

I'm highly satisfied with Amazon as of the current date. No complaints yet! Good luck! And sorry it took me so long to reply... I go through phases where the boss has me on the floor instead of in the office (especially when staffing is short) so I only get to do programming stuff periodically.

 

[Thales750]

Thanks for posting this guys. ant updates in the last year?

 

[jleach]

I wasn't involved in the initial thread here, but use SQL Database (aka, Azure SQL Server hosting) with Access FEs often. Have done it with AWS as well, and some other lesser-known hosts. No major issues, just be careful of performance when you're writing the FE.

I personally prefer Azure for their database service, but otherwise (for the rest of the cloud stuff), lean towards AWS. In either case though, it's basically six one, half dozen the other. For simple hosted databases, either are perfectly fine. It's when you get into much more integrated scenarios that the variances in adjacent services between AWS and Azure start coming into play.

 

[AccessBlaster]

Would .net work with a sql backend, For a remote app?

 

[jleach]

Would .net work with a sql backend, For a remote app?

Sure, it's very common.