Solved

adodc too many active users

Posted on 2000-04-06
26
361 Views
Last Modified: 2013-12-25
Hi!!

    At the run time of a program, there is an error i can't fix.  It start with a windows with the adodc name and it says too many active user.

    When I click ok, another window appear. It says runtime error '-2147467259(80004005)'

    I don't know why it append or why and I'd like to learn how to fix this error
0
Comment
Question by:gfaucher
  • 13
  • 12
26 Comments
 
LVL 1

Expert Comment

by:tkuppinen
ID: 2691007
What's your db?  SQL server will only allow for so many active connections.
0
 

Author Comment

by:gfaucher
ID: 2691099
I'm using an access db
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 2691152
This is an ugly error!

I did have this error which have a description of "Unknown error" I think.

I have sometimes corrected it using a client side cursor (instead of the server side default).

What is the code that cause this error?
0
 

Author Comment

by:gfaucher
ID: 2691215
emoreau

It might be that because the program is accessing a db on another drive on the network.  

Would you tell me how to do it
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 2691264
How do you open your recordset?

Before opening your recordset, you should set the CursorLocation like this:
Set rstTemp = New Recordset
rstTemp.CursorLocation = adUseClient
rstTemp.Open pstrQuery, mdbConnect, pCursorType, pLockType
0
 

Author Comment

by:gfaucher
ID: 2700723
I'm not really used to this.  Normally I use an adodc then i enter the query by the line of code adodc.recordsource = query then adodc.refresh.

This is how i get my information in the db
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 2700749
Instead of using ADO Data control, if you use ADO code, do you still have the error?
0
 

Author Comment

by:gfaucher
ID: 2700909
What do you mean by ado code??
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 2700921
Don't use ADO Data Control and use code like my comment of Thursday, April 06 2000 - 04:04PM EDT
0
 

Author Comment

by:gfaucher
ID: 2701283
It doesn't work, it gives me the same error number right at the beginning.  
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 2701353
2 things:

1) Have you tried on another database (nwind.mdb for example)?

2) Can we see code?
0
 

Author Comment

by:gfaucher
ID: 2701457
This is the code where the error always occurs.

