Getting Access Error 3048 Cannot open any more databases after upgrading from 2007 to 2016 Access

Are you closing all your object variables and setting them to nothing?

 If so, this is something I'm sure Microsoft would want to look at.   If your not, then you need to.

 Also make sure you are only referencing the fields you need in queries (no SELECT * FROM)

Jim.

Is it possible that the application was also updated?
You can manually delete temporary tables and queries and refresh the tabledefs and querydefs collections programmatically.

We have downloaded an excellent diagnostic tool written by Ben Sacherich  and developed in 2008 which shows the steady decline in the number of Access TableIDs as we sequentially process invoices until a threshold is reached and the error 3048 is triggered.

 And do you have a link for this?   I found one reference on the net, and from the description given, I'm not sure the tool would be accurate.   He opens database connections rather than working with fields and shows the max around 249.  That's way off the table ID limit and my guess is that he's actually measuring something else.

  The concept is great, but I think it needs to be done differently.   Opening a series of SELECT * FROM queries and knowing the number of fields would be more accurate I think.

Jim.

You can also install a PDF printer and output your reports to it bypassing the ExportToPDF addin ...if it works as it should then something in the implementation of the export is not that "good"...or else you have some other issue in the report generating

I've received an update to the Ben Sacherich Available Connections code:

This doesn't answer your question about PDFs but I have an updated version of the available connections app that you may find useful.
See http://www.mendipdatasystems.co.UK/availab...ions/4594418530

Still no joy down here in Oz. Trying out another suggestion provoked by this from Germany:

"Every new Access version needs considerable more process memory than the predecessor. Now with Access 2019 according to my judgement you cannot run complex Access frontend databases any more.

32 bit Office installations can use a max. of 2 GB RAM in a single instance. This is the main reason why Microsoft has introduced the 64 bit version. So Excel junkies can process big datasheets.

According to my rich experience an Access instance becomes instable at the latest with a 500 MB memory rest available. Therefore in a frontend process you can actually use a 1.5 GB (of the 2 GB).

Of this 1.5 GB Access 2019 squanders 700 MB (!) on its own (without an open database). That's 330 MB more than Access 2010 which has almost the same feature scope. Back then a 370 MB were sufficient for the idle run.

So for the future the only option is the conversion to 64 bit. Otherwise with the 32 bit version of Office 2021 you will not be able to open a single database any more (because of lack of memory)."

I'm not sure this is the same problem you have encountered, of course, but the symptoms are similar, at least.

Trying to see if recompiling in 64-bit Access 2016 gives us the same error 3058. It's a long shot, but at this point in time, we'll try anything to get our customers safely back in no error message zone...

Sam

Sam,

<<32 bit Office installations can use a max. of 2 GB RAM in a single instance. This is the main reason why Microsoft has introduced the 64 bit version. So Excel junkies can process big datasheets.>>

 It's true that the memory footprint has gotten bigger, which is why Microsoft is starting to push 64 bit harder, but in Access, normally running into this problem gives you a "System resources exceeded message" and not the "Cannot open any more databases"

 Again, and this is critical; have you checked your code for the closing of objects and setting them = nothing?   Also not doing SELECT *?

 The reason I ask is that the table ID limit was published as 2048, but I've always found that it floats a bit based on available memory.   So if your chewing up more in other ways, you might back into this where as before you were fine.

and frankly this:

<<So for the future the only option is the conversion to 64 bit. Otherwise with the 32 bit version of Office 2021 you will not be able to open a single database any more (because of lack of memory).">>

  Is how rumors spread.   There's no way to tell at this time what the 2021 perpetual release will bring.  But consider that Office is built off of one code base now and the current version of Access as part of Office 365 is running fine in 32 bit for almost everyone.    It's also true that while new features might be added  by that time, but older features might be depreciated as well, so there's no telling what's coming up memory wise.

Jim.

Hello Sambodh.  A friend of mine told me that my name was mentioned on EE and I appreciate your kind words on the Available Connections utility.   I wrote that a while ago and still use it frequently.  I've got a lot of "robust" forms in my application and the users like to keep multiple forms open at once.  I'm often battling with error 3048, though often it's not even reported.  Sometimes the form just stops loading without giving an error.  You think the form load finished but then you notice that some controls are empty.  Ugh.  I've been thankful to have this dialog to track down the inefficient queries.

Someone has made updates to my 2008 version and posted it on their website.  I have not reviewed it yet.  The link you shared above was truncated and does not work.  I'll try to post it again here:  <http://www.mendipdatasystems.co.uk/available-connections/4594418530>

I'm curious to know if the 'available connections' limit is any different with Access 64-bit.  I'd refrain from quantifying them as "Table IDs" because that's something different (but still not easy to define).

Can you share a snippet of the code you are using to generate invoices?   Are you re-using the same database connection object, or creating a new one for each invoice?

