database grows dramatically during usage and "property value too large"

Question

We have an Access database created in 2004.  The database will grow during the day from 9.5 Meg to 1.5 Gig.

Upon compact and repair, it will return to <10 meg. 

Also, we are having "Property value too Large"  intermittently on 2 queries.  The first query is based on 2 tables, and the second query is a crosstab based on the first query and 2 different tables.

I believe the 2 issues to be related, but have investigated everything I know to look at.

Basically, the tables format is correct and all the queries have the correct format for the fields.

the database is used daily by 4 people, and all the issues just started about 2 monthes ago.

The database has multiple users and has not been split.

I have looked at splitting it, but I will need to make some other changes before I can split.

any help appreciated

Answer 1

If you haven't already seen, you may find the following links informative

• http://www.devhut.net/2017/04/20/access-best-practices-and-troubleshooting-steps/
• http://www.devhut.net/2017/04/09/setting-up-an-ms-access-database/

Answer 2

"The database has multiple users and has not been split."

There's your problem.  You can't really do any other troubleshooting until this is addressed.

"I will need to make some other changes before I can split."

One does not exclude the other.  You simply need a deployment script that pushes out updates to your users, then you can continue development as required.

Answer 3

I will follow up on the 2 links

thanks for your help!

Answer 4

OK

thanks for the help.

I have split the database.  After splitting, I had to rebuild 6 of the queries.  I was still receiving the error message about  "Property value too Large". 

I am no longer receiving the error message.

I will test for a week, then distribute to all users.

I have created a new folder on our intranet that contains the back end.  the front end is in my local documents folder.

Answer 5

Daniel is right. You'll get nowhere before you have split the database. In fact, you should feel lucky it has worked (more or less) until now.

The frontend you can distribute with a shortcut on the users' Desktop and a script:

Deploy and update a Microsoft Access application in a Citrix environment