Connection string problems

[J E Jensen]

Hello NG

In a rather old database we have made a function to create a link between
the database and a database stored on a SQL server.

The the function hangs if i am using a server with multiple instances of sql
servers.

ODBC;DRIVER={SQL
Server};SERVER=SqlServerName;DATABASE=MyDatabase;UID=sa;PWD=wrong;APP=The
Application (Jet Link)

The SqlServerName can't use the format SERVER\INSTANCE the connection is
made by using:
Dim dbSrc As Database
Set dbSrc = OpenDatabase("", dbDriverCompleteRequired, False, Cn)

Can anyone give me a hint to solve this?

Kind regards

J Jensen

 

[Sylvain Lafontaine]

Strange that something like SERVER=SqlServerName\Instance doesn't work. One
possibility would be that SqlServerName is an Alias; if not, then creating
an Alias should solve the problem. Look under the various configuration
tools of SQL-Server to create such an Alias on the client side. You don't
say what version of SQL-Server you are using, so I cannot be more specific.

Using an ODBC Provider more recent than the current one that you are using -
such as the Native ODBC Provider for SQL-Server - might also help. Search
the web for "Connection String" to know how to change the provided.

You might also be hit by a firewall problem as using an instance will change
the used port for the TCP/IP connection.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[J E Jensen]

Hello Sylvain

Thanks for the reply.

The server is SQL Server 2005.
If i use the Data Link Properties dialog an fill out the informations every
thing works ok. So my Client can see the SQL server just fine.

Kind regards

J Jensen