cannot open any more database

[algecan]

Ive started to get this message quite a lot on my database (2003 SP3). It is split between a FE and BE and has one main form that has several tabed forms and sub forms on them, including a couple of reports that run.

Ive read quite a bit on this but cant seem to find a definative answer. I was wondering though, is there any way that you can find out how many connections the FE currently has? I believe the limit is just over the 200 mark but im sure i dont have that many forms/subforms/comboboxes/reports running so it would be usefull if i could run a bit of code that told me where all these connections were coming from, then i could see which connections should be closed.

Im certainly no expert in this field but i dont consider my database to be very big (11 tables) and my queries really are not very complex, often just refering to the table and filtering from a combobox on the mainpage

Cheers.

 

[MarkK]

one main form that has several tabed forms and sub forms on them

This is your trouble. Every recordset maintains a connection to the database and the database only supports a finite set of connections. And every combo and listbox opens a recordset as well as every bound form and they add up fast.
A common solution is to only load subforms that are immediately visible in the tab control. Here is the code pattern I commonly use for this purpose...

dim m_sfms as variant

property get sfms as variant
[COLOR="Green"]' exposes an array of your subforms which is constructed once on first reference[/COLOR]
  if isempty(m_sfms) then m_sfms = array(me.sfm1, me.sfm2, me.sfm3, me.sfm4)
  sfms = m_sfms
end property

private sub mytab_change()
[COLOR="Green"]  'unload every subform[/COLOR]
  dim sfm as access.subform
  for each sfm in sfms
    sfm.sourceobject = ""
  next
[COLOR="Green"]  'selectively load the subform for the current tab page only,
  'pages are referenced by name so you can change the order easily[/COLOR]
  select case me.mytab.pages(me.mytab.value).name
    case "pg01"
      sfm1.sourceobject = "SubformForTabPageOne"
    case "pg02"
[COLOR="Green"]      ' ...[/COLOR]
  end select
end sub

You'll need to change the names...
Cheers,

 

[algecan]

Thanks for that lagbolt, It sounds like a good solution. could you give me a hand with your code, as im not fantastic with VBA?

Does this...

dim m_sfms as variant
 
property get sfms as variant
[COLOR=green]' exposes an array of your subforms which is constructed once on first reference[/COLOR]
  if isempty(m_sfms) then m_sfms = array(me.sfm1, me.sfm2, me.sfm3, me.sfm4)
  sfms = m_sfms
end property

code go onto the main form?

and then this code...

private sub mytab_change()
[COLOR=green]'unload every subform[/COLOR]
  dim sfm as access.subform
  for each sfm in sfms
    sfm.sourceobject = ""
  next
[COLOR=green]'selectively load the subform for the current tab page only,[/COLOR]
[COLOR=green]'pages are referenced by name so you can change the order easily[/COLOR]
  select case me.mytab.pages(me.mytab.value).name
    case "pg01"
      sfm1.sourceobject = "SubformForTabPageOne"
    case "pg02"
[COLOR=green]' ...[/COLOR]
  end select
end sub

Go onto the the change event of each tab?

If ive got that right could you check the notes i have writeen below to see if they are correct?

private sub [COLOR=red]'[/COLOR][COLOR=red]Current tab name'[/COLOR]_change()
[COLOR=green]'unload every subform[/COLOR]
  dim sfm as access.subform
  for each sfm in sfms
    sfm.sourceobject = ""
  next
