Applaction Crashes when opening Table

[R_J_Potter@msn.com]

Hi,Folks
I've got a hard one to resolve in MS Access 2010
I have a Table Named Statements
I have a query Named qStatements
I have a Form Named frmStatements

The Table has:
one date Field,
Six Currency Fields and
One Memo Field
I did not know I had a problem until this week when I tried to Open the Table.
Data entry has always been made on frmStatements (This works A-Okay)
Then I wanted to modify the Table this week, My Application Crashed as soon as I opened the table, and it also
crashed when I tried to view the data by running the Query.
It only works when running frmStatements, Data Source is qStatements.
I can open the Table in Design Mode okay. I do not see anything incorrect in the table setup.
I've gone back to ALL my Application backup files and the problem is in all of them.
I'd like to recover the data in the Table. Any suggestions as to why Opening a Table causes the App to Crash and
maybe a way to recover the data. I can create a new table A-Okay, Just a lot time to pull documents to restore a new table.

Oh, by the way, The message box that appears:
[Microsoft Access has Stopped Working] Windows is checking for a solution to the Problem..
A second message box appeared:
[Microsoft Access has Stopped Working] A problem caused the program to stop working correctly. Windows will close the program and notify you if a solution is available...

 

[JHB]

Have you tried a "Compact & Repair"?

 

[R_J_Potter@msn.com]

Yes I did. That was the first thing I tried.

Thank you for reviewing my issue.
Ron

 

[JHB]

You are not writing what you've tried to solve the problem, so ..!
Have you tried:

• to import the table into a new database?
• create a "Make table query" based on the problem table?

Are you able to scroll through all the records using the form?

 

[The_Doc_Man]

Can you directly open the query in datasheet view?

Because if so, your path to recovery is to build the empty table first, then do an INSERT INTO the empty table from the query.

How big is the Memo field, by the way?

 

[R_J_Potter@msn.com]

I did create a Make table query in the Application, but it crashed also,
BUT

I did create a blank database and imported the problem Table, query and Form.
This morning I created a Make Table Query in the blank database.
Yes, the Table did Open, as soon as I scrolled thru the Table, It Crashed.
BUT again,

Well, I open the table again, this time I Selected all the fields and Selected COPY.
I Pasted the data into an Excel worksheet.
I think I found the problem in the Excel worksheet. There were two records with no transaction Date (Key Field) with several Currency Fields with out of range currency amounts, i.e. 467,578,397,291,394.00

I deleted those two records and imported the excel sheet into a new Table.

All is well now, thank you very much. You helped me to save the day.
Ron

 

[The_Doc_Man]

R J - The lack of a transaction date shouldn't have crashed Access. It should have just complained about key-field violations and refused the operation. However, don't know what happens for the currency case when that overflows. On the other hand, I am a bit confused about what was actually happening here. Was it that you were trying to import text data from somewhere? Otherwise, what you described can't happen exactly as you described it because Access should have kvetched loudly the moment of the original faulty data entry. Your "table open" operation should not have been the first time Access saw that bad number.

 

[R_J_Potter@msn.com]

Hi, Thanks for your comments.. I really don't know how this table got corrupted. I agree that Access should have complained that no date was present. One possibility is that I may not had that field set as KEY. The oldest backup of the app goes back to 2010 and the problem existed in that file also.

The data for this table is manually entered monthly on frmStatements. Nothing is or was imported. Those two bad entries I found after I created that Make Table query and opened the new table. At that point, the new table opened as long as I did not scroll down into the data. I selected all fields and selected Copy. I pasted into Excel. Mid way into the excel file I found those two records. The "Date_Trans" value was #NUM!
When the frmStatements was opened, those two bad records never displayed...
After I imported the excel sheet into a new table, the new table functions A-Okay...

I still do not know exactly how the problem got created. Thanks for you comments and
I'm also a GrandPa 3 times and I've been retired from a major oil company for almost 14 years. I been to New Orleans many times.