We are in the process of converting a VB6 application from
an Access 97 to Access 2000 database. The application
uses Access to attach 40-50 SQL Server 6.5 tables (done in
groups of 10 or so) to synchronize data down to the local
database.

We have had no problems on the existing code, however on
the Access 2000/SQL Server 6.5 combination we are getting
random errors. Most come in two forms, the first is
simply a SQL Server Logon box that is presented to the
user. In the App log on the server it shows an error
17832 - Unable to read login packet, every time this error
is generated. We also receive error 17809 - "Unable to
connect. The maximum number of 'xx' configured
connections are already connected..." These errors only
manifest themselves when 3+ users are connected and
running queries against the database concurrently.

None of the existing synchronization code was changed and
if we change the reference back to DAO 3.51 (from DAO 3.6)
and change back to the Access 97 database, everything
works fine. Further, the queries run much faster under
Access 97. Most of the failure points are on queries that
are using outer joins on tables with 10000+ records.

Has something changed in Access 2000 with regard to
attached tables? Does it create and delete a new
connection each time?

We do have connection pooling enabled and have tried both
named pipes and TCP/IP.

Any help would be greatly appreciated.

Thanks, Darin

>1. What is the Service Pack of SQL Server 6.5?

We are using service pack 5a.


>2. What do you mean by "The application uses Access to
attach 40-50 SQL
>Server 6.5 tables"? Do you mean that there is an .mdb
file with linked
>tables connecting to SQL Server 6.5? Or you mean that it
is a .adp file?

We are using an mdb with linked tables. The application
opens a connection to SQL Server gets some preliminary
information (not using a link, but using DAO). We then
open the Access database, link the SQL Server tables (5-10
at a time) and run queries to determine which rows have
been deleted, inserted, etc and make the appropriate
changes on the Access database. Those links are then
removed, and the next 5-10 tables are linked and the
process repeats until all 50 tables have been updated.


>3. When does the problem occur? Retrieving data or
connect to SQL Server?

Most often we are seeing the error during the execution of
the query, although the error shows up in the app log
as "17832: Unable to read login packet".


>4. You wrote "we are getting random errors", Do you mean
this problem
>occurs randomly? Did the application ever work fine with
Access2000 and SQL
>Server 6.5?
Sorry, I should have been more clear. I meant the errors
are occurring randomly. We had no problems with the
application during our developer test, nor can we get the
problem to fail when few people are synching
concurrently. However when it failed in QA test, and
realized the concurrency issue, we are now able to
duplicate those issues in the development environment.

As a side note (not sure if I mentioned it in my original
message) the queries seem to run much longer under the
Access 2000/SQL Server 6.5 combo, then Access 97/SQL
Server 6.5.

Thanks for all of your help.
Darin
>-----Original Message-----
>Hi Darin,
>
>Considering the complex of this issue, I would like you
to provide more
>information so that I can narrow down this issue.
>1. What is the Service Pack of SQL Server 6.5?
>2. What do you mean by "The application uses Access to
attach 40-50 SQL
>Server 6.5 tables"? Do you mean that there is an .mdb
file with linked
>tables connecting to SQL Server 6.5? Or you mean that it
is a .adp file?
>Can you describe it in detail? How does the application
work with Access
>database and SQL Server?
>3. When does the problem occur? Retrieving data or
connect to SQL Server
>6.5 in the VB6 application?
>4. You wrote "we are getting random errors", Do you mean
this problem
>occurs randomly? Did the application ever work fine with
Access2000 and SQL
>Server 6.5?
>
>I am standing by for your response.
>
>Regards,
>
>Michael Shao
>Microsoft Online Partner Support
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>
>.
>

Hi Darin,

Thanks for your feedback. According to my understanding, the application
can work fine with SQL Server 6.5 and Access 97. There are errors with SQL
Server 6.5 and Access2000. The errors recorded in the log do not caused the
application crash. They are just recorded in the app log. The errors always
occur when there are many query requests. If I have misunderstood, please
feel free to let me know.

1. You wrote "However when it failed in QA test, and realized the
concurrency issue, we are now able to duplicate those issues in the
development environment."
a. Do you mean that you have executed the problem query (The query caused
the error in log) using Query Analyzer? Does the same error occur? Or the
problem only occurs when there are many query requests.

b. It seems that you can reproduce this problem in the development
environment. What are the detailed steps to reproduce this problem? Did you
reproduce the error only when there are many query requests?

2. I would like you to try to implement these problem queries in Access
2000. Does any error occur? Does the error only occur when there are many
requests?

Thanks for using MSDN newsgroup.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.