Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

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

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
MedscapeEditorial
Asked:
MedscapeEditorial
  • 4
  • 4
  • 2
3 Solutions
 
GrahamMandenoCommented:
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
 
MedscapeEditorialAuthor Commented:
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
 
GrahamMandenoCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Jeffrey CoachmanCommented:
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
 
MedscapeEditorialAuthor Commented:
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
 
Jeffrey CoachmanCommented:
<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
 
GrahamMandenoCommented:
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
 
MedscapeEditorialAuthor Commented:
@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
 
GrahamMandenoCommented:
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
 
MedscapeEditorialAuthor Commented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now