Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2006
    Posts
    3

    Question Unanswered: Connection fails in linking SQL Server 2005 tables into Access using DAO 3.51

    Hi,

    I have an app that creates and attaches tables from SQL Server into an Access mdb using an ODBC link from VB6 using DAO 3.51 on Windows 2000. Recently I upgraded to SQL Server Express edition and tested it. It worked fine against the Express Edition. So, I installed SQL Server 2005 Standard Edition on our Server hoping that they operate identically. How wrong one can be! the same code that worked against Express Edition does not work on Standard Edition.

    It is strange that the App works for about 5 seconds which attaches about 38 tables and after that the connection drops and I have a hell of a time to get the rest of the tables linked. The error that comes up is:

    Connection failed:
    SQLState: '01000'
    SQL Server Error: 10060
    [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (connect()).
    Connection failed:
    SQLState: '08001'
    SQL Server Error: 11
    [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check your network documentation.

    The PC that I am using to connect is in a different domain to the Server where the SQL Server database resides. I thought that domains might be the reason. But even joining the PC to the same domain as the server still does not work and behaves the same. So I do not think it is a domain related issue.

    I have also looked at the SQL Server and database configurtaions. I cannot see anywhere any specific time out of around 5 seconds. Even the time out through the SQL Server ODBC driver is set to 30 seconds by default as far as I know. So, it looks like there is a time out somewhere but I am absolutely confused as to where this 5 second time out is comming from.

    I also thought permissions on the database or tables might be the issue but after looking at the database and table permissions, they all look OK. The user has prettymuch the same access as the SA on the database.

    Has anybody came across this problem? If so, I appreciate if someboday can give me a hint as to where I can start.

    Thanks,

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    welcome to the forum

    Using VB6 to programmatically create linked tables between Access and SQL Server 2005 using DAO 3.51 seems a slightly odd way to go. I presume there is good reason?

    Anyway - can you manually link the tables?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Aug 2006
    Posts
    3

    Question Connection fails in linking SQL Server 2005 tables into Access using DAO 3.51

    Quote Originally Posted by pootle flump
    Hi

    welcome to the forum

    Using VB6 to programmatically create linked tables between Access and SQL Server 2005 using DAO 3.51 seems a slightly odd way to go. I presume there is good reason?

    Anyway - can you manually link the tables?
    Hi,

    Thank you for your reply.

    Our application is a reasonably complex one. So I will not bore you with the details but if you want I can fill you in outside of this forum. Rest assured that there is a good reason for doing this.

    Anyway, The tables link properly if I just open an Access MDB, use the same DSN and select all the tables. However, when they are linked from within our App, they fail. Any ideas?

  4. #4
    Join Date
    Aug 2006
    Posts
    3

    Thumbs up Connection fails in linking SQL Server 2005 tables into Access using DAO 3.51

    Hi,

    Finally worked out what was causing this problem.

    After looking around the application event logs on the server, I noticed that ISA server was not letting the application to connect. It was saying that the connections for my PC have exceeded the maximum allowed.

    I looked at the ISA server and as I expected, there was a maximum of 40 for TCP connections. That is why the application was managing to connect 38 tables (we had two other connections within the app to the SQL Server Database). Once I increased the limit for my PC's IP address, everything went back to normal. So it was not a timeout issue.

    They have set this limit to prevents attacks and I can see it working if people use fixed IP addresses. But if DHCP is used, the only other option is to increase the limit globally which goes against the initial gaol of having the limit in the first place.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •