Solved

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

Posted on 2011-02-10
10
280 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
[X]
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
  • 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
Industry Leaders: 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!

 
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
 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

690 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