Posted by Bruce on 09/13/07 14:55

On Sep 13, 5:30 am, teddysn...@hotmail.com wrote:
> On Sep 12, 8:37 pm, Hugo Kornelis<h...@perFact.REMOVETHIS.info.INVALID> wrote:
>
> [...]
>
>
>
> > Hi Edward,
>
> > I agree with Bruce and Erland that the problem is very likely happening
> > at the SQL Server end. To get more information, I suggest the following
> > steps:
>
> > 1. Use profiler to catch the commands sent by Access to SQL Server that
> > lead to the timeout.
>
> > 2. Use SQL Server Management Studio (or Query Analyzer, if you're on SQL
> > Server 2000) to execute those same commands.
>
> > SSMS and QA don't timeout, so this will give you the opportunity to find
> > out if the problem is "just" slowness, or some infinite blocking; it
> > will also give you plenty time to investigate potential locking issues.
> > Plus, both SSMS and QA show all error and informational messages, even
> > those that Access likes to suppress.
>
> > If you don't find the reason after this, then post a repro script so
> > that we can reproduce the problem (a repro script consists of CREATE
> > TABLE statements with the table structure, including constraints,
> > indexes, and properties; INSERT statements with sample data; and of
> > course the statements that causes the timeout).
>
> > (followup set to SQL Server groups only)
>
> UPDATE:
>
> I visited the client this morning on a different matter, and took
> another look at the problem, in the hope that it might have gone
> away. No such luck.
>
> Since I had a few minutes to spare, I created a NEW MS Access
> database, linked via the DSN to the database, and created a link to
> JUST the table that was causing the problem. I then created a single
> form which was based on the newly linked table. I opened the form,
> pressed the "New" record button on the Record Navigation group,
> entered a new record, pressed the "Previous" record button and voila!
> the record was saved! I then reverted to the original application and
> it worked! My client was delighted.
>
> I was just in my car getting ready to leave when the client phoned.
> "It's stopped working again". Sure enough, the original application
> had gone into timeout. I reloaded the new database appl. that I'd
> developed not ten minutes before, created a new record, and once again
> it worked! And guess what? When I reloaded the original application
> it too worked.
>
> So, for some reason that completely escapes me, the connection times
> out for this one particular INSERT on a linked table. Open a
> different Access app., linked to THE SAME table, and the INSERT
> doesn't time out. Reload the original app., and it doesn't time out
> (though I have NO confidence at all that it will stay stable for any
> length of time).
>
> I'm completely baffled. I was planning to do what Hugo suggested - to
> set a trace going and capture the SQL that the "hanging" insert was
> sending, but I ran out of time and unfortunately the application
> resides on a PC that is geographically distant from any PC where I
> could set the trace going.
>
> (followup reset to include Access groups)

If it is true that one app truly 'works' and the other does not, then
the problem is client side and there is some difference between the
client apps that is making the difference. This puts you back to
finding out what that difference is (Access version, system options,
property settings, file corruption, etc.). The possibility remains
however that your second 'new' app will also malfunction
intermittently like the first one does given the time and opportunity
to do so. This puts you back to troubleshooting the entire chain from
client to server. Sometimes it is more expedient to try rewriting
things to work a different way (as you intimated you might do by
rewriting this in .net) than it is to try to actually find the
problem. Rather than going to the extreme of rewriting the whole app
you might try unbinding that particular form and handling your update
via DAO, ADO, or a passthrough query to see if that avoids the
problem. If you have the time and energy to actually pinpoint what's
going on you could always install EM on the machine where the app
currently resides or investigate the use of Windows' Remote Desktop or
inexpensive third party products like Dameware to remotely access
another PC. Best of luck.

Bruce

[Back to original message]