|
|
Posted by teddysnips on 09/12/07 11:18
On 11 Sep, 20:37, Bruce <deluxeinformat...@gmail.com> wrote:
> 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?
I'm in the process of entirely recreating the table from scratch
(creating a clone, copying all data in from existing table, dropping
existing table, renaming clone to existing table name)
> 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.
The strange thing is that the user cannot add data using the
application (as upthread - an Access .mdb file using a form bound to
the linked table) but I can open the table from within the application
(Access exposes a list of tables, somewhat like EM, and you can open
the table), go to a new row and type in the required values with NO
latency at all. The data are saved the instant you tab off the row.
Presumably, if the table were locked as you say, this operation would
be prevented.
Edward
Navigation:
[Reply to this message]
|