[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

Access - SQL data issue

We are experiencing a data inconsistency issue.  Employee Database has Access 2003 front end. Users are using mde file with forms and queries, reports, etc in it. Backend is SQL Server 2005. The users can change certain fields in the database, other fields they cant since there is a synchronization with AD.
The issue came up several times thruout last year:
the user claimed to have changed a certain field in the record, saved it (via a button on the form that runs a save command), closed out of the database (X is disabled, there is a button that runs close command). At first, this was considered a user error. Now I am thinking that this may not be the case.
Any thoughts on this? This is an important issue I need to be able to explain to my management.
1 Solution
Anthony PerkinsCommented:
>>At first, this was considered a user error. <<
There is not such thing as a "user error", it is just a badly designed program.

>>Now I am thinking that this may not be the case. <<
Correct, it is not, it is a bug in your code.  You need to log all events to confirm the user is clicking the save button, if they are not and are expecting it to save when then close the app, you have a problem.
Jeffrey CoachmanMIS LiasonCommented:
I agree with acperkins.

This is what can happen when you create your own (Save, close, ...etc) interface and bypass some of Access' built in functionality (disable "X" on the form, ...etc)

You need to be very careful of how you code things or you can twist yourself into knots...

Many developers create these alternate interfaces because users do not understand how Access functions normally.

Sometimes a little user education, (not a new interface), is required.


It might be wise to add a little sanity checking code in the Form's On_Close event.  Check if the data's Dirty, and if it is, pop-up a confirmation "Data has been changed.  Do you want to save the changes?" message box that runs your Save code on "Yes", that simply closes on "No", and that does nothing on "Cancel".  

The simplest "save" code, of course, is to set Me.Dirty = False.

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

HarterAuthor Commented:
I appreciate all the comments but this is not my code, nor my design.
How do I " log all events to confirm the user is clicking the save button"?
I am still not following what is happening with the database.. why isnt the data being saved?
Without getting into a detailed debugging session, I don't think anyone could tell you why not.

Do you have a production or test environment where you can safely work on this yourself?  It sounds like you need to create a set of test to cover all the ways that the forms and database could be closed, and determine what should and should not happen, then start working through them to narrow down exactly where and how this is going wrong.

Then you can start to debug it, or ask us more specific questions.  Doing things by the book makes sense.

It also sounds like you need to make it clear to you management that right now, it *isn't* clear what's going wrong, and that you'll need to spend some time doing some careful, well documented testing to determine what's going on, and until that is done, you can't begin to address the fix.

The idea is to get things to a point where it's simple (i.e. easy to explain or describe).  While things are complicated (i.e. hard to explain or describe) there's always that chance that management will think that the reason you can't give them an answer it that you're not up to it, as opposed to the answer not yet being available.  Tell them the steps that you're taking towards the solution, and they'll know that you're doing ok.
Eric ShermanAccountant/DeveloperCommented:
Is is just the one user???

Are you having any other problems, error messages, etc. when entering or changing data using the Front-End Forms???

HarterAuthor Commented:
Another user reported the same issue several months ago and since then that only user repeatedly having issue. I - myself experienced it one time. I ended up making a change directly in the SQL table and then running a report. Could there be an issue linking tables to SQL server? Or server issue?
HarterAuthor Commented:
No other problems, no error messages at all. The user changes information in a form, saves the record using a Save button and closes the Form, runs report and the change isnt reflected.
That is definitely not enough information to be able to draw a definite conclusion.  So now there's a decision to make: How much effort should be put into resolving this?  Given that the problem is intermittent, that it's not clear if this is a network problem, a data architecture problem, a user interface problem, operator error, or freakin' sunspots, how much of your time should be committed to solving this?

That's a decision for you and your manager to nut out.

My initial thoughts are, in no particular order:
* User error (might require use of a screen recorder like TimeSnapper to capture this - training might be a better solution)
* Network error (check the logs around the time the error occurred)
* Data architecture (insufficient relational integrity, allowing orphaned records to be created that then don't show in reports)
* Poor error handling, errors being deliberately ignored in code so badly formed data is saved (relates to data architecture)
* Who knows?!?

Talk to your manager about how to proceed.
HarterAuthor Commented:
Unfortunately, the answers are needed because even though the issue is intermittent, it persists, it happens. There are other processes that depend on the accurate data in the database. I mean this is the HR system!!!! The department is not happy with it and had purchased a new system to implement in 6 months but for now they are still using this (they call it) broken database. Most of the frustration comes from the data accuracy issue, the rest is from my inability to explain what is happening.....
They want me to investigate and that is what I need to do. The person responsible for the database had added some tracking in it to show that such user modified such record but it is not showing what exactly was changed in the record, what field. There is a check for dirty data before closing the form...
Access will keep tend to buffer read and writes. Make sure your actions when the database or form closes cannot be cancelled ie. make sure a save error can abort the close (stay on record, stay on form); make sure you have the on error handle able to cancel the close events and cascade this cancel to all close events. Make sure the various close events cannot make the app quit before the write to the database is complete. docmd.save is not the way to do it for the contents of records.
HarterAuthor Commented:
I understand all of those and we already had disabled cancel or close button and created a close db procedure that writes all the uncommitted data. I appreciate your response.
HarterAuthor Commented:
the database had been retired

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now