Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Problems after repair and compact

Problems after repair and compact

Problems after repair and compact

I was having some issues with errors in my database and performed a repair and compact on it.
I then began getting some errors with regularly uses routines. They seem to be focused on a form that opens up on top of another form. The first was in the macro it saves the record. It states record not available. then it will not let me enter or delete any info from the controls.

Any Ideas?

RE: Problems after repair and compact

It also says this recordset is not updateable.

RE: Problems after repair and compact

I would try to open the record source behind the form in datasheet view and scroll through all the records. There might be some corruption.

Hook'D on Access
MS Access MVP 2001-2016

RE: Problems after repair and compact


It states record not available. then it will not let me enter or delete any info from the controls
It almost sounds as if the form or underlying query is not update-able. Without seeing the code or the query there is no way to answer. I would look at the recordsource and verify that the query is updateable. Then verify that the form is updateable. There are lots of reasons that the recordsource can not be update-able. It is also possible that your code has made the form non update-able. There could also be corruption as Duane states.

RE: Problems after repair and compact

The database has been in operation since 2004. It's corruption. A new adventure for me.
Not sure what is causing it. I recently built a second database. It uses (as in links to) a couple of the tables in the original database that is having the issues now. Is this possibly a cause? I did it this way because the data needs to be current in both places.
The database with the problem had 44771 records. I do not or rather haven't repair and compact on a regular basis.

RE: Problems after repair and compact

I have seen some issues years ago with a record or two corrupt. I found I had to create a couple maketable/append queries to copy the good records to a new table. I never new the cause but perhaps two people were editing the same record at the same time or someone stopped Access without exiting.

Hook'D on Access
MS Access MVP 2001-2016

RE: Problems after repair and compact

I would not assume anything and check the behavior of the recordset now before assuming corruption. Sometimes data changes that breaks things, usually not if properly designed but sometimes data mysteries happen. That said, with all recovery attempts backup the original and try stuff...

Generally I would try the decompile command line switch on the Front End, compile everything.

If that doesn't work or just to try more stuff all at once to be over it, I would import all objects in each back end and front end to new files. Note sometimes corruption follows objects on import to a new file.

If that doesn't work two solution paths based on 1 data corruption or 2 application corruption.
1) You could try dumping the tables behind the record source of the form to text, creating new tables in a new file and importing the text data and remaining tables. Ultimately you could try this with all tables and not just the suspect ones. I have never had to go down this path but would seem to be the cleanest / most straight forward.
You might not be able to use the dataset as a whole. I have seen in smaller tables things like #Error in fields once or twice... Just manually add correct records and delete the problem ones. According to something I read a long time ago, Access won't tab out of a field with error data... the solution is to leave something holding the tab key down and let it run overnight... Obviously, a locked office or using a local copy on a laptop at home are likely the way to go here. I do not know if the tab not moving is true or if it is true with current versions so if this works, I would like to know. I have not seen it in a really long time.

2) Use the Undocumented SaveAsText and LoadFromText VBA procedures to save out suspect objects to text file and reload them... Again in a clean file importing other objects separately. And again ultimately you could try this with all objects and not just the suspect ones. The intellisense works on those procedures which should be enough documentation or easily found elsewhere if you need more help.

Good luck! And do not forget the old stand by, restore to the last known working version.

RE: Problems after repair and compact

I was able to just delete the #error fields and export the rest to a fresh table.
Thanks for all you help!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close