I want to share a problem we have recently encountered.  We have an Access application that is connecting to SQL Server using ODBC just as this article shows.  This application has been working this way for more than two years.  The SQL Server this app’s database is on also hosts a number of other production databases. 

About six months ago, we began to see a problem with our server.  Out of the blue, the server would become very unresponsive.  All applications will have very slow query execution times.  Looking at the resources of the server, the only thing that would stand out is an excessive I/O utilization.  The only “cure” to the problem was to fail the server over (it is a cluster server) to the other node and things would return to normal for a while.  This problem had no pattern and could not be predicated.  After about three months of our own investigation, we had to bring Microsoft into the picture.  They have spent around three months pouring over trace logs and all kinds of information.  They finally pointed out the little Access application I mentioned above.  They showed concern with this application because it was showing extensive “transaction” times on tables.  They told us to investigate this application.  After six months of having our production server go down at least once a week, management decided to move this application to a server by itself to see if the problem on our production server would go away.  We have been running over three weeks now without a problem on our production server. 

We haven’t really dug into the application in question, but I do know this is an Access front end that is linked to SQL tables.  And I know the application’s forms are bound to the tables.  And I know that when you open one of the forms, you will see an “open transaction” to the table.  As long as the form is open, the transaction will remain open.  We at this point suspect this kind of behavior is indicative to Access linked tables and nothing really can be done about it, short of rewriting the application.

I do want to point out the Access application is written in Access 97 (…yes I know).  It is using MDAC 2.8 ODBC to connect to a SQL Server 2000 server. It is connecting using the TCP/IP protocol.  There are about 15 continuous users of the application.

If anyone out there has ever seen or heard of problems like this, I would love to hear from you.