Quote:
Originally Posted by
On Sep 7, 10:44 am, teddysn...@.hotmail.com wrote:
[...]
Quote:
Originally Posted by
Is this form bound to a table or view in the SQL database or is it
updating via code? What is the code behind your "Add" and "Close"
buttons?
The form is bound to a table - obviously in this case it's a linked
table. Not a view.
The "Add" button's code as follows:
Private Sub cmdAddnew_Click()
On Error GoTo cmdAddnew_Click_Err
' Display Service Bulletin form with blank record
DoCmd.OpenForm "frmSBIndex", , , , , , gcintAddRecord 'Global Const
gcintAddRecord As Integer = 0
Me.Visible = False
cmdAddnew_Click_Exit:
Exit Sub
cmdAddnew_Click_Err:
Call modErrorHandler(Err, Erl, Error(Err), "cmdAddnew_Click")
Resume cmdAddnew_Click_Exit
End Sub
The "Close" button's code as follows:
Private Sub cmdClose_Click()
On Error GoTo cmdClose_Click_Err
DoCmd.Close
If (modIsloaded("frmSelectSBs")) Then
Call modDisplayForm("frmSelectSBs")
End If
cmdClose_Click_Exit:
Exit Sub
cmdClose_Click_Err:
Call modErrorHandler(Err, Erl, Error(Err), "cmdClose_Click")
Resume cmdClose_Click_Exit
End Sub
Does that help? (Obviously there's a bunch of functions such as
modErrorHandler that won't help at all!)
EdwardOn Sep 10, 6:35 am, teddysn...@.hotmail.com wrote:
Quote:
Originally Posted by
On 7 Sep, 19:39, Bruce <deluxeinformat...@.gmail.comwrote:
>
Quote:
Originally Posted by
On Sep 7, 10:44 am, teddysn...@.hotmail.com wrote:
[...]
Quote:
Originally Posted by
Is this form bound to a table or view in the SQL database or is it
updating via code? What is the code behind your "Add" and "Close"
buttons?
>
The form is bound to a table - obviously in this case it's a linked
table. Not a view.
>
The "Add" button's code as follows:
>
Private Sub cmdAddnew_Click()
>
On Error GoTo cmdAddnew_Click_Err
>
' Display Service Bulletin form with blank record
DoCmd.OpenForm "frmSBIndex", , , , , , gcintAddRecord 'Global Const
gcintAddRecord As Integer = 0
Me.Visible = False
>
cmdAddnew_Click_Exit:
Exit Sub
>
cmdAddnew_Click_Err:
Call modErrorHandler(Err, Erl, Error(Err), "cmdAddnew_Click")
Resume cmdAddnew_Click_Exit
>
End Sub
>
The "Close" button's code as follows:
>
Private Sub cmdClose_Click()
>
On Error GoTo cmdClose_Click_Err
>
DoCmd.Close
If (modIsloaded("frmSelectSBs")) Then
Call modDisplayForm("frmSelectSBs")
End If
>
cmdClose_Click_Exit:
Exit Sub
>
cmdClose_Click_Err:
Call modErrorHandler(Err, Erl, Error(Err), "cmdClose_Click")
Resume cmdClose_Click_Exit
>
End Sub
>
Does that help? (Obviously there's a bunch of functions such as
modErrorHandler that won't help at all!)
>
Edward
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.
Bruce|||On 10 Sep, 17:20, Bruce <deluxeinformat...@.gmail.comwrote:
[...]
Quote:
Originally Posted by
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|||On Sep 11, 6:45 am, teddysn...@.hotmail.com wrote:
Quote:
Originally Posted by
On 10 Sep, 17:20, Bruce <deluxeinformat...@.gmail.comwrote:
[...]
>
Quote:
Originally Posted by
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
Just a few things I might look at. Maybe you have already.
Have you compiled the code module?
Have you checked for field name errors on the form?
Is your form based on a query that is not updateable?
Have you refreshed the link to the table?
Have you tried creating a new table and appending records from the
problem table?|||On Sep 11, 5:31 pm, lgeastw...@.gmail.com wrote:
[...]
Quote:
Originally Posted by
Just a few things I might look at. Maybe you have already.
Have you compiled the code module?
Both compiled it, and also decompiled and recompiled. Interestingly
(or not, YMMV), this crashed Access every time I tried to load the
application.
Quote:
Originally Posted by
Have you checked for field name errors on the form?
Yes.
Quote:
Originally Posted by
Is your form based on a query that is not updateable?
No, it's based on a linked table.
Quote:
Originally Posted by
Have you refreshed the link to the table?
Many, many times. I've also deleted and recreated the DSN used to
connect.
Quote:
Originally Posted by
Have you tried creating a new table and appending records from the
problem table?
Better than that, I've entirely recreated the database from scratch -
i.e. scripted it, then created an import using DTS. Took a while mind
you. Didn't do any good.
One thing that I didn't mention, mainly because it's not something
that has changed recently (in fact it changed about two years ago, and
I didn't notice until today when I was sitting with the client), is
that the application was developed under Access 2k (makes sign of
cross) but is actually running under Access 2k3. So my latest wheeze
was to create a new Access 2k3 database, import all the objects from
the 2k database, recompile, save in 2k3 format. Haven't had a chance
to try it out yet - my client left the office to play golf at midday,
since the lack of the application meant he couldn't do any work.
Thanks, anyway, for all the suggestions. I'll let you all know the
outcome of tomorrow's little experiment. If it doesn't work, I think
I'm probably going to suggest that I rewrite this as a .NET
application. Shouldn't take too long, since all the forms ane
designed, the database exists, and the VBA would make a perfectly
acceptable pseudo-code design.
Edward|||On Sep 11, 5:45 am, teddysn...@.hotmail.com wrote:
Quote:
Originally Posted by
On 10 Sep, 17:20, Bruce <deluxeinformat...@.gmail.comwrote:
[...]
>
Quote:
Originally Posted by
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|||Bruce (deluxeinformation@.gmail.com) writes:
Quote:
Originally Posted by
Having said that, I think your timeout is almost undoubtedly happening
at the SQL level.
Query timeouts are always client-side, although the reason it takes
a long time for the query to run, is likely to be found on the SQL
Server side.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On 11 Sep, 20:37, Bruce <deluxeinformat...@.gmail.comwrote:
Quote:
Originally Posted by
On Sep 11, 5:45 am, teddysn...@.hotmail.com wrote:
>
>
>
>
>
Quote:
Originally Posted by
On 10 Sep, 17:20, Bruce <deluxeinformat...@.gmail.comwrote:
[...]
>
Quote:
Originally Posted by
Quote:
Originally Posted by
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.
>
Quote:
Originally Posted by
You're right about the timeout. I put a breakpoint on the line:
>
Quote:
Originally Posted by
DoCmd.Close
>
Quote:
Originally Posted by
at which point the hourglass started up. After about two minutes
there was an error message:
>
Quote:
Originally Posted by
"ODBC - Insert on a linked table 'dbo_tblSBIndex' failed
[Microsoft][ODBC SQL Server Driver] Timeout expired (#0)"
>
Quote:
Originally Posted by
This message does not appear if the breakpoint is removed.
>
Quote:
Originally Posted by
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.
>
Quote:
Originally Posted by
There are no triggers on any tables in the database. I'm pretty well
all out of ideas.
>
Quote:
Originally Posted by
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)
Quote:
Originally Posted by
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|||On Tue, 11 Sep 2007 03:45:05 -0700, teddysnips@.hotmail.com wrote:
Quote:
Originally Posted by
>On 10 Sep, 17:20, Bruce <deluxeinformat...@.gmail.comwrote:
>[...]
Quote:
Originally Posted by
>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|||teddysnips@.hotmail.com wrote:
Quote:
Originally Posted by
the application was developed under Access 2k (makes sign of
cross) but is actually running under Access 2k3. So my latest wheeze
was to create a new Access 2k3 database, import all the objects from
the 2k database, recompile, save in 2k3 format.
Is there a separate 2k3 format? 2k, 2k2, 2k3 all use Jet 4.0, at
least. (Access is a small part of what we do, so I don't think I've
had occasion to use anything later than 2k.)|||On Sep 12, 8:37 pm, Hugo Kornelis
<h...@.perFact.REMOVETHIS.info.INVALIDwrote:
[...]
Quote:
Originally Posted by
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)
UPDATE:
I visited the client this morning on a different matter, and took
another look at the problem, in the hope that it might have gone
away. No such luck.
Since I had a few minutes to spare, I created a NEW MS Access
database, linked via the DSN to the database, and created a link to
JUST the table that was causing the problem. I then created a single
form which was based on the newly linked table. I opened the form,
pressed the "New" record button on the Record Navigation group,
entered a new record, pressed the "Previous" record button and voila!
the record was saved! I then reverted to the original application and
it worked! My client was delighted.
I was just in my car getting ready to leave when the client phoned.
"It's stopped working again". Sure enough, the original application
had gone into timeout. I reloaded the new database appl. that I'd
developed not ten minutes before, created a new record, and once again
it worked! And guess what? When I reloaded the original application
it too worked.
So, for some reason that completely escapes me, the connection times
out for this one particular INSERT on a linked table. Open a
different Access app., linked to THE SAME table, and the INSERT
doesn't time out. Reload the original app., and it doesn't time out
(though I have NO confidence at all that it will stay stable for any
length of time).
I'm completely baffled. I was planning to do what Hugo suggested - to
set a trace going and capture the SQL that the "hanging" insert was
sending, but I ran out of time and unfortunately the application
resides on a PC that is geographically distant from any PC where I
could set the trace going.
(followup reset to include Access groups)
Edward|||On Sep 13, 5:30 am, teddysn...@.hotmail.com wrote:
Quote:
Originally Posted by
On Sep 12, 8:37 pm, Hugo Kornelis<h...@.perFact.REMOVETHIS.info.INVALIDwrote:
>
[...]
>
>
>
Quote:
Originally Posted by
Hi Edward,
>
Quote:
Originally Posted by
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:
>
Quote:
Originally Posted by
1. Use profiler to catch the commands sent by Access to SQL Server that
lead to the timeout.
>
Quote:
Originally Posted by
2. Use SQL Server Management Studio (or Query Analyzer, if you're on SQL
Server 2000) to execute those same commands.
>
Quote:
Originally Posted by
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.
>
Quote:
Originally Posted by
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).
>
Quote:
Originally Posted by
(followup set to SQL Server groups only)
>
UPDATE:
>
I visited the client this morning on a different matter, and took
another look at the problem, in the hope that it might have gone
away. No such luck.
>
Since I had a few minutes to spare, I created a NEW MS Access
database, linked via the DSN to the database, and created a link to
JUST the table that was causing the problem. I then created a single
form which was based on the newly linked table. I opened the form,
pressed the "New" record button on the Record Navigation group,
entered a new record, pressed the "Previous" record button and voila!
the record was saved! I then reverted to the original application and
it worked! My client was delighted.
>
I was just in my car getting ready to leave when the client phoned.
"It's stopped working again". Sure enough, the original application
had gone into timeout. I reloaded the new database appl. that I'd
developed not ten minutes before, created a new record, and once again
it worked! And guess what? When I reloaded the original application
it too worked.
>
So, for some reason that completely escapes me, the connection times
out for this one particular INSERT on a linked table. Open a
different Access app., linked to THE SAME table, and the INSERT
doesn't time out. Reload the original app., and it doesn't time out
(though I have NO confidence at all that it will stay stable for any
length of time).
>
I'm completely baffled. I was planning to do what Hugo suggested - to
set a trace going and capture the SQL that the "hanging" insert was
sending, but I ran out of time and unfortunately the application
resides on a PC that is geographically distant from any PC where I
could set the trace going.
>
(followup reset to include Access groups)
If it is true that one app truly 'works' and the other does not, then
the problem is client side and there is some difference between the
client apps that is making the difference. This puts you back to
finding out what that difference is (Access version, system options,
property settings, file corruption, etc.). The possibility remains
however that your second 'new' app will also malfunction
intermittently like the first one does given the time and opportunity
to do so. This puts you back to troubleshooting the entire chain from
client to server. Sometimes it is more expedient to try rewriting
things to work a different way (as you intimated you might do by
rewriting this in .net) than it is to try to actually find the
problem. Rather than going to the extreme of rewriting the whole app
you might try unbinding that particular form and handling your update
via DAO, ADO, or a passthrough query to see if that avoids the
problem. If you have the time and energy to actually pinpoint what's
going on you could always install EM on the machine where the app
currently resides or investigate the use of Windows' Remote Desktop or
inexpensive third party products like Dameware to remotely access
another PC. Best of luck.
Bruce|||(teddysnips@.hotmail.com) writes:
Quote:
Originally Posted by
Since I had a few minutes to spare, I created a NEW MS Access
database, linked via the DSN to the database, and created a link to
JUST the table that was causing the problem. I then created a single
form which was based on the newly linked table. I opened the form,
pressed the "New" record button on the Record Navigation group,
entered a new record, pressed the "Previous" record button and voila!
the record was saved! I then reverted to the original application and
it worked! My client was delighted.
At this point, did your mock-up application still run?
Did you try to save twice from the mock-up?
Have you checked if there are any triggers on the troublesome table? My
feeling is that there is a trigger which produces a result set, or even
just a rowcount, and the application fails to consume that. When you try
to save a new, friendly ADO will open a new connection behind your back,
because the first connection is busy. But the second connection is blocked
by the first.
But that's really only a guess, and keep in mind that my knowledge on
Access is about zero.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
No comments:
Post a Comment