Posted by teddysnips on 09/07/07 15:44

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

[Back to original message]