connect to remote back end

titorober23
titorober23 used Ask the Experts™
on
Hi,

I have a database in ms access(front and back end) - we are planning to upsize it and move the back end to sql server.
I need to have remote access to the database, how can i accomplish this

i know you can remotely connect to a sql server, but how do i tell the system, look for the back end in the remote server.
internally i can do an odbc connection, but how will it work remotely

please help

thanks and regards
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Are you talking about connecting access to sql or connecting to sql without access?
Commented:
You'll do best by installing SQL Server Dev Edition ($50) on your box and sorting things out from there. The connection strings from Access are pretty straightforward (server, database, name, pwd, and a couple of other things). From my experience, I'd do the table migration manually, copying the tables over, making them linked tables in Access instead of the original native versions, and working table by table (or, at least, group by group). I've seen people start cursing MS after using the migration tool MS provides. That's not to say that it can't do what it claims - just that our friend Murphy predicts that our systems are never quite simple enough for that tool!

Good luck

Mike
You don't even need Developer Edition; SQL Server Express Edition is a free download and will work fine for this purpose.

Once you get the connect strings and permissions figured out, the main thing you need to know is that Server Name doesn't have to be a text name - when it's hosted on a remote server, it's usually an IP address, like 123.456.123.456.  The default for SQL Server is for port 1433, so you don't need to specify it, but if you need to you can specify a different one in the server name after a comma, like this:

123.456.123.456,1436

Hope this helps,
Armen Stein
Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
Here's a page that lists connection strings, including several used to connect to remote servers:

http://www.connectionstrings.com/sql-server-2005

That's for 2005, but the same site also has listings for other versions as well.

Also, be sure that your SQL Server is setup to handle remote connections (not the SQL Server you'll install on your dev machine, but the SQL Server the FE will ultimately connect to). Most instances of SQL Server are not setup to handle remote connections by default, and you'll have to do some twiddling.

One caveat: If you're going to host this on your own internal networks (and allow outside, external clients to connect to your internal database) this can be very tricky to do and still maintain security. You might consider enlisting the help of a security consultant to set this up for you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial