ADP remote connection issue

[fred]

I have an ADP database linking tables to a named instance of SQL Express 2008
(ie not the default instance) on a Vista PC. Everything works great from the
Vista PC regardless of where I move the FE on the host PC. When opening the
ADP from a XP PC on the LAN, I get an error that the SQL database does not
exist or access is denied. I get the same error when copying the adp FE to
the XP PC. Remote connections are on, port 1433 is open in the firewalls, and
native client 10.0 is instaled on both PC's. I created a new user on the
Vista PC and tried opening the adp and received the same results! I must be
missing something obvious. Can you please assist me? Thanks, Fred.

 

[Sylvain Lafontaine]

If it's not the default instance, it won't use the port 1433. The port 1434
must be opened and the SQL-Server Browser service must be running in order
to inform the client on which port the named instance is listening. Even in
this case, it's still possible that a firewall might be blocking the
connection.

Also, instead of relying on the browser service, you can also check in the
configuration on which port the named instance is listening and use it
directly in the connection by adding it after the name/address of the server
(separated by a comma in the case of ADP if I remember correctly).

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[fred]

How do I find which port is listening by the named instance? I could modify
the adp connection string if I had the port number, correct? Otherwise, in
sql configuaration manager, I change tcp to port 1434, then open 1434 in
firewall, and ensure sql browser service is running, right? Thank you so
much, Fred.

 

[Sylvain Lafontaine]

The port 1434 is only for accessing the SQL-Server Browser service, will be
used to inform the client on which port it must connect for communicating
with the named instance but won't be used for etablishing the connection
itself (to the named instance).

For determining the port, look under SQL Server Configuration Manager | SQL
Server Network Configuration | Protocols for SQL2005 | TCP/IP --> Properties
| Tab "IP Addresses".

Under this table, you'll see the ports used for each IP Address, both for
the static and the dynamic configuration. If the dynamic port is blank, it
won't be used and only the static IP port will be used; if set to 0, it will
be used and reallocated each time the machine is rebooted.

These ports can be set individually or not. Look at the bottom of this
window for IPALL, it will give you the default ports for both the static and
the dynamic ports that will be used globally if a static or dynamic port is
not set individually for each IP address that the instance is listening to.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[fred]

Sylvain,
Thank you so much for the help thus far. I still have one issue. Everything
is working great when I open the adp from a different user account on the
host Vista PC; however, I can't gain access from the XP PC. Curiously, I set
up the connection in the ODBC services console on the XP PC and when I test
the connection to sql, it is sucessfull. When I open the adp project I get
the same failure message. When I hover over the sql linked table in the adp,
the connection string is the same as on the Vista PC. I have done this with
the firewalls off, as well as with ports open. Any ideas? Just FYI, I only
run SQL2005 Express on the XP and am not sure if there are issues that may be
contributing to the current problem. Thanks, Fred.

 

[Guest]

I have an ADP database linking tables to a named instance of SQL Express
2008
(ie not the default instance) on a Vista PC. Everything works great from
the
Vista PC regardless of where I move the FE on the host PC. When opening
the
ADP from a XP PC on the LAN, I get an error that the SQL database does not
exist or access is denied. I get the same error when copying the adp FE to
the XP PC. Remote connections are on, port 1433 is open in the firewalls,
and
native client 10.0 is instaled on both PC's. I created a new user on the
Vista PC and tried opening the adp and received the same results! I must
be
missing something obvious. Can you please assist me? Thanks, Fred.

 

[Sylvain Lafontaine]

Not sure to really understand your problem here, as there are no linked
tables in an ADP project and ADP project don't use ODBC either; so checking
the ODBC services console should be of no use here. If you try to use a DSN
name (created in the ODBC services console) in place of a server name (or
server address) in the connection dialog box of an ADP project, this won't
work at all.

If you are using a MDB or an ACCDB database file with ODBC Linked Tables,
you should refreshing the links using the Linked Tables Manager or better,
delete these links and recreate them.

Could you clarify what you are using exactly?

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[fred]

I created a dsn and connected so I assumed the adp would be able to connect
to sql on the other pc but I still get 'ODBC sql server driver conncetion
open' error.

 

[Sylvain Lafontaine]

Like I said before, ADP don't use ODBC; so they don't use DSN either.
Setting up a DSN connection in the ODBC connection panel is of no use to
ADP.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Merlinus]

Check out Linglom's blog - it saved my sanity ;-
http://www.linglom.com/2009/03/28/enable-remote-connection-on-sql-server-2008-express/

 

[fred]

Thank You!!!! After following the instructions in the link, I simply opened
the adp on the XP machine, got an error initially, chose to setup a user DSN
for sql server since that was an option after the link table utility failed,
supplied some info and the port number (1434), clicked OK and watched the
application work perfectly. Again, thank you very, very much. It was really
important to get this working. I am migrating my hardware databases to sql
using the SSMA and really wanted to use access adp's so I can use all the
forms and reports that have been developed over the years, in other words, my
life just got a little better! Fred.