odbc sql server driver communication link failure

I have an Access 2003 front-end with SQL 2005 back-end. Occasionally, users get the error below and th开发者_运维知识库e front-end crashes. Any pointers as to how I can resolve this?

[ODBC SQL Server Driver] Communication link failure

---

From the googling and reading I have done on this topic, it seems to me that this might simply be a bug in MS Access that they've never bothered to fix, ie: there is no resolution.

The symptoms I am seeing and that others seem to have is that the connection Access has to SQL Server becomes "bad", and once it reaches this state, nothing will fix it except restarting Access, period. Although it blows a person's mind, this is entirely possible - if the code related to connection management (either in MS Access itself, or within the ODBC provider) doesn't check validity of the connection state and always assumes it is fine, then we would see exactly the symptoms we are seeing.

You'd think, surely Microsoft would fix this, but it wouldn't be the first time.

Update

I'm seeing the same behavior when using a Microsoft Access Project (*.adp) so the problem appears to be within MS Access itself (as opposed to just ODBC).

---

I've seen this behavior with deadlocking transactions. Here's an example of how that might happen:

BeginTrans

'Insert/Update/Delete record in table Employees:
CurrentDb.Execute "DELETE * FROM Employees WHERE EmpID=1"

'Attempt to read from Employees table before committing; Access hangs
Set rs = CurrentDb.OpenRecordset("Employees")

CommitTrans

The workaround is to either:

• read what you need to from the employees table before committing or
• commit your insert/update/delete before trying to read from the employees table.

---

Agree with the above comments the problems seems to be that the client loses cnectivity to the server. if you are using a hostname for the server I would reccomend to use an IP cos if it is purely a dns problem that will solve it if that dosent work then it is a problem on the network like package losses or server overload problems or something in that direction

---

Had the same error:

OBSERVATIONS: - set up an access database linked to MSSQL tables at work - from home, over VPN, could not open any tables - got this error every time - from DSN, did a test connection and it tested okay - pinged the MSSQL server host name used in DSN and it pinged fine - However, when i replaced the server host name in the DSN with the IP and restarted access, everything worked fine.

CONCLUSION: - I'm not sure if it was using the IP address instead or the changing DSN settings and saving that flipped a switch somewhere but it worked... hopefully I won't have to repeat often... we shall see...

HTH