Posted by Bruce on 09/11/07 19:37

On Sep 11, 5:45 am, teddysn...@hotmail.com wrote:
> On 10 Sep, 17:20, Bruce <deluxeinformat...@gmail.com> wrote:
> [...]
>
> > It looks pretty straightforward as you describe. The two minute delay
> > attempting to save the record makes me think that something is timing
> > out, i.e., it's attempting to save the record but can't for some
> > reason (perhaps something else has the record locked at that point?),
> > and your error handler isn't telling you why. Have you tried setting
> > a breakpoint at Docmd.Close to see if an error is occurring that your
> > error handler is trapping but simply discarding? Alternatively you
> > might try setting the 'break on all errors' option under tools,
> > options, general tab in the VBA editor. Also are there any triggers
> > on the table on the SQL side that might be causing a problem? Looking
> > at the error log on the SQL side during the time frame the problems
> > occur might shed some light on the issue.
>
> You're right about the timeout. I put a breakpoint on the line:
>
> DoCmd.Close
>
> at which point the hourglass started up. After about two minutes
> there was an error message:
>
> "ODBC - Insert on a linked table 'dbo_tblSBIndex' failed
> [Microsoft][ODBC SQL Server Driver] Timeout expired (#0)"
>
> This message does not appear if the breakpoint is removed.
>
> I put a breakpoint on the same line in another functionally identical
> form (as mentioned in message 1 upthread) and this did NOT time out.
>
> There are no triggers on any tables in the database. I'm pretty well
> all out of ideas.
>
> Edward

Does this table have a unique key defined at the SQL level? Have you
tried dropping and recreating the link for this table?

Having said that, I think your timeout is almost undoubtedly happening
at the SQL level. Some other user or process has tblSBIndex (or a
subset of records therein) locked at the time you're doing your
update. I'm not an SQL guru but you'll probably want to use EM to
look at the locks while your update on tblSBIndex is hung to see who
or what else has it locked and why. The locks can be viewed under
Management, Current Activity (you'll probably want to look at the
Locks / Object node to see locks on that specific table) on your
server.

Bruce

[Back to original message]