Posted by Bruce on 09/07/07 18:39

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

[Back to original message]