Unanswered: Connection fails in linking SQL Server 2005 tables into Access using DAO 3.51
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:
SQL Server Error: 10060
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (connect()).
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.
Connection fails in linking SQL Server 2005 tables into Access using DAO 3.51
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.