Go Back   Access World Forums > Apps and Windows > Other Software

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-01-2016, 09:20 AM   #1
GrandMasterTuck
In need of medication
 
Join Date: May 2013
Posts: 129
Thanks: 74
Thanked 10 Times in 7 Posts
GrandMasterTuck is on a distinguished road
The best remote database backend for an Access front end?

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!

GrandMasterTuck is offline   Reply With Quote
Old 08-01-2016, 09:45 AM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 9,620
Thanks: 37
Thanked 3,116 Times in 3,025 Posts
CJ_London is a jewel in the rough CJ_London is a jewel in the rough CJ_London is a jewel in the rough
Re: The best remote database backend for an Access front end?

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.
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
GrandMasterTuck (08-23-2016)
Old 08-01-2016, 12:00 PM   #3
GrandMasterTuck
In need of medication
 
Join Date: May 2013
Posts: 129
Thanks: 74
Thanked 10 Times in 7 Posts
GrandMasterTuck is on a distinguished road
Re: The best remote database backend for an Access front end?

Quote:
Originally Posted by CJ_London View Post
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!

GrandMasterTuck is offline   Reply With Quote
Old 08-01-2016, 02:15 PM   #4
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 9,620
Thanks: 37
Thanked 3,116 Times in 3,025 Posts
CJ_London is a jewel in the rough CJ_London is a jewel in the rough CJ_London is a jewel in the rough
Re: The best remote database backend for an Access front end?

not so far as I am aware

I found this link

http://stackoverflow.com/questions/1...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/4...ugh-web-server
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
GrandMasterTuck (08-23-2016)
Old 08-23-2016, 08:25 AM   #5
GrandMasterTuck
In need of medication
 
Join Date: May 2013
Posts: 129
Thanks: 74
Thanked 10 Times in 7 Posts
GrandMasterTuck is on a distinguished road
Re: The best remote database backend for an Access front end?

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!
GrandMasterTuck is offline   Reply With Quote
Old 08-19-2017, 06:28 AM   #6
NauticalGent
Dazed and Confused
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 1,053
Thanks: 237
Thanked 135 Times in 123 Posts
NauticalGent will become famous soon enough
Re: The best remote database backend for an Access front end?

GMT,

It has been just over a year since you posted last on this subject. How goes it?
__________________
...see, what had happened was...
NauticalGent is offline   Reply With Quote
Old 08-19-2017, 06:46 AM   #7
GrandMasterTuck
In need of medication
 
Join Date: May 2013
Posts: 129
Thanks: 74
Thanked 10 Times in 7 Posts
GrandMasterTuck is on a distinguished road
Re: The best remote database backend for an Access front end?

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.

GrandMasterTuck is offline   Reply With Quote
The Following 4 Users Say Thank You to GrandMasterTuck For This Useful Post:
jdraw (10-04-2017), Lightwave (10-02-2017), NauticalGent (08-19-2017), Uncle Gizmo (08-19-2017)
Old 10-02-2017, 12:01 AM   #8
Lightwave
Ad astra
 
Lightwave's Avatar
 
Join Date: Sep 2004
Location: Edinburgh
Posts: 1,332
Thanks: 102
Thanked 118 Times in 113 Posts
Lightwave will become famous soon enough
Re: The best remote database backend for an Access front end?

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
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by Lightwave; 10-02-2017 at 12:12 AM.
Lightwave is offline   Reply With Quote
The Following User Says Thank You to Lightwave For This Useful Post:
Uncle Gizmo (10-02-2017)
Old 10-02-2017, 10:43 AM   #9
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 9,620
Thanks: 37
Thanked 3,116 Times in 3,025 Posts
CJ_London is a jewel in the rough CJ_London is a jewel in the rough CJ_London is a jewel in the rough
Re: The best remote database backend for an Access front end?

@GrandMaster

Quote:
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?
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 10-04-2017, 05:25 AM   #10
Lightwave
Ad astra
 
Lightwave's Avatar
 
Join Date: Sep 2004
Location: Edinburgh
Posts: 1,332
Thanks: 102
Thanked 118 Times in 113 Posts
Lightwave will become famous soon enough
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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by Lightwave; 10-06-2017 at 06:37 AM.
Lightwave is offline   Reply With Quote
Old 11-19-2017, 09:12 AM   #11
GrandMasterTuck
In need of medication
 
Join Date: May 2013
Posts: 129
Thanks: 74
Thanked 10 Times in 7 Posts
GrandMasterTuck is on a distinguished road
Re: The best remote database backend for an Access front end?

Quote:
Originally Posted by CJ_London View Post
@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.

GrandMasterTuck is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
MS Access Front End and SQL server Backend ? saleemMSMS SQL Server 3 02-24-2010 07:33 PM
Backend database - Front end application linking issue dealwi8me General 1 11-25-2007 07:02 PM
Access Front End/SQL Backend oldozziesubby SQL Server 9 04-07-2007 06:41 PM
Sybase backend Access front end Chimp8471 General 2 01-12-2006 06:11 AM
access as backend and swishmax as front end flixx General 2 10-08-2005 06:42 AM




All times are GMT -8. The time now is 04:55 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World