Private Sub CmdListe2_Click()
'Imprime la liste des employés à appeler pour le surtemps en x copie(s)
Dim CompteurDeb As Integer
Dim CompteurFin As Integer
Dim TabClasse(1 To 50, 1 To 5) As Variant
Dim TabProd(1 To 50, 1 To 5) As Variant
Dim TabMaint(1 To 50, 1 To 5) As Variant
Dim TabApprenti(1 To 50, 1 To 5) As Variant
Dim Temp(1 To 5) As Variant
Dim TNo As Integer
Dim Compt1 As Integer
Dim Compt2 As Integer
Dim Compt3 As Integer
Dim Compt4 As Integer

    AdoEmployes.RecordSource = "Select * from Employes where Matricule Like '" _
                                & AdoClassempl.Recordset.Fields("Matricule") & "%'"
    AdoTempSup.RecordSource = "Select * from RequeteHeureSup where Matricule Like '" _
                                & AdoClassempl.Recordset.Fields("Matricule") & "%'"
    TxtAttente.Text = "Le système compile les données"
    TxtAttente.Visible = True
    TxtAttente.SetFocus
    Call Module1.SavePeriode
    AdoListe2.Recordset.MoveFirst
    While Not AdoListe2.Recordset.EOF
        AdoListe2.Recordset.Delete
        AdoListe2.Recordset.MoveNext
    Wend
    AdoListe2.Refresh
    AdoClasse.Recordset.MoveFirst
    While Not AdoClasse.Recordset.EOF
        'Vide le tableau
        For i = 1 To 50
            For j = 1 To 5
                TabClasse(i, j) = ""
                TabProd(i, j) = ""
                TabMaint(i, j) = ""
                TabApprenti(i, j) = ""
            Next
        Next
        Compt1 = 0
        Compt2 = 0
        Compt3 = 0
        Compt4 = 0
        TClasse = AdoClasse.Recordset.Fields("Classe")
        AdoClassempl.Recordset.MoveFirst
        While Not AdoClassempl.Recordset.EOF
        'Ceux qui sont de la classe TClasse
            If AdoClassempl.Recordset.Fields("Classe") = TClasse Then
                Compt1 = Compt1 + 1
               
                AdoEmployes.Refresh
                AdoTempSup.Refresh
               
                TabClasse(Compt1, 1) = TClasse
                TabClasse(Compt1, 2) = AdoEmployes.Recordset.Fields("Nom")
                TabClasse(Compt1, 3) = AdoEmployes.Recordset.Fields("Prenom")
                TabClasse(Compt1, 4) = AdoEmployes.Recordset.Fields("Tel")
                TabClasse(Compt1, 5) = AdoTempSup.Recordset.Fields("TotHeure")
               
            Else
                'Ceux qui ne soont pas de la classe TClasse mais
                'qui ont la sous classe TClasse sans être apprenti
                'et qui sont de la productivité
                If (AdoClassempl.Recordset.Fields("Classe") <> TClasse) _
                And (AdoClassempl.Recordset.Fields("Classe") <> "AO") _
                And (AdoClassempl.Recordset.Fields("Classe") <> "AM") _
                And (AdoClassempl.Recordset.Fields("ClasseSecond") Like "*" & TClasse & "*") _
                And (Not (AdoClassempl.Recordset.Fields("Maintenance"))) Then
                    Compt2 = Compt2 + 1
                                       
                    AdoEmployes.Refresh
                    AdoTempSup.Refresh
                   
                    TabProd(Compt2, 1) = TClasse
                    TabProd(Compt2, 2) = AdoEmployes.Recordset.Fields("Nom")
                    TabProd(Compt2, 3) = AdoEmployes.Recordset.Fields("Prenom")
                    TabProd(Compt2, 4) = AdoEmployes.Recordset.Fields("Tel")
                    TabProd(Compt2, 5) = AdoTempSup.Recordset.Fields("TotHeure")
                Else
                    'Ceux qui ne sont pas de la classe TClasse,
                    'qui ont la sous-classe TClasse, sans être
                    'apprenti et qui sont de la maintenance
                    If (AdoClassempl.Recordset.Fields("Classe") <> TClasse) _
                    And (AdoClassempl.Recordset.Fields("Classe") <> "AO") _
                    And (AdoClassempl.Recordset.Fields("Classe") <> "AM") _
                    And (AdoClassempl.Recordset.Fields("ClasseSecond") Like "*" & TClasse & "*") _
                    And (AdoClassempl.Recordset.Fields("Maintenance")) Then
                        Compt3 = Compt3 + 1
                        AdoEmployes.Refresh
                        AdoTempSup.Refresh
                       
                        TabMaint(Compt3, 1) = TClasse
                        TabMaint(Compt3, 2) = AdoEmployes.Recordset.Fields("Nom")
                        TabMaint(Compt3, 3) = AdoEmployes.Recordset.Fields("Prenom")
                        TabMaint(Compt3, 4) = AdoEmployes.Recordset.Fields("Tel")
                        TabMaint(Compt3, 5) = AdoTempSup.Recordset.Fields("TotHeure")
                    Else
                        'Les apprentis
                        If ((AdoClassempl.Recordset.Fields("Classe") = "AO") _
                        Or (AdoClassempl.Recordset.Fields("Classe") = "AM")) _
                        And (AdoClassempl.Recordset.Fields("ClasseSecond") Like "*" & TClasse & "*") Then
                            Compt4 = Compt4 + 1
                            AdoEmployes.Refresh
                            AdoTempSup.Refresh
                           
                            TabApprenti(Compt4, 1) = TClasse
                            TabApprenti(Compt4, 2) = AdoEmployes.Recordset.Fields("Nom")
                            TabApprenti(Compt4, 3) = AdoEmployes.Recordset.Fields("Prenom")
                            TabApprenti(Compt4, 4) = AdoEmployes.Recordset.Fields("Tel")
                            TabApprenti(Compt4, 5) = AdoTempSup.Recordset.Fields("TotHeure")
                        End If
                    End If
                End If
            End If
            AdoClassempl.Recordset.MoveNext
        Wend
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 2701588
To minimize changes in your code, open a connection, then a recordset a copy this recordset to your ADODC with this code:

Option Explicit

Private mdbConnect As ADODB.Connection

