Results 1 to 9 of 9
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    what's the matter with ADO?!

    I've tried to convert a perfectly working code that uses DAO in Access 2000 into an ADO-equivalent but when I run the procedure a message box pops up saying, "Number of rows with pending changes exceeded the limit." This happens when the code executes the first loop in the procedure which should add records to the VAX table (line 31 of Sub Comando0_Click). Could it be that ADO cannot handle as many rows as DAO at one fell swoop?
    Attached Files Attached Files

  2. #2
    Scott A
    Guest

    Re: what's the matter with ADO?!

    I think the problems is because you are using adLockBatchOptimistic as the LockType. This is telling the ADO recordset to perform the actual updates as a batch, instead of individually, in which case the rst.Update statement isn't really writing out the changes. Batch updating was not possible in DAO Recordsets (except via Transactions), so the ADO translated code you have is not actually the direct equivalent to the DAO code.
    Anyway, try changing the LockType to adLockOptimistic instead, or keep the batch processing, but every so often (like every 100 records or so), issue an UpdateBatch (ie. rst.UpdateBatch).

    HTH

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thanks Scott, well...

    The problem is solved but another one has risen:
    I've seen that when you want to open a recordset in DAO you have to use the "set" keyword before the variable declared as recordset (cfr. code line 55 of the attached file(Form_Maschera1DAO.cls)) and this allows you to reuse the same variable ("tabe" in this case) throughout the procedure without the need to close it. Not so in the ADO equivalent (cfr. code line 54 in the previously attached file(Form_Maschera1.cls)). In fact, when I step into the ADO procedure, line 54 gives me the following error message, "Operation is not allowed when the object is open."
    Problem is I can't use the Close method for the recordet immediately before reopening it in order to reuse it because in certain cases my code (see lines 28-29 in the attached Report_Report4.cls) requires the recordset to remain open.
    How can I circumvent this?
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Thanks Scott, well...

    In both DAO and ADO, you can filter an open recordset to a new recordset. Have you tried that instead of opening the recordset again with different arguments? And you should always close a recordset before reusing the variable. Otherwise, the resources allocated for that variable don't get cleaned up reliably and you wind up with a memory leak.
    Charlotte

  5. #5
    Scott A
    Guest

    Re: Thanks Scott, well...

    Charlotte is 100% right (no big surprise [img]/w3timages/icons/tonguea.gif[/img]). You definately want to be cleaning up your object correctly.

    But, did you know you can do something like:
    Set rstADO = New ADODB.Recordset
    - or -
    Set rstADO = <ADO Connection>.Execute(CommandText:=strSQL, Options:=adCmdText)

    Maybe this will help give you some ideas.
    (Although, I state again, proper cleanup of object is essential)

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Good advice Charlotte, but...

    How should I set up the filter to replace lines 28-29 in Report_Report4.cls? The Filter property restricts your criteria expressions to those of the form FieldName-Operator-Value, I can't put an aggregate function such as Max in it. Or can I?[img]/w3timages/icons/smile.gif[/img]

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Good advice Charlotte, but...

    I have no idea what you mean by "replace lines 28-29 in Report_Report4.cls". Reports don't have any real existence in Access, so you can't replace a particular "line" of a report, you can only filter the data that feeds into it. Maybe if you explain further what you're trying to accomplish, you'll get a more useful answer.
    Charlotte

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ADO does work!!:-)

    The second option "Set rstADO = <ADO Connection>.Execute(CommandText:=strSQL, Options:=adCmdText)" you offered in your last suggestion has been an epiphany for setting the Report_Report4.cls (see file) straight. Though all of my project's code now bears the mark of Charlotte's advice about closing each recordset variable before reopening it.
    So, kudos to both of you!
    Attached Files Attached Files

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Good advice Charlotte, but...

    Sorry. By saying Report_Report4<big>.cls</big> I was asking how I could replace lines 28-29 in the code relevant to Report4 (which is in the attached file Report_Report4.cls) to make it work as in DAO.
    But it's o.k., please see my reply to Scott.
    Ciao

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •