Theo Fitchner
asked on
Can I Use Microsoft Access to Connect to an MS SQL Backend on a webhost?
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?
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?
The only way around that is to host the web server yourself. That may not be an option for you but worth considering.
ET
ET
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks you guys. You were both very helpful. I didn't know Azure could accept that whole range.
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.