Private Sub Command1_Click()
Dim rstTemp As ADODB.Recordset

    Set rstTemp = New ADODB.Recordset
    rstTemp.Open "SELECT * from Employees", mdbConnect, adOpenStatic
    Set Adodc1.Recordset = rstTemp
   
    Set rstTemp = New ADODB.Recordset
    rstTemp.Open "SELECT * from Products", mdbConnect, adOpenStatic
    Set Adodc2.Recordset = rstTemp
   
    MsgBox Adodc1.Recordset.RecordCount
    MsgBox Adodc2.Recordset.RecordCount
End Sub

Private Sub Form_Load()
    Set mdbConnect = New ADODB.Connection
    With mdbConnect
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Microsoft Visual Studio\VB98\Nwind.mdb;Persist Security Info=False"
        .Open
    End With
End Sub

Before getting this in your code, delete all your datacontrols and recreate them to be sure you have properties set correctly.

At best, you delete all the Data Controls on your forms.

BTW, instead of this code (that clear the array):
        'Vide le tableau
        For i = 1 To 50
            For j = 1 To 5
                TabClasse(i, j) = ""
                TabProd(i, j) = ""
                TabMaint(i, j) = ""
                TabApprenti(i, j) = ""
            Next
        Next

You can use:
   Erase TabClasse
   Erase TabProd
   Erase TabMaint
   Erase TabApprenti
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:gfaucher
ID: 2703469
I fixed the code I've sent you before. is the solution the same for this code?


Private Sub CmdNextDate_Click()

    Call Module1.EmptyRecord
    CalDateSur.Value = CalDateSur.Value + 1
    TxtHeureRef.SetFocus
    AdoHeureSup.Recordset.CursorLocation = adUseClient
    AdoHeureSup.RecordSource = "Select * from TempSup where MATRICULE like '" _
    & AdoEmploye.Recordset.Fields("Matricule") & "%' and Date like '" _
    & CalDateSur.Value & "%'"
    AdoHeureSup.Refresh
    Call Module1.CheckNoMatch
    TxtHeureRef.SetFocus
End Sub

Private Sub CmdNextEmp_Click()
   
    Call Module1.EmptyRecord
    If Not AdoEmploye.Recordset.EOF Then
        AdoEmploye.Recordset.MoveNext
        If Not AdoEmploye.Recordset.EOF Then
            AdoHeureSup.RecordSource = "Select * from TempSup where MATRICULE like '" _
            & AdoEmploye.Recordset.Fields("Matricule") & "%' order by date"
            AdoHeureSup.Refresh
           
            If AdoHeureSup.Recordset.RecordCount <> 0 Then
                AdoHeureSup.Recordset.MoveLast
                obj.MoveLast
                'CalDateSur.Value = AdoHeureSup.Recordset.Fields(1)
            Else
                AdoHeureSup.Recordset.AddNew
                AdoHeureSup.Recordset.Fields("Matricule") = _
                AdoEmploye.Recordset.Fields("Matricule")
                CalDateSur.Value = Premier
                CboDateSur.Text = CalDateSur.Value
                TxtHeureRef.Text = 0
                TxtHeureAcc.Text = 0
            End If
        End If
    End If
    TxtHeureRef.SetFocus
End Sub

Private Sub CmdPrevDate_Click()

    Call Module1.EmptyRecord
    CalDateSur.Value = CalDateSur.Value - 1
    AdoHeureSup.Recordset.CursorLocation = adUseClient
    AdoHeureSup.RecordSource = "Select * from TempSup where MATRICULE like '" _
    & AdoEmploye.Recordset.Fields("Matricule") & "%' and Date like '" _
    & CalDateSur.Value & "%'"
    AdoHeureSup.Refresh
    Call Module1.CheckNoMatch
    TxtHeureRef.SetFocus
End Sub

