Very Computer

I have a problem in a VB6 application that has been patched for many
years and is now used towards a SQL-Server database.
It uses a local Access97 (DAO3.51) database for its "daily operations"
but the real data are on SQLServer. In order to process normally we
have made table connects using ODBC so that we can run DAO queries
with joins between the local and remote tables. This usually has been
working OK, but now we see occational ODBC failures for some
operations. When the app starts up it connects the SQL tables into the
Access97 database using an ODBC string that defines the datasource
name, user login, server name, network library to use, switches off
windows authentication etc.
From then on many operations are done using these tables. But then
later in one and the same procedure where we make several calls using
joins with both local and foreign tables then - poohh! - we get a ODBC
connection error. Always at the same finishing query...
Now if we go into the Control panel and open the data source and walk
through it and set the server name, authentication (SQL) and network
configuration (TCPIP), then go back and run the same operation in the
program (which has not been closed down) - it works OK!

Question:
Why is the ODBC connection changing its properties so that the
connection fails suddenly when it did not from the start? It looks
like it is not using the connect string we defined when we connected
the tables.
We override the servername, authentication and network library in our
ODBC string just to make sure it will work, and it does at the
beginning, but does not last all through....
But if the system data source we use is manually changed to reflect
the settings we use then it works. Problem is that we cannot do this
because the user is not foreseen to be an administrator.

Any tips at all?

/Bo

If you feel comfortable, run odbc trace to find out why connection string
is changed. If you have a good repro, consider opening a case with PSS
Access support on the issue.

HTH

This posting is provided "AS IS" with no warranties, and confers no rights.

Regards,

Kun Cheng
Microsoft Support Engineer
--------------------

| Subject: ODBC connection fails intermittently
| Date: Tue, 15 Apr 2003 23:46:49 +0200


| X-Newsreader: Forte Free Agent 1.92/32.572
| MIME-Version: 1.0
| Content-Type: text/plain; charset=us-ascii
| Content-Transfer-Encoding: 7bit
| Newsgroups: microsoft.public.sqlserver.programming
| NNTP-Posting-Host: h5n2fls33o827.telia.com 213.66.182.5
| Lines: 1        
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.programming:350906
| X-Tomcat-NG: microsoft.public.sqlserver.programming
|
| I have a problem in a VB6 application that has been patched for many
| years and is now used towards a SQL-Server database.
| It uses a local Access97 (DAO3.51) database for its "daily operations"
| but the real data are on SQLServer. In order to process normally we
| have made table connects using ODBC so that we can run DAO queries
| with joins between the local and remote tables. This usually has been
| working OK, but now we see occational ODBC failures for some
| operations. When the app starts up it connects the SQL tables into the
| Access97 database using an ODBC string that defines the datasource
| name, user login, server name, network library to use, switches off
| windows authentication etc.
| From then on many operations are done using these tables. But then
| later in one and the same procedure where we make several calls using
| joins with both local and foreign tables then - poohh! - we get a ODBC
| connection error. Always at the same finishing query...
| Now if we go into the Control panel and open the data source and walk
| through it and set the server name, authentication (SQL) and network
| configuration (TCPIP), then go back and run the same operation in the
| program (which has not been closed down) - it works OK!
|
| Question:
| Why is the ODBC connection changing its properties so that the
| connection fails suddenly when it did not from the start? It looks
| like it is not using the connect string we defined when we connected
| the tables.
| We override the servername, authentication and network library in our
| ODBC string just to make sure it will work, and it does at the
| beginning, but does not last all through....
| But if the system data source we use is manually changed to reflect
| the settings we use then it works. Problem is that we cannot do this
| because the user is not foreseen to be an administrator.
|
| Any tips at all?
|
| /Bo
|