ODBC Update on a linked table failed

I created a form in Access 2003 who's recordsource is an ODBC linked table f
rom
SQL Server 2000. The form has a subform that lists all the records in that
table. The form also has all the fields from that table as separate controls
.
I have a command button to add new records, which works without any
problems. When the user clicks on any record in the subform, I fill in the
controls on the form, and this works fine. But If I try to edit anything in
the record, when it tries to update the record, I get this message , ODBC
Update on a linked table "Table Name" failed - [Microsoft][ODBC SQL
Server
Driver]Timeout expired(#0). It also takes 1-2 minutes before this message
appears. If I remove the subform or if I use a table thats not linked for th
e
recordsource, it works fine. What am I doing wrong?You probably have some locking or blocking issues based on
how the form and recordsources are designed. You can view
whatever SQL statement are being executed by running a trace
or running Profiler. In terms of optimizing your form
itself, you would probably want to post that on one of the
Access newsgroups. Try one of these:
microsoft.public.access.formscoding
microsoft.public.access.odbcclientsvr
-Sue
On Fri, 17 Nov 2006 11:01:02 -0800, Crossh
<Crossh@.discussions.microsoft.com> wrote:

>I created a form in Access 2003 who's recordsource is an ODBC linked table
from
>SQL Server 2000. The form has a subform that lists all the records in that
>table. The form also has all the fields from that table as separate control
s.
>I have a command button to add new records, which works without any
>problems. When the user clicks on any record in the subform, I fill in the
>controls on the form, and this works fine. But If I try to edit anything in
>the record, when it tries to update the record, I get this message , ODBC
>Update on a linked table "Table Name" failed - [Microsoft][ODBC SQL
Server
>Driver]Timeout expired(#0). It also takes 1-2 minutes before this message
>appears. If I remove the subform or if I use a table thats not linked for t
he
>recordsource, it works fine. What am I doing wrong?|||Sorry, I'm not familiar with these. How do you run a trace or Profiler?
It is definitely a locking issue, because I tried splitting the form into
two separate forms, clearing out the subform list before opening up the new
form for editing, and it works fine. I just don't understand why the subform
that has the record list is locking the record. The subform properties are
RecordsetType=Snapshot, RecordLocks=NoLocks, Query properties are
RecordsetType=Snapshot, RecordLocks=NoLocks.
"Sue Hoegemeier" wrote:

> You probably have some locking or blocking issues based on
> how the form and recordsources are designed. You can view
> whatever SQL statement are being executed by running a trace
> or running Profiler. In terms of optimizing your form
> itself, you would probably want to post that on one of the
> Access newsgroups. Try one of these:
> microsoft.public.access.formscoding
> microsoft.public.access.odbcclientsvr
> -Sue
> On Fri, 17 Nov 2006 11:01:02 -0800, Crossh
> <Crossh@.discussions.microsoft.com> wrote:
>
>|||The subform design, properties, etc would probably be better addressed
in a Microsoft Access newsgroup.
To run profiler, from the start button go to the SQL Server program
group and you will find profiler. You can find more information on
using the tool in Books Online (the SQL Server help file).
-Sue
On Tue, 21 Nov 2006 07:24:02 -0800, Crossh
<Crossh@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sorry, I'm not familiar with these. How do you run a trace or Profiler?
>It is definitely a locking issue, because I tried splitting the form into
>two separate forms, clearing out the subform list before opening up the new
>form for editing, and it works fine. I just don't understand why the subfor
m
>that has the record list is locking the record. The subform properties are
>RecordsetType=Snapshot, RecordLocks=NoLocks, Query properties are
>RecordsetType=Snapshot, RecordLocks=NoLocks.
>
>"Sue Hoegemeier" wrote:
>|||I actually posted it in both newsgroups. I wasn't sure which was causing the
problem, SQL or Access. You were the first to respond. Thanks so much for
your help.
"Sue Hoegemeier" wrote:

> The subform design, properties, etc would probably be better addressed
> in a Microsoft Access newsgroup.
> To run profiler, from the start button go to the SQL Server program
> group and you will find profiler. You can find more information on
> using the tool in Books Online (the SQL Server help file).
> -Sue
> On Tue, 21 Nov 2006 07:24:02 -0800, Crossh
> <Crossh@.discussions.microsoft.com> wrote:
>
>|||Yeah...it's not as cut and dry as it might seem. It's timing
out due to locking, blocking type of issues in SQL Server
but then again that would be related to how the form and
subform is designed. What you can do is use profiler or even
just execute sp_who2, sp_lock, query sysprocesses when you
hit the issue. As long as it's timing out, you should be
able to capture it with those (but Profiler would be
better). From there, you would want to determine what is
being executed, what part of what action on the form,
subform is leading to the problem. And then from there...you
can look at the design. Most of it will be related to how
the forms are populated, what kind of binding and that type
of thing. I can't remember enough Access off the top of my
head to give you enough direction on how you may want to
rethink the form, subform design.
-Sue
On Tue, 21 Nov 2006 09:19:01 -0800, Crossh
<Crossh@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I actually posted it in both newsgroups. I wasn't sure which was causing th
e
>problem, SQL or Access. You were the first to respond. Thanks so much for
>your help.
>"Sue Hoegemeier" wrote:
>