Connect to data on a remote computer

[amwebcom]

What is the best way to connect to and send data back and forth to a remote
computer? I want to store my tables in SQL Server on a computer that is not
part of the network. Our inventory data will be in SQL Server, our customers
have an MS Access 2007 front-end as their user interface. I want them to send
their orders to use and display our inventory data to them by connecting
their local Access interface to our SQL Server tables. We could also store
the data in Access tables on our server if necessary.

 

[Tom van Stiphout]

On Thu, 1 May 2008 09:10:07 -0700, amwebcom

You would expose your SQL Server to the internet, just like many ISPs
do. Then you can build a connection to it over TCP/IP and even link
the SQL Server tables to your Access database (not that I am in favor
of that for Production systems; the Internet is not stable enough for
Access).

-Tom.

 

[amwebcom]

Thank you Tom. I thought the ideal solution was building a true web enabled
site with ASP pages linked to the server, but my boss vetoed the idea. He
created the Access database his customers use himself and is very proud of
it. Also, he also doesn't want to confuse his customers by changing the
interface they are accustomed to seeing. So he wants to keep Access as the
front end, but doesn't object to migrating the data to SQL server. So I am
stuck with Access for the moment.

I tried using ADO within Access to create a connection string to the remote
SQL server as the solution. You have clarified my concern about security and
stability. However, I may not have any choice.

I created a connection string in Access and could connect to another
computer on the network, but can't connect from outside of the network. I
created a static IP address for the SQL computer and used "Provider =
'sqloledb';Data Source=192.162.00.00, 1433; Network Library=DBMSSOCN; Initial
Catalog=DatabaseName; User ID=OurDomain\MyUserName;Password=MyPassword;" I
have also tried dozens of variations on that string. It either tells me the
server doesn't exist or I don't have rights.

I have checked from the SQL end and seem to have my users set up correctly,
used Surface Area Configuration to allow remote access, and opened the 1433
portal, but no luck. I must be doing something wrong, but can't figure out
what. I thought that maybe because I have Vista as the OS on my laptop and
was trying to make the connection to his server, that Vista was the problem.


You said "the Internet is not stable enough for Access." Can you explain
what you mean with a little more detail. Is there a better way to store the
data in a centralized location and still let his customers use the Access
front end? I am getting desperate and would really appreciate any help I can
get.

Andrea

 

[Tom van Stiphout]

On Mon, 5 May 2008 17:14:01 -0700, amwebcom

That 192 IP address is most likely not a remote one.

For connection strings go to connectionstrings.com

Access is very sensitive to momentary hickups in network continuity.
Such hickups are fairly common on the internet.
Some people have reported good success with an ADP connected to a
remote sqlserver. I would be reluctant to go there because ADP seems
not actively supported/promoted by MSFT anymore.

-Tom.