Solved

Access - SQL data issue

Posted on 2011-03-08
13
232 Views
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.
thanks
0
Comment
Question by:Harter
13 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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...
:-O

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.

;-)

JeffCoachman
0
 
LVL 8

Expert Comment

by:Andrew_Webster
Comment Utility
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.

0
 

Author Comment

by:Harter
Comment Utility
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?
0
 
LVL 8

Expert Comment

by:Andrew_Webster
Comment Utility
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.
0
 
LVL 19

Expert Comment

by:Eric Sherman
Comment Utility
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???

ET
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:Harter
Comment Utility
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?
0
 

Author Comment

by:Harter
Comment Utility
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.
0
 
LVL 8

Expert Comment

by:Andrew_Webster
Comment Utility
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.
0
 

Author Comment

by:Harter
Comment Utility
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...
0
 
LVL 5

Expert Comment

by:tygrus2
Comment Utility
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.
0
 

Accepted Solution

by:
Harter earned 0 total points
Comment Utility
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.
0
 

Author Closing Comment

by:Harter
Comment Utility
the database had been retired
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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 the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

728 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now