ACCESS Front End
SQl Server 2k Back End

The application is a perfectly straightforward MS Access MDB file that
is linked to a SQL Server database on a LAN.

The application has been stable for six years. However, earlier this
month the SQL Server box crashed, owing to another database growing
too big for its boots. Since then SQL Server has been reinstalled and
databases reattached.

However, since then there is a really weird bug with the MS Access
application.

It's a Technical Publications Distribution system. There is one full-
time user. It is his responsibility to add new documents, or amend
documents when they are revised. The system also allows the user to
create distribution lists for companies.

An early design decision, and one which I regret not putting my food
down about, resulted in each different document type having its own
table. Partly this is because prior to this system each document type
was stored in a separate database.

Anyway, the architecture of the system is pretty standard. A
switchboard allows the user to select a document type - e.g. Component
Maintenance Manual, Service Bulletin etc. Each document type has a
"Search" form, allowing the user to search for a document or group of
documents. Each "Search" form also has an "Add" button, to allow the
user to add a new record. This "New" record is simply a form bound to
the table associated with the document type. After entering data,
closing the form automatically saves the record. This can then be
found in the "Search" form.

Except for one form. Open the "Service Bulletin" search form and
press "Add". The "Service Bulletin Record" form is opened at a new
record. Enter data into the mandatory fields, press "Close", and the
system waits for about two minutes before returning to the "Search"
form. However, the record has NOT been saved. For all the other
document types, this operation works correctly (with a two second
instead of a two minute wait!).

I was sitting with the client the other day, looking at a different
problem, and I asked him to show me the "Service Bulletin" problem
again (just on the off chance that there was an error message he
wasn't telling me about). And guess what! The system worked as
expected, as it had for the preceding five or so years. We scratched
our heads, congratulated ourselves on a job well done, and went our
separate ways.

Except the next day it stopped working again. You can add data to the
underlying table directly into SQL Server, either through EM or via a
query in QA. You can also add the data into the linked table in
Access "Tables" pane. But the application doesn't work, for just this
one simple operation, on this one simple table. The database is only
143 Mb, the table has only 16,000 rows and 15 columns.

Needless to say, it works fine on our network.

Anyone, anywhere, any ideas?

Edward

On Sep 7, 10:44 am, teddysn...@hotmail.com wrote:
> ACCESS Front End
> SQl Server 2k Back End
>
> The application is a perfectly straightforward MS Access MDB file that
> is linked to a SQL Server database on a LAN.
>
> The application has been stable for six years. However, earlier this
> month the SQL Server box crashed, owing to another database growing
> too big for its boots. Since then SQL Server has been reinstalled and
> databases reattached.
>
> However, since then there is a really weird bug with the MS Access
> application.
>
> It's a Technical Publications Distribution system. There is one full-
> time user. It is his responsibility to add new documents, or amend
> documents when they are revised. The system also allows the user to
> create distribution lists for companies.
>
> An early design decision, and one which I regret not putting my food
> down about, resulted in each different document type having its own
> table. Partly this is because prior to this system each document type
> was stored in a separate database.
>
> Anyway, the architecture of the system is pretty standard. A
> switchboard allows the user to select a document type - e.g. Component
> Maintenance Manual, Service Bulletin etc. Each document type has a
> "Search" form, allowing the user to search for a document or group of
> documents. Each "Search" form also has an "Add" button, to allow the
> user to add a new record. This "New" record is simply a form bound to
> the table associated with the document type. After entering data,
> closing the form automatically saves the record. This can then be
> found in the "Search" form.
>
> Except for one form. Open the "Service Bulletin" search form and
> press "Add". The "Service Bulletin Record" form is opened at a new
> record. Enter data into the mandatory fields, press "Close", and the
> system waits for about two minutes before returning to the "Search"
> form. However, the record has NOT been saved. For all the other
> document types, this operation works correctly (with a two second
> instead of a two minute wait!).
>
> I was sitting with the client the other day, looking at a different
> problem, and I asked him to show me the "Service Bulletin" problem
> again (just on the off chance that there was an error message he
> wasn't telling me about). And guess what! The system worked as
> expected, as it had for the preceding five or so years. We scratched
> our heads, congratulated ourselves on a job well done, and went our
> separate ways.
>
> Except the next day it stopped working again. You can add data to the
> underlying table directly into SQL Server, either through EM or via a
> query in QA. You can also add the data into the linked table in
> Access "Tables" pane. But the application doesn't work, for just this
> one simple operation, on this one simple table. The database is only
> 143 Mb, the table has only 16,000 rows and 15 columns.
>
> Needless to say, it works fine on our network.
>
> Anyone, anywhere, any ideas?
>
> Edward

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?

Bruce

On 7 Sep, 19:39, Bruce <deluxeinformat...@gmail.com> wrote:
> On Sep 7, 10:44 am, teddysn...@hotmail.com wrote:
[...]
> 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 ? (Obviously there's a bunch of functions such as
modErrorHandler that won't at all!)

Edward

On Sep 10, 6:35 am, teddysn...@hotmail.com wrote:
> On 7 Sep, 19:39, Bruce <deluxeinformat...@gmail.com> wrote:
>
> > On Sep 7, 10:44 am, teddysn...@hotmail.com wrote:
> [...]
> > 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 ? (Obviously there's a bunch of functions such as
> modErrorHandler that won't 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.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

On Sep 11, 6: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

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:
[...]
> 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.

> Have you checked for field name errors on the form?

Yes.

> Is your form based on a query that is not updateable?

No, it's based on a linked table.

> Have you refreshed the link to the table?

Many, many times. I've also deleted and recreated the DSN used to
connect.

> 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:
> 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?

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:
> 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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

teddysnips@hotmail.com wrote:
> Except the next day it stopped working again. You can add data to the
> underlying table directly into SQL Server, either through EM or via a
> query in QA. You can also add the data into the linked table in
> Access "Tables" pane. But the application doesn't work, for just this
> one simple operation, on this one simple table. The database is only
> 143 Mb, the table has only 16,000 rows and 15 columns.

16,000 or 16,384, as in 2^14, as in possibly limited by the number of bits
available to represent something?

> Needless to say, it works fine on our network.

Is that with the same database?

Andrew

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

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

teddysnips@hotmail.com wrote:

> 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.INVALID> wrote:
[...]
> 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:
> On Sep 12, 8:37 pm, Hugo Kornelis<h...@perFact.REMOVETHIS.info.INVALID> wrote:
>
> [...]
>
>
>
> > 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)

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:
> 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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx