How to tell when an Access Database has maxed out on total number of objects?

[Fran Lombard]

I just had a rather fustrating experiece
New code and controls added to a screen were not saved - upon completing some work compiling the code, closing and saving the screen. This happened twice in a row on the same screen so i was doubly careful to save after every small change. Still after closing the form and reopening, the new work was gone.

The i tried a compact and repair an Access barked out a message about unable to perform save operation out of memory.
Something like that.

Now i thought i read somewhere there are limits to total objects and im thinking if thats the case i may have hit this.

Does anyone know if these limits im referrong to exist?
If so is thete a way to test if ive hit this limit?
Or, Does this sound like some other issue?

Ive grown reluctant to attempt to do anything untill i get this db stable.

Thanks
Fran

 

[Deleted member 147267]

Does anyone know if these limits im referrong to exist?

Yes. Google can be your friend if you let it. Here's just one of his/her comments:

Microsoft Access 2016 Maximum Limits and Specifications

Microsoft Access 2016: maximum limits and specifications ... Microsoft Access training in Toronto, Ontario, Canada. Our instructor-led courses are offered in downtown Toronto.

www.avantixlearning.ca

 

[The_Doc_Man]

It is certainly possible that you have created a database with too many objects, but there are specific error messages for this. To find your limits, do this web search: "Access nnnn specifications" where nnnn is the year number, as 2010 or 2016. You need to be specific with the year because some of the limitations have changed over the years.

How big is the database file you are trying to manage? In theory you can have up to 2 GB of content, but in practical terms there are limits to the number of items you can have.

Before you do any more damage to your file, which MIGHT be corrupted, make a copy of it for backup purposes. Then in the Database Tools section there is a way for you to do imports and exports of items. So... create a new empty database. NOTHING in it except the normal stuff created by Access to initialize that database to a usable state. (The "MSys" tables and a few other things are the "stuff" to which I refer.)

Now from the Database Tools section, from the NEW copy try to import elements from the old database. The import tool is multi-tabbed and lets you pick tables, queries, forms, etc. on separate tabs. See if that gives you something workable.

Then you have to look at how many items you have. There is a dependencies tool in the Tools ribbon that lets you identify items that depend on other items. If you have old items that no longer have anything that depends on them and you never use them yourself, consider dumping them. The only issue is that if you really have "blown the limit" then the DB you build this way won't be much better. Which is why some trimming might be a good idea if it is at all possible.

 

[onur_can]

Way to exceed the ms access 2gb limit
https://dba.stackexchange.com/questions/167731/bypass-ms-access-2gb-file-size-limitation

 

[Fran Lombard]

@The_Doc_Man Thanks again.

Im not sure if those db tools are available to me. Currently by db is an Access 2007 adp or SqlProject file connected to a Mssql 2012 Express db server. Since Access 2007 is not certified for Mssql 2012 some of the functionality gets greyed out or is just not available in a Project file.

I will check it out none the less.

Regarding size of the db - it no where near the 2gig limit since it really just holds forms, reports anc code. Maybe 100 meg. But thats all "code objects"

I think im going to put this down for now and enjoy Easter with the family and get back to this with a clear head.

Thanks again
Fran

 

[Pat Hartman]

@Fran,
It might be time to consider converting from the .adp to an .accdb. A lot of the code you wrote for the .adp will not be needed so it is easier than going the other way. The .adp was never widely adopted since linking to ODBC data sources was much more flexible and not sufficiently slower to justify using an .adp. The .adp gave you ONLY SQL Server and ONLY specific versions at that whereas stanard ODBC gives you a wide range of databases you can link to including Jet/ACE which the .adp didn't allow you to link to. 2007 is 13 years old and probably has a retirement date in the not too distant future.

 

[isladogs]

The full list of Access specifications can be found at https://support.office.com/en-us/article/Access-specifications-0cf3c66f-9cf2-4e32-9568-98c1025bb47c
I agree about converting your ADP file to ACCDB. Recent versions of Access from A2013 onwards cannot even open ADP files.

 

[zeroaccess]

I sometimes find a C&R not enough, and do a full decompile and recompile. Directions here:

Follow these steps to customize the Send To menu with an Access Decompile shortcut:

1. Create a shortcut to the Access executable.
2. Append the /decompile flag in the Target for the shortcut. The shortcut will look like this:
3. "C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE" /decompile
4. Open Windows Explorer and paste the following into the address bar:
5. C:\Users\%username%\AppData\Roaming\Microsoft\Windows\SendTo
6. Copy the shortcut you created into the SendTo Folder, and name it Access Decompile or whatever is recognizable to you.

Then:

1. Backup your database.
2. Open your database using the bypass key and Compact & Repair, then close.
3. Holding the bypass key and using the shortcut created with the instructions above, decompile your database (right-click your database, then Send To -> Access Decompile).
4. Close that instance of Access.
5. Open a new instance of Access and open the database you just decompiled, but BE SURE YOU BYPASS ALL STARTUP CODE (i.e., hold down the shift key). If you don't do that, go back to step 3 and try again, since if the startup code runs, your code will recompile before you are ready to do so.
6. Compact the decompiled database (and be sure you hold down the shift key so that it bypasses the startup code; see #5).
7. Open the VBE and on the Debug menu, choose COMPILE [name of project].
8. On the File menu, save the project.
9. Compact again.

Why are all these steps necessary?

Because you want to not just decompile the VBA, you want to make sure that all the data pages where the compiled p-code (packed code) was stored are completely discarded before you recompile.

I also recommend:

• in VBE options, turn off COMPILE ON DEMAND
• in the VBE, add the COMPILE button to your toolbar.
• compile often with that button on the toolbar, after every two or three lines of code.

IMPORTANT: Only do C&R and Decompile operations on a copy of the database. If it blows up, just make another copy and try again.