Hi Ben,

Wow, great to connect with the creator of AvailableConnections, no matter what the code is really counting, it sure is helping me diagnose where the problems are!

Code snippet:

DoCmd.OpenReport "Invoice", acViewReport, "", "[ProjectID]=" & Me.ID, acNormal, intLastTechnician
DoCmd.OutputTo acOutputReport, "", acFormatPDF, strInvoiceOutputFile
DoCmd.Close acReport, "Invoice"

The issue seems to revolve around the acFormatPDF parameter, which in 2007 runs some code injected into 2007 by Save As PDF or XPS. When that same code (I assume...) is compiled and executed in 2016 CTR version, something goes wrong and every time this code is run the number of active connections decreases by exactly 16. Maybe I will never find out where that number comes from, but it is repeatable both in 32-bit CTR Access 2016 and 64-bit CTR, which we tested yesterday.

We definitely have clarity now on how to solve our problem. If we use Foxit Reader PDF Printer to generate the PDF instead of DoCmd.OutputTo acOutputReport, "", acFormatPDF there is no steady decline by 16 each time we send an invoice.

Your question about the database connection object is very relevant and I know that I've hacked code from all over Google to create this software package called Job Tracker and each code borrowing usually had its own connection in the snippet. Do you subscribe to the idea that one connection for the whole time spent in the package is the way to go, or is opening and closing as needed OK?

Great to correspond with you and it was your recommendation of EE in your blog that finally made me bite the bullet and become a paying subscriber to EE...

Sam

Do you subscribe to the idea that one connection for the whole time spent in the package is the way to go, or is opening and closing as needed OK?

I typically bind forms and controls to queries or query objects, so the concept of creating a global connection object doesn't usually apply.  I have used a global connection to make some VBA routines faster, but I can't recall using that when I also had to deal with "cannot open any more databases".  It doesn't seem like a global connection would work with the way your code is currently written.

Looking at the simplicity of your code, my thoughts are:

• If it worked in the old Access and doesn't work in the new, it could be a bug in Access or the result of less "resources" available in this version.

• Try to reduce the complexity of the query in your Invoice report.  Use the Available Connections dialog as a gauge to measure the reduction of the connection count.  Maybe you'll be able to optimize enough to stay under the resource limit.

• Try to find an alternative method of producing PDFs.  (It sounds like you have a good start on that already)

• Make a sample database that can reproduce the problem.  Share it in online forums to see others experience the same issue.

• Send a sample database to Microsoft.  Maybe you'll get lucky and they will respond.

If you think you have a solid case, and it is affecting other developers, write it up as bug/feature request and post it on the Access UserVoice site.  This is a site where the Access community can vote on user submitted feature requests.  If you don't have a case that's also going to impact others and garner votes, it may not be worth your time.   I like to mention Access UserVoice because it's the primary place the Access team is following to get user feedback.   Anyone who cares about Access should go to that site and use their 20 votes.

Hi Ben,

You are correct that we have made a good start on bypassing the faulty compiled code in 2016 which creates the error 3048. Here is where we are at today.

The code we use now:

DoCmd.OpenReport "Invoice", acViewPreview, "", "[ProjectID]=" & Me.ID, acHidden, intLastTechnician
DoCmd.SelectObject acReport, "Invoice"
DoCmd.PrintOut acSelection
DoCmd.Close acReport, "Invoice"

This works if the default printer for the user is Foxit Reader PDF Printer, since that generates the PDF. Not only is it much faster than Access 2016 but the size of the PDF is way smaller than in 2007, something which always seemed strange when receiving other invoices from other packages.

Big question now which is where we are currently stuck: How to set the default printer on our Remote Desktop Services server to Foxit Reader PDF Printer?

So far we haven't succeeded after trying a lot of different ways:

1. Set Foxit Reader PDF Printer as Default Printer in Control Panel > Devices and Printers. It doesn't take, the RDP user gets their own default printer redirected up.
2. Played around with the Server Manager>Remote Desktop Services > Collections> Job Tracker property called Client Settings, in particular disallowing client printer redirection.
3. Running a Powershell script that supposedly set the default printer on our server to Foxit Reader PDF Printer
4. Creating a registry entry at HKCU\Software\Microsoft\WindowsNT\Current Version\Windows\HKEY_CURRENT_USER\Software\Microsoft\Windows NT\CurrentVersion\Windows\SessionDefaultDevices and adding a new key      Device      REG_SZ       Foxit Reader PDF Printer,winspool,Ne01:

So If anyone knows how to set the default printer on Windows Server 20121R2 to Foxit Reader PDF Printer please inform us and we have our solution.

Thanks in advance,

Sam

Many thanks to you all for helping us out of a very serious issue that could have killed us off as a software provider!

