Problems after repair and compact 1

[LimitedTech]

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?

 

[LimitedTech]

It also says this recordset is not updateable.

 

[dhookom]

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.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MajP]

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.

 

[LimitedTech]

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.

 

[dhookom]

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.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[lameid]

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.

 

[LimitedTech]

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