Solved

adodc too many active users

Posted on 2000-04-06
26
379 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 70

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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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 70

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 70

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 70

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 70

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 70

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
 

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 70

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 70

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 70

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 70

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 70

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 70

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
Suggested Courses
Course of the Month4 days, 23 hours left to enroll

635 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