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