Database already exists / System resource exceeded error messages

[Sdata]

I have been running Access 2016 for the past 2+ years without much issue. I run the same queries in a macro once a week to process new records. Our records are stored over 10 databases linked with a key field. Most databases are currently around 1GB. There are about 400K records in each database.

My databases are automatically compacted and repaired when they close.

A couple of months ago, I started getting a "Database already exists" warning when I closed any Access Database. I had not seen that before. I don't believe I did anything to cause this. This week many of the queries that worked fine for years started having "System resources exceeded" errors.

What I have done so far:
1. Reduced the size of the databases by 25% by moving older records to separate archive databases.
2. Simplified a few complex queries into basic tasks. This works some but I would need to write hundreds of new queries and the old ones had been working great for many years before now.
3. Reinstalled Access. This will allow an extra 10 queries to run but eventually system resources get exceeded.
4. Tried setting the MaxLocksPerFile value to 15000; 25000 & 25000 in the VBA editor. Since I am not a programmer, I think I did it correctly but not certain.
5. Used Task Manager to find any other programs running and eating up memory- there were not any.
6. Left computer off and unplugged overnight to drain any RAM- not sure if this really works but it fixed many computer problems in 1995. Like the reinstall of Access, an extra 10 queries ran before hitting system resources exceeded.
7. Scoured the internet for help and landed here.

Could the two warnings be connected? Any idea why queries that worked great for years are now exceeding system resources?

Thank you

 

[isladogs]

First of all I would get rid of the automatic compact and repair as it can cause corruption.
Only compact when your app exceeds a specified size and make a backup first to be safe.

As you may have corruption, I would backup then DECOMPILE your application.
http://www.fmsinc.com/microsoftaccess/Performance/Decompile.asp
This only takes a few seconds and remove any compile code including any that is corrupted. Then recompile your code using Debug...Compile

Personally I wouldn't have archived records...unless your apps are close to the 2GB limit.

Are these multi user apps? Are they split with each user having the own copy of the FE? This is ESSENTIAL.

Increasing MaxLocksPerFile can help but I think 25000 is too high and may have side effects.
You can also increase MaxBufferSize settings in the registry
Finally check for available connections using this app to see whether you are getting close to the 255 connections limit http://www.mendipdatasystems.co.uk/available-connections/4594418530

If none of the above help try copying all objects into a new empty database...but i doubt that will be necessary.

The list of similar threads at the bottom of this page may also be useful

 

[arnelgp]

consider moving your data to a bigger sql, like mssql or MySQL. there are free versions of this.

 

[isladogs]

Thanks for adding that point Arnel ... which I had intended to do as well.
Using e.g. sql server will offer greater security and stability.
The free Express version of SS has a limit of 10GB compared to 2GB in Access.
Paid versions can go up to 1TB I believe.

 

[Sdata]

Isladogs and Arnel, Thank you for your help.

I am the only user and all the databases are on one computer. I stopped the autocompact, kept the MaxFileLocks at the default setting, decompiled, checked for to many connections. Not much changed.

I downloaded Microsoft SQL Server 2017 Express and SQL Server Management Studio. That got me much further through my macros before Exceeding System Resources. I have copied the worse offending code into an empty database and all but one query worked. Not sure yet if the entire program copied and run into the empty database would cause the error earlier.

I think the issue can can be fixed if I simplify the queries that still cause the error into several steps. I will let you know.

If you have any theories why my macros worked great for a couple of years before the getting the system resource exceed error without changing anything, I would be interested to hear them. Thanks again.