We now working all the emailing of critical reports - invoices, quotations, invoice reminders, statements and receipts - by setting a default printer in Access 2016 for each of them, and then manually defining the required parameters in Foxit Reader PDF Writer to pick up the PDF from the default directory.

It would be nice to go a bit further in the automatic creation of the default directory for the PDF Writer, but that is beyond our current skills in VBScript or GPO settings, hence the word "manually" above.

This has been a wonderful introduction to my participation in EE and once again, thank you.

Sam

Well maybe "manually" can be done  automatically with a bit of work
UNTESTED..taken from Foxit's forum and based on this post
Goto to C:\Users\Username\AppData\Roaming\Foxit Software\Foxit PDF Creator and find FoxitReaderPrinterProfile.xml..modify the .xml to your needs and you should be OK

Hi John,

I am aware of the location of this xml profile, on our server it is:

C:\users\username\AppData\Roaming\Foxit Software\Foxit PDF Creator\Foxit Reader PDF Printer\FoxitPrinterProfile.xml

and it contains:

<?xml version="1.0"?>

-<FXCreatorData>

<General DeleteLogFile="0" DPI="600" ColorSpace="2" PDFVersion="17" IgonareBK="0" TemplateName="Standard" OpenFile="0" UseDefFileName="1" Overwrite="1" DefaultFolder="J:\"/>

<Layout FormName="A4" Orientation="1" PaperLength="2970" PaperWidth="2100" PaperSize="9" UOM="0"/>

<DocumentInfo DocCreator="" DocKeyWords="" DocAuthor="" DocSubject="" DocTitle="" AddDocInfo="0"/>

</FXCreatorData>

The reason the DefaultFolder is J:\ is because J is a mapped drive and the same for every user of a particular company. There is a DBPath environment variable which points to J: within the logon profile.

I have contemplated using VBScript in the logon profiles but have not had time to try it yet, as I need to clone our environment on Amazon AWS to do it safely. Something along the lines of what we have already in one of our logon scripts:

if not fso.FileExists(strDesktop + "\Job Tracker.lnk") Then
       'wscript.echo "creating desktop icon for Job Tracker Front End"
       Set oMyShortcut = shell.CreateShortcut(strDesktop + "\Job Tracker.lnk")
       oMyShortcut.WindowStyle = 3  
       oMyShortcut.IconLocation = "C:\Scripts\Job Tracker icon.ico"
       oMyShortcut.TargetPath = destination & "Job Tracker Front EndDBPath.accde"
       oMyShortCut.Hotkey = "ALT+CTRL+J"
       oMyShortCut.Save
       Set oMyShortcut = nothing
       'wscript.echo "copying desktop icon to Start Menu programs"
       'startup = shell.ExpandEnvironmentStrings("%USERPROFILE%") & "\AppData\Roaming\Microsoft\Windows\Start Menu\Programs\Startup\"
       fso.CopyFile strDesktop + "\Job Tracker.lnk", shell.SpecialFolders("Startup") + "\"
end if

if not fso.FolderExists(shell.ExpandEnvironmentStrings("%USERPROFILE%") & "\AppData\Local\Programs") then
      'wscript.echo "creating Syncdocs folder"
      fso.CreateFolder(shell.ExpandEnvironmentStrings("%USERPROFILE%") & "\AppData\Local\Programs")
      'wscript.echo "copying Syncdocs folder to this user"
      fso.CopyFolder "C:\Scripts\Syncdocs", shell.ExpandEnvironmentStrings("%USERPROFILE%") & "\AppData\Local\Programs\Syncdocs"
else
      ' Launch Syncdocs
      'wscript.echo "launching Syncdocs from folder"
      'wscript.echo "C:\Users\" & shell.ExpandEnvironmentStrings("%username%") & "\AppData\Local\Programs\Syncdocs"
      shell.Run "C:\Users\" & shell.ExpandEnvironmentStrings("%username%") & "\AppData\Local\Programs\Syncdocs\Syncdocs.exe"
      'shell.Run "C:\Users\%username%\AppData\Local\Programs\Syncdocs\Syncdocs.exe"

end if

What do you reckon?

At the moment we go in to each user account and manually set the Foxit Reader PDF Printer default folder to J: and each report we email out has Foxit Reader PDF Printer as its specific printer... But there are over 150 users to do!

Sam

Well i didn't have such a need for tampering with Foxit's PDF settings but since the VBscript handles just about everything why don't you include a "default" FoxitReaderPrinterProfile.xml and push it along.....normally on the next reboot it should read the settings and use J:\ as its default output drive

Not every one of our users has a need for the profile as they don't all print. Perhaps I push it out to just the subset who do print and they have their own logon scripts anyway. I might try tomorrow...

New question on the same database upgrade just posted at

https://www.experts-exchange.com/questions/29144964/Write-Conflict-after-upgrading-Access-2007-database-to-Access-2016.html

Sambodh