Solved

MS Access -- Data being deleted from a single field after being entered.

Posted on 2011-02-10
10
240 Views
Last Modified: 2012-06-21
Have a new fun problem with one of my databases.

Using MS Access 2007

On a form with 100-150 (not really sure anymore) fields for data entry, the database is refusing to accept data for 1 field. It's a date field, one of many, and I haven't changed anything structurally in 2-3 months. But the users enter the information, save it, and when they reopen the record the data is gone.  

Any idea why it's not saving?  
0
Comment
Question by:MedscapeEditorial
  • 4
  • 4
  • 2
10 Comments
 
LVL 20

Expert Comment

by:GrahamMandeno
ID: 34866699
Are you sure the textbox on the form is correctly bound to the field in your RecordSource?  Check the COntrolSource property of the textbox.

--
Graham Mandeno [Access MVP]
0
 

Author Comment

by:MedscapeEditorial
ID: 34867742
Checked that--it's still fine.  It's taking the data about 50% of the time. Thought perhaps it was an older structural change but nothing I can readily find.  And 50% of the time seems too high for it to be two people in the same record at the same time--most of the work doesn't overlap.

I rebuilt the field on the form....we'll see. I'm not optimistic.  

0
 
LVL 20

Expert Comment

by:GrahamMandeno
ID: 34867874
It sounds like database corruption to me.  You may need to take some steps to recover the code and/or the data.

There is some very helpful information on this here:
http://www.granite.ab.ca/access/corruptmdbs.htm

--
Graham
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 83 total points
ID: 34899133
Well, 100-150 fields in a table seems a bit high.

Can you take a step back and explain what this table is storing please?

Access will start to throw errors if the data in a Record is beyond a certain limit.
Not really based on the number of fields, but the Datatypes of the fields and what is being stored in them.

Also check the size of the DB.
Because of Images, OLE fields, or other things, you may be close to the 2GB DB limit.

Are you running the Compact/Repair utility on a regular basis?
Have you tried creating a new DB and importing all of the objects?
Is this Data Linked?
...etc

In other words, without being intimately familiar with this DB, it is difficult to come up with a exact "Solution" to your issue here.


JeffCoachman
0
 

Author Comment

by:MedscapeEditorial
ID: 34953461
The database is for project tracking. This issue is on the main data entry form, which pulls about 12 tables together.

The specific table in question has about 8 fields, four of which are date fields, one of which is a text linked to dropdown for names, and a couple other small fields. There is one open note field that is limited to 250 characters.  It lives in it's own subform.

The database is 14MB, size is unlikely to be a problem.

They run Compact/Repair daily, along with a backup--we have 30-50 people in the db every day. Yes, I'm aware this is a problem for Access.  Yes I've been saying that it's not built to handle this kind of abuse for four years. It's taken 4.5 years to get them near to  migrating to something else. Telling me to split the database or migrate it to something else is nothing I haven't tried to talk them into, to no avail.

Yes I tried creating a new DB and importing. I also rebuilt the field and imported the data. Neither seems to have fixed the problem.  

The data isn't linked to anything outside the database, just a relationship to the key of the primary table.

It's just not saving the data from that one date field.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34954211
<This issue is on the main data entry form, which pulls about 12 tables together.>
...Then because of this, you need to make sure that this data is not being "Orphaned"
(Data is being saved but because of a broken/incorrect link, the data is not visible.)
This may also happens if you create a button to close the form.

Also. make sure there is not a filter on somewhere
0
 
LVL 20

Accepted Solution

by:
GrahamMandeno earned 167 total points
ID: 34955518
You have 30-50 people sharing the same unsplit database?????  This will certainly cause you problems, and I'm surprised they are not even more severe!

It seems you have tried this before, but I suggest you explain to "them" in words of one syllable that their problems will be greatly alleviated, if not solved, if they allow you to split the database and give each user a private local copy of the front-end.

--
Graham
0
 

Author Comment

by:MedscapeEditorial
ID: 34956841
@Graham oh if only...

The good news is they have finally picked a new system and are apparently in training for it--what little news has trickled to me, so we're onto the sunset of this database. It was supposed to be a 6 month stop gap. In 2006.  

But I just can't seem to find the issue. Will go mucking about the back end again this evening--it ate all the code this morning just for fun.  (Fortunately *that* is a 2 minute fix these days).
0
 
LVL 20

Assisted Solution

by:GrahamMandeno
GrahamMandeno earned 167 total points
ID: 34957491
Missing data.... Disappearing code....  These are pretty good signs of database corruption and you have the perfect conditions to bring it on!

If you are really unable to try what I suggested, you could post the RecordSource SQL for your form, or if the RecordSource is a query then post the SQL of the query.

--
Graham
0
 

Author Closing Comment

by:MedscapeEditorial
ID: 35130198
Ultimately there's not much we can do to fix this--they've got to be willing to move to something else.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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.

746 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

12 Experts available now in Live!

Get 1:1 Help Now