Access - SQL data issue

Posted on 2011-03-08
Last Modified: 2012-05-11
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.
Question by:Harter
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35069229
>>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.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35071083
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.



Expert Comment

ID: 35071606
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.

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.


Author Comment

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

Expert Comment

ID: 35073794
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.
LVL 19

Expert Comment

by:Eric Sherman
ID: 35075447
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???


Author Comment

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

Author Comment

ID: 35094269
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.

Expert Comment

ID: 35094854
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.

Author Comment

ID: 35094950
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...

Expert Comment

ID: 36961900
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. is not the way to do it for the contents of records.

Accepted Solution

Harter earned 0 total points
ID: 36962014
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.

Author Closing Comment

ID: 36984568
the database had been retired

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question