Go Premium for a chance to win a PS4. Enter to Win


Access - SQL data issue

Posted on 2011-03-08
Medium Priority
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.

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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. docmd.save 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

885 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