Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Posts
    14

    Unanswered: ADO Recordsets question

    I'm teaching myself VB6 and working with ADO recordsets to connect to my Access97 db. Looking at several examples and I'm sort of confused on the sequence of events for what I want to do.

    1 DB with several tables. On my Form_Load event I call 4 different procedures to pull data from 4 different tables.

    In my Declarations I have:
    Dim cnAllocation As ADODB.Connection
    Dim rsAllocation As ADODB.Recordset

    In the Form_Load I setup my connection and set my recordset like:
    Set cnAllocation = New ADODB.Connection
    Set rsAllocation = New ADODB.Recordset
    cnAllocation.CursorLocation = adUseClient
    cnAllocation.Open strConnection

    rsAllocation.Open strSQL

    My first procedure fires and grabs the data. This is where I'm confused. After the procedure is finished do I rsAllocation.Close and then in the next procedure can I issue rsAllocation.Open strSQL or do I leave it Open? Right now I run the first procedure, then on the next procedure I issue a rsAllocation.Open strSQL

    I've tried both and I get the error "Not allowed when object is open or closed" depending on whether it was opened or closed by the previous procedure. Do I need to open a new recordset for each procedure even though I'm still connecting to the same database but just using a different table for each procedure.

    I apologize if this is a very simple question but all the examples I have found seem to deal with just one table. If someone could lay out the squence needed to do this I would appreciate it. FWIW the procedures all work when run by themselves but the problem comes when I try to run one after another.

    TIA
    David

  2. #2
    Join Date
    Dec 2003
    Location
    chennai
    Posts
    27

    Re: ADO Recordsets question

    hi

    this is simple but u must implement when ur going to pass query to the same recordset

    befor passing next query u must close the recordset using code given below

    rsAllocation.close
    set rsAllocation=Nothing

    then u can pass a new query to the same recordset its not give any error message

    bye
    wish u happy christmas and new year


    Originally posted by vbnovice
    I'm teaching myself VB6 and working with ADO recordsets to connect to my Access97 db. Looking at several examples and I'm sort of confused on the sequence of events for what I want to do.

    1 DB with several tables. On my Form_Load event I call 4 different procedures to pull data from 4 different tables.

    In my Declarations I have:
    Dim cnAllocation As ADODB.Connection
    Dim rsAllocation As ADODB.Recordset

    In the Form_Load I setup my connection and set my recordset like:
    Set cnAllocation = New ADODB.Connection
    Set rsAllocation = New ADODB.Recordset
    cnAllocation.CursorLocation = adUseClient
    cnAllocation.Open strConnection

    rsAllocation.Open strSQL

    My first procedure fires and grabs the data. This is where I'm confused. After the procedure is finished do I rsAllocation.Close and then in the next procedure can I issue rsAllocation.Open strSQL or do I leave it Open? Right now I run the first procedure, then on the next procedure I issue a rsAllocation.Open strSQL

    I've tried both and I get the error "Not allowed when object is open or closed" depending on whether it was opened or closed by the previous procedure. Do I need to open a new recordset for each procedure even though I'm still connecting to the same database but just using a different table for each procedure.

    I apologize if this is a very simple question but all the examples I have found seem to deal with just one table. If someone could lay out the squence needed to do this I would appreciate it. FWIW the procedures all work when run by themselves but the problem comes when I try to run one after another.

    TIA
    David

  3. #3
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    brefore rsallocation.OPEN statement put this check

    if rsallocation.STATE then rsallocation.close

  4. #4
    Join Date
    Dec 2003
    Posts
    14

    ADO Recordsets

    Thanks for the help. Let me make sure I got this correct.

    In my Form_Load event I open the recordset like:

    Set cnAllocation = New ADODB.Connection
    Set rsAllocation = New ADODB.Recordset
    cnAllocation.CursorLocation = adUseClient
    cnAllocation.Open strConnection

    With my first procedure I will do this:

    rsAllocation.Open strSQL
    ...addtional code
    rsAllocation.close
    set rsAllocation=Nothing

    when I call my next procedure I will do this

    if rsallocation.STATE then rsallocation.close
    rsAllocation.Open strSQL
    ...addtional code
    rsAllocation.close
    set rsAllocation=Nothing

    and then repeat this for each procedure. Is this correct? Again, sorry for bothering everyone with something so simple.

    TIA
    David

  5. #5
    Join Date
    Feb 2002
    Posts
    2,232
    You do not need to destroy the recordset object - so do not do "set rs = nothing". Once you have created the recordset object it is reusable as long as the recordset is closed before the next use. Reusing the object results in better perfomance and memory usage.

    Anyway - here it is:

    Set cnAllocation = New ADODB.Connection
    cnAllocation.CursorLocation = adUseClient
    cnAllocation.Open strConnection

    With my first procedure I will do this:

    Set rsAllocation = New ADODB.Recordset
    rsAllocation.Open strSQL
    ...addtional code
    if rsallocation.STATE then rsallocation.close

    when I call my next procedure I will do this

    rsAllocation.Open strSQL
    ...addtional code
    if rsallocation.STATE then rsallocation.close
    ...
    'the end of your recordsets
    set rs = nothing
    if cn.State then cn.close
    set cn = nothing

  6. #6
    Join Date
    Dec 2003
    Posts
    14

    ADO Recordsets

    Thanks for the info. It has been a great help.

    David

  7. #7
    Join Date
    Feb 2002
    Posts
    2,232
    Happy to help.

Posting Permissions

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