Can “Access 2007†Access Remote SQL-Server?

[Brad]

Can “Access 2007†Access Remote SQL-Server?

Is it possible to access a Microsoft SQL-Server database that resides in
another firm’s location? Our Website hosting is outsourced. A small number
of SQL-Server tables are used to support this site. We want to access the
data in these tables via Access 2007 at our site.

Just recently we have set up a test system with Access 2007 getting at a
local SQL-Server database via ODBC. This works nicely. Now we would like to
connect to the remote tables that support our Website. They are located in
another state at the hosting firm’s location. We would like to connect over
the internet. There is no VPN.

If this connection is possible, where can I learn about how to set it up?

If getting at these remote SQL-Server tables is not possible with
Access-2007, are there good alternative approaches. Our budget is very
limited.

Thanks in advance for your help.
Brad

 

[Arvin Meyer [MVP]]

It is possible. It will be slow, possibly too slow to be useful. I suggest
using either a Terminal Server connection or building an asp/asp.net
interface.

 

[Brad]

Arvin,

Thanks for the advice.

Could you point me in the right direction to find the details of how to make
this connection so that we can test it?

(How to configure Access 2007 to connect to a remote SQL-Server Database).

I have seen some info on the internet that suggests that this connection is
not possible. I would like to try it for myself.

Thanks,
Brad

 

[mcnewsxp]

Arvin,

Thanks for the advice.

Could you point me in the right direction to find the details of how to make
this connection so that we can test it?

(How to configure Access 2007 to connect to a remote SQL-Server Database)..

I have seen some info on the internet that suggests that this connection is
not possible.  I would like to try it for myself.

Thanks,
Brad

i've done this with VPN and DSN and yes, it is too slow to use for
daily production, but may be ok for your purposes.

 

[Arvin Meyer [MVP]]

Pretty much the same way except that you'd use the IP address of the
machine, and Port 1433 (the usual port that SQL-Server listens on). Just
build a DSN using that. Ping it first to make sure that it's open, since
Admins often close it when there are no outside calls.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Brad]

Arvin,

Thanks for the help. I appreciate it.

Gradually I am getting there!