Posted by teddysnips on 09/13/07 10:30

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)

Edward

[Back to original message]