Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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.

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.


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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

719 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