2

I have an MS Access (2013) application with a split database. Everything seems to run smoothly except for occasionally I will get Error 3048: Cannot open any more databases.

The error occurs when the front end tries to run vba code which involves pulling data from the back end and will stall on any line with: Set DB = OpenDatabase() or DoCmd.RunSQL() commands.

The strange thing is that this error seems to be time based. I can access the back end hundreds of times without error if I do it quickly enough but after some time has passed (~1 hr) the error shows up. In fact, I can open the application and leave it running in the background (with no code running) then go back into it after an hour and I will get the error the first time the code tries to open the back end.

I've searched the length and breath of this site and google for solutions so I know this error has been addressed before. To save people reiterating the usual fixes I will list what I've tested for so far with no success:

  • Recordset limit: I'm not leaving any recordsets open, every time I open one I make sure to close it. The same for the databases. All my requests to the back end are done via 3 or 4 vba functions and each of these has a Rec.Close or DB.Close corresponding to every OpenRecordset() and OpenDatabase() and I never have more than 2 recordsets open at a time.

  • Control limit: I have 151 controls on the biggest form in the application so I should be below the limit (I believe this is 245 for a single form?)

  • Corrupt database: I've copied all my forms and code to a new Access database and run a Compact and Repair.

  • Machine Issue: I've tested the application on several machines and reproduced the same error.

Anyway with most of the above situations I would expect the application not to run at all, instead of running fine for a set amount of time and then crashing.

Some other points of note:

  • Citrix Users: The users are split between normal windows machines users who are experiencing this error and others who are using the application through a virtual desktop software (Citrix) who are having no issues. Unfortunately I don't know enough about this virtual desktop to really work out what that implies.

  • Background vs Foreground: Some users have claimed that the application only crashes if it has been running for a long time AND they switch over to another program and switch back. I've confirmed that simply switching between the application and other programs doesn't cause it to crash but haven't yet been able to leave it running in the foreground long enough to confirm if it crashes without switching between programs.

I've been struggling with this for days, anyone able to help me out?

Jain
  • 21
  • 2
  • Are you also setting Rec = nothing and DB = nothing at the end of every VBA call? Also make sure you are using dbOpenSnapshot if you are not editing the records returned. – JJ32 Oct 06 '16 at 15:21
  • Yes, I'm setting everything to Nothing at the end (I meant to mention that). I haven't been using dbOpenSnapshot, I'll try changing to this where I can but some of my functions DO edit the records in the database and so I wont be able to change them. I would expect these to still cause the issue if that is the case... – Jain Oct 06 '16 at 15:45
  • Also make sure to set RecordsetType to snapshot in any forms where you do not expect to edit data. See http://stackoverflow.com/questions/30519628/getting-error-3048-cannot-open-any-more-databases. The fact that it only happens when VBA is called makes me think something isn't being closed out or you are stuck in an infinite loop somewhere. – JJ32 Oct 06 '16 at 15:52
  • This is what I thought as well, but can't find anything that isn't being closed or any loops. Also it can break on a first attempt if I leave it open long enough beforehand or else run perfectly for several iterations if I do them straight after opening - this would seem to indicate that there isn't a loop or unclosed recordset. – Jain Oct 06 '16 at 16:02
  • The other thing that's curious is Citrix users do not experience this problem. Have you tried running a copy of the front-end locally on the other windows users desktops? (I'm assuming they are running the front-end on the network). I'm not a network expect, but you might want to consider using terminal services or some such if this approach helps. Or you might want to use the approach in the link and use disconnected recordsets if that's less headache. – JJ32 Oct 06 '16 at 16:08
  • Ok I've tried all suggestions made here and then some and this is just getting more frustrating. I created a new database with 1 table and 1 form. The form just had 1 combo box and 1 label and some code which looked up the value in the combo box in the table and found a corresponding number to print as the label caption. So there's a line in the code to to the effect of: `Set Rec = CurrentDB.OpenRecordset("SELECT [Number] FROM TableName WHERE [Name] = '" & combo1.value & "'")`. I then saved this database locally on my machine. But I still get the same error message after about an hour. – Jain Oct 21 '16 at 14:36
  • Just for clarity, it cannot possibly be a network issue or a control limit issue or a recordset limit issue (the error will appear even if it's the first time the code has run). Really stumped at what else it could even be. – Jain Oct 21 '16 at 14:38
  • Very odd. Would it be a violation of policy to send a copy of the database (frontend only)? I'd really be curious to see what's going on. – JJ32 Oct 21 '16 at 16:28
  • Also look through [this thread](http://stackoverflow.com/questions/12579186/ms-access-2010-cannot-open-any-more-databases) if you haven't already, might give you some ideas. – JJ32 Oct 21 '16 at 16:39

0 Answers0