Private Sub CmdPrevEmp_Click()
    Call Module1.EmptyRecord
    If Not AdoEmploye.Recordset.BOF Then
        AdoEmploye.Recordset.MovePrevious
        If Not AdoEmploye.Recordset.BOF Then
            AdoHeureSup.Recordset.CursorLocation = adUseClient
            AdoHeureSup.RecordSource = "Select * from TempSup where Matricule like '" _
            & AdoEmploye.Recordset.Fields("Matricule") & "%' Order by Date"
            AdoHeureSup.Refresh
            If AdoHeureSup.Recordset.RecordCount <> 0 Then
                AdoHeureSup.Recordset.MoveLast
                'CalDateSur.Value = AdoHeureSup.Recordset.Fields(1)
            Else
                AdoHeureSup.Recordset.AddNew
                AdoHeureSup.Recordset.Fields("Matricule") = _
                AdoEmploye.Recordset.Fields("Matricule")
                CalDateSur.Value = Premier
                CboDateSur.Text = CalDateSur.Value
            End If
        End If
    End If
    TxtHeureRef.SetFocus
End Sub
Private Sub CmdSave_Click()
    AdoHeureSup.Recordset.Update
End Sub

Private Sub DGEmploye_RowColChange(LastRow As Variant, ByVal LastCol As Integer)
    AdoHeureSup.RecordSource = "Select * from TempSup where MATRICULE like '" _
            & AdoEmploye.Recordset.Fields("Matricule") & "%' order by date"
    AdoHeureSup.Refresh
    AdoHeureSup.Recordset.MoveLast
    CalDateSur.Value = AdoHeureSup.Recordset.Fields(1)
End Sub
Private Sub Form_Load()
    AdoHeureSup.RecordSource = "Select * from TempSup where MATRICULE like '" _
            & AdoEmploye.Recordset.Fields("Matricule") & "%' order by date"
    AdoHeureSup.Refresh
    AdoHeureSup.Recordset.MoveLast
    CalDateSur.Value = AdoHeureSup.Recordset.Fields(1)
   
End Sub
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 2703540
The concept of minimizing the connections is always good!
0
 

Author Comment

by:gfaucher
ID: 2703593
But when I insert or modify data, I ahve no other way to do so than connect to the db?

Are you telling me that when I refresh the adodc, I create a new connection?
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 2703645
These lines bind a recordset to a ADODC:
rstTemp.Open "SELECT * from Employees", mdbConnect, adOpenStatic
Set Adodc1.Recordset = rstTemp

(mdbConnect is my connection)

What I say is to open ONE connection and bind all your adodc on that connection (like the code I gave you yesterday).
0
 

Author Comment

by:gfaucher
ID: 2703747
i'll try this, thanks a lot.  

BTW, What can i do with expert points that i got??  I'm a new user of this site.
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 2703803
There is not much to do with it!

If you have enough points, you appear in the EE Hall of Fame (today's lowest has 87226 points).

Experts had a surprise last february. Experts who were ranked in the top 100 had the choice of receiving a palm pilot or a scanner absolutely free.

EE said that there will be other gift like that one!
0
 

Author Comment

by:gfaucher
ID: 2704042
I'Ve seen that you are from Montréal, do you speek french because I'm from Sorel, Québec.  Our exchange would certainely be easier.
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 2704073
Certainement que je parle français. C'est ma langue maternelle!
0
 

Author Comment

by:gfaucher
ID: 2704103
ca c'est cool!!! un expert de ma langue.

J'ai essayer ton code et ca marche très bien pour aficher les données mais je ne peut pas en insérer.  lorsque je tape adodc.recordset.addnew, je recoit une erreur qui dit: L'opération demandée par l'application n'est pas prise en charge par le fournisseur.

0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 2704157
A l'ouverture du recordset, ajoute un paramètre:
rstTemp.Open "SELECT * from Employees", mdbConnect, adOpenStatic, adLockPessimistic
0
 

Author Comment

by:gfaucher
ID: 2704242
Ca semble fonctionner, est-ce garantie que je n'aurai plus cette erreur maintenant??
0
 
LVL 69

Accepted Solution

by:
Éric Moreau earned 200 total points
ID: 2704275
Garanti est un bien grand mot!!! Tu n'auras plus de problèmes jusqu'au prochain!!!
0
 

Author Comment

by:gfaucher
ID: 2704311
Merci beaucoup!!! avant que je trouve ce site, ca faisait déjà une semaine que je me cassait la tête sur ce problème.

Pourrais-tu svp m'envoyer ton adresse e-mail afin que je puisse te contacter en cas de besoin.  Moi c'est g_faucher@hotmail.com,  je te garantie que je ne l'utiliserai pas pour rien.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now