ACCESS keeps closing

[robirdman]

I was doing a search in Access 2007 in a table that took years to build and has over 200,000 records with many fields, keeping track of my photographic images.


Access suddenly closed. I came back and went to the table and it closed again. There were messages about where I wanted to back to, so I made a backup and opened that. Same thing. If I go to the table, Access closes.
Strangely, if I first instead open a query based on the table, it opens, but if I try to open the table, Access closes. I tried compacting and repairing but it keeps closing There was another window about running diagnostic tests. I did and no difference.
Nothing like this ever happened before. This is Windows 10.

[Micron]

The db file size is what? Current limitation is 2 Gb for any table or for the whole db also, including overhead but I'm not sure what the 2007 limitation is. It sounds like your images are not in the table - probably just file paths if anything.
You have a backup file for such valuable data, yes?
Did you try opening a new db and importing this table into it? If it's corrupted, not sure that will help, unless the table is OK but the db isn't.
If you can open a query on it, you might be able to run a make table query to create a new table. I hate to play the 'corruption' card because so often it's what people go to, but this sure seems like corruption.

[robirdman]

The database latest backup shows less than 900K. No images, just all the data about them. Now it was a cycle of opening, asks for password, type in, asks again, type in, asks where I want to backup. If I backup it closes. If I close that window instead it opens. I copied the table and pasted with a new name and the database closed. I went through the procedure and opened again and I was able to open the copy table and it seems like it has the latest entries. I go back to the original and try to open and database closes again. I rebooted and opened access and tried to open the table and there was a bar graph that looked like it was going to open, but after a few minutes, it stopped about halfway, closed and I got the attached message.

[robirdman]

I don't know what is going on now. I tried opening an earlier backup, because some later ones were saying compilation error. It opened into what appears to be a newer version of Access (says at the top that the file is Access 2007-2016 format). I could n't figure out how to do familiar things like manage or see what version.

Then I tried newer backups. One showed no tables, another Msyscompact error. I don't know what's going on.

[robirdman]

I don't even know where that version came from. Went to the desktop icon of Access 2007, and opened the last backup. Table copy opened, then I tried the original. It looked like it was opening with the bar graph, then a diagnostic window appeared. Clicked it and got this window, attached.

[isladogs]

It sounds very much like corruption. What does the MSysCompactError table show?
If other tables open without issue, then I suspect you have corrupted data in the affected table.
You may be able to find it using the query - it will probably look like 'Chinese' characters in one or more records.

Another possibility is corruption in your code. You may be able to fix that by decompiling your project. See http://www.fmsinc.com/microsoftacces.../Decompile.asp
Or try importing all objects into a new database.

[robirdman]

I am so worried about drastic steps This is a very complex database that keeps track of all my images and videos, where and when shot, along with lots of other information. Most are of living things, with scientific and common names. Other tables allow queries to arrange phylogenetically, see what was sent for publication, paid for, all kinds of data. I ran a query that sorts things phylogenetically and shows the numbers of images of each species. Over 9000 records show with numbers. Another query showed all images sent to publishers and those that were actually published and paid for. This has 70,000 entries. Scrolling through them, everything looks normal in both tables. The most recently published images are listed. Nothing seems amiss. Yet all is based on a table that keeps closing Access. A report, sorting all images of living things phylogenetically and showing the numbers of species and counts of images, of groups such as Phylum, Class, Order, and family, has the same last total that I remember, I think. The duplicate table created seems to have representatives of 250,000 records, probably about right.
Importing into a new database would lose all these relationships. When the compile says I will have a smaller database, does it mean with fewer records? Attaching pictures of a probably most complex report that keep track of all living things. Shots from top middle and end, show no problems. I was wondering about deleting the table and then renaming its duplicate to replace, but want to check the most recent backup that isn't on this drive, but another, not currently connected. I had seen a list of backups on this computer from when I was trying to solve this and backing up, names I didn't apply, like database 1, 2 3 etc. Some had 0 bytes and I will have to find one that had the Msysycompact error table to check.

[robirdman]

I opened an apparent backup named database 4 that shows the Msyscompact error. It says I don't have permission for 52 tables or queries, but I can open those I tried in the database which crashes when the one table is attempted..
I created all from scratch, no other users.
This all came about when someone liked a youtube video of some Swallowtails and asked what camera I used. I did a filter on the main table, to show all swallowtails (Papilio in species) and movie in movie field, and bam the database closed

[Micron]

Importing into a new database would lose all these relationships.

You can import relationships as well as several other items.

When the compile says I will have a smaller database, does it mean with fewer records?

No, it will only remove unnecessary overhead.
It's not possible (at least for me) to discern what is normal and what isn't based on post 3. You make it sound like you have more than one version of Access installed, or you are on a corporate machine that got updated without you knowing. If the former, you could have accidentally upgraded an old db and now you are trying to open it in an older version that can't read it, but that's a wild shot. I've never seen that error so I'm not going to be of much help I guess.

I don't see where you stated that you tried a make table query in either a new or old db. Importing a corrupt table can import the corruption with it.

[robirdman]

I tried to do the decompile, following the directions and it says it can't find C:\program
Attachments show it typed as indicated and as in path found.

[isladogs]

1. Your MSysCompactErrors table does indicate major problems. I have never seen that number of records in that table before.
Hence, in your case, I agree with Micron's comment. Corrupted data could easily get imported into a new database. Always worth trying but may not fix your issues

2. When you decompile, you need the full path to your MsAccess.exe file followed by a space then /decompile
The error is likely to be that the path is different on your machine.
If you are running 32-bit Office under 32-bit Windows the (x86) part will not apply.

[Welshgasman]

Here is my decompile shortcut path. Notice the double quotes?

"C:\Program Files\Microsoft Office\Office12\MSACCESS.EXE" /decompile

[robirdman]

I tried pasting into 'run' what you typed both with and without quotes, because, except for the (x86), you can see above the attachment to compare, is my path.
Either way it still says cannot find C:\Program. Doesn't get beyond that.

Now I am wondering about something else. Months ago I had GoDaddy install Office 365 on my computer. I thought I already had Access 2007 64bit. Other programs are 64bit. But later there was a problem and Office had to be reinstalled. They said it was because I had Access 32 and so Office was installed as 32. Sometimes, since, when I would go to open a database, it would say, Installing Access. I was worried, but then all my databases were there and intact. I'm wondering if this has any bearing, though both of those Office installments were months ago.

I had started another reply about trying to follow the debug procedure on a new backup. I went to database tools>visual basic and there was: slides14 (slides 20a_backup). Slides 20 is the database so I don't know where slides 14 figures in.
If I click the plus, then I get Microsoft Office Access Class Objects. Click that plus and there is only Report_slide labeller. If I now choose debug for any of these I can't do the next step in the link (step out) because it is grayed out. The only non-grayed are: compile slides 14, Add watch, or Clear all Breakpoints. At this point when I tried to post, I had been logged out and logged back in and everything went blank. Retyped it all. Picture attached of debug page.

[isladogs]

Office 2007 was 32-bit only. Office 2010 was the first to be sold in both 32-bit & 64-bit versions

[robirdman]

Since I couldn't do the first step of either decompiling or debugging, because it didn't correlate to the links, I did a test creating a new database and imported the problem table and then a few others and queries related to them. It kept the links but a problem is that there are subcategories at the left, instead of just an unbroken column, and listing tables multiple times according to their relationships. So only 3 tables that would show takes up 20 lines and 52 items, if I imported all would be hundreds and extremely unwieldy. would there be a method to return to single instant listing without categorical relationships?