After Compact and Repair, I get an error message!!

[LeonS]

Hello! After I close everything and go to Database Tools and press Compact and Repair. All seems well but an error message comes up saying the the query is too big, or the result is too big. As far as I am aware, I am NOT running any queries (having closed everything before launching CaR). The message comes up the (3) times before going away and the database opens!! Thank you for any advice!!

[NoellaG]

The query message could origin from the compact & repair operations. It uses queries to copy the data. How big is your database?

[LeonS]

A staggering 112,000 !! I do not know how it got that big! The images are NOT stored in the db but in a separate file in the same folder. 12 tables with 4 records (for testing) in the Master1, and small tables or tables that are created by the Queries (27), Forms (24 - 3 main forms the others are for showing information created by the Queries) and Macros (24). I hope this helps. Leon

[isladogs]

Is that a total of 112,000 records or the file size is 112 MB? Either way, that is nowhere near the limits of an Access database.
If you are still having problems, try importing all objects into a new blank database

[LeonS]

Hello! 112 Mb !! I still think it is big. The database is (I think!) very simple. What takes up the space? Thanks for the suggestion!! Leon

[NoellaG]

I think that 24 forms can take up quit a bit of space, especially if they contain fixed pictures like logo's.

[LeonS]

Some of the forms are, on screen, quite small. The are very simple and are designed to show the results of a query in a "nice" way. I have exported almost all of the objects from my original to a new blank database. The size has dropped from the 112 Mb I told you about, to 65 Mb!!! I am now going through the new version to see if everything works. Linking the tables etc etc. The logos I am using are all small 50-100 kb .jpg files. 15 in total. I keep my people images in another folder, not in the database. In the new database, there is the same problem with the "not clean" opening of the First Page on opening. Going back to that page from another form and there is no problem. It seems like an "starting up" problem and not an opening problem. And the error message after CaR is still there. Cheers, Leon

[isladogs]

The maximum size of an Access database is 2 GB but it is usually wise to limit the size to no more than 1 GB to allow space for temporary objects during action queries.
The fact that you have recovered around 60 MB is that was space previously allocated to deleted record or deleted objects.

In most databases, tables occupy almost all the file size. Splitting a database with all tables in a backend has many advantages including significantly reducing the size of the frontend.

If you use a lot of images in forms/reports, make sure these are small files and use linked images rather than embedded.
Access doesn't handle JPG images well due to their lossy nature. Embedding them often increases the Access file size by far more than the image file sizes.
PNG images are also small but are better quality and do not cause an equivalent bloating effect

[LeonS]

Many thanks for that. I will try to change the image files to png. Currently, some are and some are not. I will convert those from jpg to png. I do plan to split the database into BE and FE But I do not seem to be able to get definitive advice. Leon

[isladogs]

There is plenty of advice available online on splitting databases. The process is normally very straightforward.
However if necessary, I suggest you post any questions about splitting a database in a new thread

[LeonS]

There is plenty of advice available online on splitting databases. The process is normally very straightforward.
However if necessary, I suggest you post any questions about splitting a database in a new thread

Getting back to the origin of this thread, the problem is still not solved.
As suggested, I have created a new blank database and exported (almost) all of the objects into the new database. The objects I did not transfer where not needed. The size went from 112 Mb to 65 Mb. Result!!
However the problem persists. The database is "trusted" - I checked that through a recommended AutoExec macro. The database is only 65 Mb, well within the 2GB maximum. My computer has masses of spare space. But the "query message" comes up 3 times. That is if I open it from the TaskBar. If I open it from a Desktop icon, there is no problem. Very frustrating. I can operate like that but my planned users may not. All suggestions welcome!!

[NoellaG]

Hi, can you give us the exact error message and number?

[LeonS]

This happens after the database has be through the CaR process. The next time it is opened from the TaskBar, this appears. I hope this helps. Leon

[LeonS]

I did copy and paste a screen print but it seems to have disappeared!! I will try again.

[NoellaG]

Is there somewhere in your database a form based on a query that combines several tables without any relations? In this case you get a cartesian product of all records which could result in a very large dataset that could give this message.