|
|
Posted by Hugo Kornelis on 09/12/07 19:37
On Tue, 11 Sep 2007 03:45:05 -0700, teddysnips@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
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)
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Navigation:
[Reply to this message]
|