| Groups | Blog | Home |
sql server odbc : ODBC Update on a linked table failed
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 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 the
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 [quoted text, click to view] <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 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 the >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. [quoted text, click to view] "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: > > >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 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 the > >recordsource, it works fine. What am I doing wrong? >
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. [quoted text, click to view] "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: > > >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: > >> > >> >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 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 the > >> >recordsource, it works fine. What am I doing wrong? > >> > >> >
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 [quoted text, click to view] <Crossh@discussions.microsoft.com> wrote:
>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: >> >> >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 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 the >> >recordsource, it works fine. What am I doing wrong? >> >>
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 [quoted text, click to view] <Crossh@discussions.microsoft.com> 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: >> >> >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: >> >> >> >> >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 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 the >> >> >recordsource, it works fine. What am I doing wrong? >> >> >> >> >> >>
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |