Solved

Can I Use Microsoft Access to Connect to an MS SQL Backend on a webhost?

Posted on 2016-10-26
5
119 Views
Last Modified: 2016-10-31
I have a Microsoft Access desktop frontend app that is connected to an SQL Server Express backend on the same PC. This works properly with the caveat that every user with the frontend app must connect to the LAN. I would like to move the backend online so that users (employees) will be able to use the frontend from any location; all they'll need is an internet connection. I will also want to create a few web pages that clients can use to access some of the data. The clients won't be writing any data; just reading.

Presently, I have a copy of the backend hosted online at HostGator. However, there is a requirement to whitelist every IP address that wishes to access the database. The problem is the users of the frontend desktop app  will have their respective internet IP addresses assigned via DHCP and so I can't whitelist in advance. In testing, the Access app connects fine when I whitelist but doesn't connect if I use an IP address that isn't whitelisted.  I don't mind whitelisting to be able to use SSMS.

Is what I am trying to do possible with a webhost? If not, what services do you recommend?

PS: I was able to create an ODBC connection on HostGator but the server address is 127.0.0.1    - this is a local host. Is there anyway to use this connection from withing Access?
0
Comment
Question by:Theo Fitchner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 58
ID: 41860635
You can do what you want, but performance will not be great.

Access apps are typically not developed with true client/server in mind.

The main issue you have at the moment is your hosting...you'll need to find a hosting company that will let you control the setup.   If HostGator didn't require the whitelisting, you'd be good to go.

Jim.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41860947
The only way around that is to host the web server yourself.  That may not be an option for you but worth considering.

ET
0
 
LVL 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 250 total points
ID: 41861689
At least at Amazon (AWS) - haven't checked at Azure - you can assign access from Anywhere to your public IP address at AWS:
AWS/gustav
0
 
LVL 9

Accepted Solution

by:
Armen Stein - Microsoft Access MVP since 2006 earned 250 total points
ID: 41863315
Yes, lots of web hosting services don't require specific IP whitelisting.  Find one that will host SQL Server without it.  Even SQL Azure can be run wide open by using a range of 0.0.0.0 to 255.255.255.255.

I agree with Jim that you might not like the performance.  Access needs to be optimized for using SQL Server, and even more so when it's over the internet.  I wrote a slide deck about that - it's called Best of Both Worlds at our free J Street Downloads Page:

http://www.JStreetTech.com/downloads

It includes some thoughts on when to use SQL Server, performance and security considerations, concurrency approaches, and techniques to help everything run smoothly.

Cheers,
Armen Stein
J Street Technology
0
 
LVL 1

Author Closing Comment

by:Theo Fitchner
ID: 41867729
Thanks you guys. You were both very helpful. I didn't know Azure could accept that whole range.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article was originally published on Monitis Blog, you can check it here . Today it’s fairly well known that high-performing websites and applications bring in more visitors, higher SEO, and ultimately more sales. By the same token, downtime…
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
The viewer will learn how to dynamically set the form action using jQuery.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question