[COLOR=green]'selectively load the subform for the current tab page only,[/COLOR]
[COLOR=green]'pages are referenced by name so you can change the order easily[/COLOR]
[COLOR=red]Do i then list every other tab in the select statement and write each subform within?[/COLOR]
  select case me.[COLOR=#ff0000]'Current tab name'[/COLOR].pages(me.[COLOR=#ff0000]'Current tab name'[/COLOR].value).name
    case "[COLOR=red]tab 2[/COLOR]"
[COLOR=red]   ' Do i use sfm1 here[/COLOR]
[COLOR=red]   'or do i use what i have written in the firstpart of the code where i [/COLOR]
[COLOR=red]   'presume i change....[/COLOR]
[COLOR=red]   'if isempty(m_sfms) then m_sfms = array(me.sfm1, me.sfm2)[/COLOR]
[COLOR=red]   'to..[/COLOR]
[COLOR=red]   'if isempty(m_sfms) then m_sfms = array(me.tab1name,   me.tab2name)[/COLOR]
 
      sfm[COLOR=red]1[/COLOR].sourceobject = "Subform[COLOR=red]1[/COLOR]ForTabPage[COLOR=red]2[/COLOR]"
      sfm[COLOR=red]2[/COLOR].sourceobject = "Subform[COLOR=red]2[/COLOR]ForTabPage[COLOR=red]2[/COLOR]"
    case "[COLOR=red]tab 3[/COLOR]"
[COLOR=green]' ...[/COLOR]
  end select
end sub

I may have over thought this but im just a little bit unsure where i have to reference the relevent tabs and subforms.

I have used the above but i dont think i have done it right. In the first part of the code i have listed every tab in the array as me.tab1 etc.

Then i have put the change event on only two of the tabs (and havent referenced any of the other tabs in this part of the code just to test it)but other tabs still work and and i wasnt expecting them not to load because i hadnt put the change event on that tab to load it.

Actually now i write about this im sure ive got it completly wrong lol, its too early in the morning here.

Thanks for the help.

 

[algecan]

Ok, so i still have this problem and it seems to be getting worse.

I modified the code given above and added it to the Onchange event for my tab forms. When a user changes tab, it sets the SourceObject to "" (me.formname.sourceobject = ""). and then sets the SourceObject to what it should be for the forms that are on the selected tab.

However this has not worked and i am still getting the error. Is using me.formname.sourceobject = "" the correct way to "turn off/unload" the subforms on the tabs because if it is i dont know why i would be getting this error.
The onchange code sets the sourceobject to "" on every other form that is not in view and typically there are 3 per tab so if the code is the correct way of doing it there should only be about 3 active forms (i have very few combo boxes)

Anyone else got any other ideas of how i can unload all these forms?

 

[Simon_MT]

I had this problem with Access 1997. Was I cross! So the first thing I did was take out the RowSource in the all combi-boxes and put the Select Statement into a Module and only set the RowSource on Entry and then Nothing on Exit.

This took sometime but it did resolve the problem.

Only in one area did I have play around with the Tab, only because it was much faster due to the quantity of data. So I did this:

Function ArtistsEntry_PageNo()
'       This function is designed to speed up the Artists Forms, by making the History and Consignments to demand only
    With CodeContextObject
        .[Page No] = .[TabCtl0]
        If .[ACFlag] <> True And .[Page No] = 3 Then
            .[ACFlag] = True
            .[Artists Consignments].SourceObject = "Artists Consignments"
        End If
        If .[OHFlag] <> True And .[Page No] = 4 Then
            .[OHFlag] = True
            .[Artists Originals History].SourceObject = "Artists Entry Originals History"
        End If
    End With
End Function

This is called only once as we wanted to preserve the record positioning on each of these subforms.

Simon

Simon

 

[SOS]

Are you using any code like

Dim db As DAO.Database

Set db = Currentdb

or using ADO

Dim cnn As ADODB.Connection

Set cnn.ConnectionString = "xxxxxxx........etc."

I've known this problem to happen when you try to use anything BUT

Set cnn.Connection = CurrentProject.Connection

(just a wacked out thought... )

 

[algecan]

I had this problem with Access 1997. Was I cross! So the first thing I did was take out the RowSource in the all combi-boxes and put the Select Statement into a Module and only set the RowSource on Entry and then Nothing on Exit.

This took sometime but it did resolve the problem.

Only in one area did I have play around with the Tab, only because it was much faster due to the quantity of data. So I did this:

Function ArtistsEntry_PageNo()
'       This function is designed to speed up the Artists Forms, by making the History and Consignments to demand only
    With CodeContextObject
        .[Page No] = .[TabCtl0]
        If .[ACFlag] <> True And .[Page No] = 3 Then
            .[ACFlag] = True
            .[Artists Consignments].SourceObject = "Artists Consignments"
        End If
        If .[OHFlag] <> True And .[Page No] = 4 Then
            .[OHFlag] = True
            .[Artists Originals History].SourceObject = "Artists Entry Originals History"
        End If
    End With
End Function

This is called only once as we wanted to preserve the record positioning on each of these subforms.

Simon

Simon

I tried changing the combo boxes as you suggested but it didnt work, it think its because ive only got about 4 its not making a difference. I need something thats going to turn all the unused forms off.

I tried deleting a tab that had the most forms on and now it works fine but the sourceobject command is not stopping me from getting this error.

 

[algecan]

Are you using any code like

Dim db As DAO.Database

Set db = Currentdb

or using ADO

Dim cnn As ADODB.Connection

Set cnn.ConnectionString = "xxxxxxx........etc."

I've known this problem to happen when you try to use anything BUT

Set cnn.Connection = CurrentProject.Connection

(just a wacked out thought... )

I only use it once or twice i think but will give it a go, cheers.


Does anyone know if there is a way to find out where what all the 255 connections are to the database