Can't open any more databases

dcjones · Apr 25, 2004

Hi All,

Major Problem.

I have been building my database over the last few weeks (with a lot of help from this Forum, thank you) and I am just about to go from developmentg to production and I am getting an error.

I have my main form with 16 subform in Tab format at the bottom of the main form.

On each subforms I have a Combo box where the user can select items form the dropdown lists.

Suddenly I am getting an error when the combo boxes are used.

ERROR: Can't open any more databases.

I have search the Forum but can't find any information regarding this error.

I need some big help with this one so if anyone can please reply.

Kind Regards, keep safe and well.

Dereck

namliam · Apr 26, 2004

Not really any help i can offer except... 16 subforms geez that (much) more than i have ever needed and i have build some complex M***** F****** ... I would have a rethink about your design....

BR

The Mailman

___ · Apr 26, 2004

I think you've reached the forms control limit.

dcjones · Apr 26, 2004

Hi All,

I have solved the problem (Quick Fix) by removing the linked tables and exporting the tables from the BE to the FE. With the tables sitting side by side with the other objects the database work with no errors. This is not ideal but it works.

I have search the web and my manuals and hve discovered that the error is caused by the lack of connections to the Jet Engine. The limited is 255 and the way I am reading the manuals, each time a ComboBox is clicked another connection to the Jet Engine is required.

I don't thin I am useing all 255 connection.

The other thing it may be is looping code that is calling for a connection each time it loops without closing the previous connection.

Does anyone have any comments regarding this.

Kind Regards, keep safe and well.

Dereck

Bill Manning · May 14, 2010

Hi there ... first time posting, just joined. I am chasing the "cannot open any more databases". I was thinking just about "databases" but have read above that it also includes forms, subforms, objects [such as combo boxes, subfrms etc] Correct?
Is there anyway to know how many are open? Can I only solve it by reducing the number of subforms to lessen complexity?

And, is there any way of closing these to reduce teh number of "open" as we move to different main menu modules?
Thanks
Bill

boblarson · May 14, 2010

It can also be due to you trying to use things by not using the internal connection. A common error I see is people opening a connection for an ADO or DAO recordset and not using the internal connection (CurrentProject.Connection for ADO and CurrentDb for DAO).

Bill Manning · May 14, 2010

hmmm .. thanks for the help. Interesting isn't it when we have put food on the table for so many years and come across things that we never have learned or even approached!! My "teacher" never spoke of "internal connection" or used the terms ADO/DAO recordset yet I create [Dim rstxxx as Recordset] them all the time. Time to dig into that area!

Bill Manning

Simon_MT · May 14, 2010

Another trick is to provide the recordset only on demand. i.e. when the Tab is being used populate the subForm. If it hasn't got the focus it doesn't need to have any information.

Simon

Bill Manning · May 14, 2010

The module/area has 9 subfrms with different sorting. My technique has been to visible=false all but the one they choose with frame choosing and radio buttons. I guess I need to cut down on so many options for them. They sure like all the options but it's causing heartburn for all. IS there a way to reduce these connections when move off the main form?
Bill Manning

shadow9449 · May 14, 2010

boblarson said:
It can also be due to you trying to use things by not using the internal connection. A common error I see is people opening a connection for an ADO or DAO recordset and not using the internal connection (CurrentProject.Connection for ADO and CurrentDb for DAO).

Hi Bob

Could you please elaborate on this? What is the wrong way to open a DAO connection?

Thanks

SHADOW

boblarson · May 14, 2010

shadow9449 said:
Hi Bob

Could you please elaborate on this? What is the wrong way to open a DAO connection?

Thanks

SHADOW

Sure - When people try to use

Dim conn As DAO.Connection

Set conn = "Then They have a connection string here which refers to the database path"

When it should just be

Set conn = CurrentDb

but in reality they don't need that connection object, they just need

Dim db As DAO.Database

Set db = CurrentDb

shadow9449 · May 14, 2010

boblarson said:
Sure - When people try to use

Dim conn As DAO.Connection

Set conn = "Then They have a connection string here which refers to the database path"

When it should just be

Set conn = CurrentDb

but in reality they don't need that connection object, they just need

Dim db As DAO.Database

Set db = CurrentDb

Sounds like they are doing more work than necessary! Even in a split database, the front end is aware of the location of the back end so I don't know why they wouldn't just do it the easy way.

Did you imply that doing it as Set conn = "<path to database>" could cause the error that Dereck was mentioning in post#1?

@ Bill:
I want to add my 2 cents here: I've been finding that using DAO recordsets is a far better way to find information than using Dlookup. I've put in timers into my code and I have seen repeated DAO calls run at half the time than the same Dlookups. As well, Allen Browne claims that using Dlookups too much can lead to the "Can't open any more databases" error. I've been using his Elookup which has the same simplicity as Dlookup but uses DAO.

http://allenbrowne.com/ser-42.html

SHADOW

boblarson · May 14, 2010

I'm addressing Bill Mannings post because the Derrick post is 6 years old. Bill should really have created a new thread for this.

And yes, I've seen it where people try to use an "outside" connection by referring to the database path instead of just using the internal components.

As for Bill's problem, it would be much easier to tell, of course, if we just had the database to look at (minus any sensitive data of course).

shadow9449 · May 14, 2010

boblarson said:
I'm addressing Bill Mannings post because the Derrick post is 6 years old. Bill should really have created a new thread for this.

Good point

I've changed "Dereck" to "Bill" accordingly.

And yes, I've seen it where people try to use an "outside" connection by referring to the database path instead of just using the internal components.

Good to know...it was worth reading today just for this tip!

Cheers

SHADOW

Can't open any more databases

dcjones

Dereck

namliam

The Mailman - AWF VIP

___

¯¯¯¯¯

dcjones

Dereck

Bill Manning

New member

boblarson

Smeghead

Bill Manning

New member

Simon_MT

Registered User.

Bill Manning

New member

shadow9449

Registered User.

boblarson

Smeghead

shadow9449

Registered User.

boblarson

Smeghead

shadow9449

Registered User.

Similar threads

Users who are viewing this thread