Results 1 to 8 of 8

Thread: [RESOLVED]Error Mssage with recordset ADO

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2006
    Posts
    367

    Resolved [RESOLVED]Error Mssage with recordset ADO

    Using ADO, VB 6.0, and Access 2000

    My datagrids are all working fine and display information from the database perfectly.

    However, when I click on the X button at the top right of the form to close the program I get an error.

    The error message I receive says...
    Run-time error
    Operation is not allowed when the object is closed.

    When I hit debug it highlights the line...
    rs3.Close

    That line is found with the following coding...
    VB Code:
    1. Private Sub Form_Unload(Cancel As Integer)
    2. Form2.rs.Close
    3. Form2.cn.Close
    4. Set Form2.rs = Nothing
    5. Set Form2.cn = Nothing
    6.  
    7. rs2.Close
    8. cn2.Close
    9. Set rs2 = Nothing
    10. Set cn2 = Nothing
    11.  
    12. rs3.Close
    13. cn3.Close
    14. Set rs3 = Nothing
    15. Set cn3 = Nothing
    16. End Sub

    I don't know much about closing and setting recordsets and connections to nothing, so that is probably why I don't get what's going wrong. I thought you were supposed to close the recordsets and connections when you were done pulling the information. However, when I do that my datagrids come out empty. So, I figured I'd just leave them open and create new ones for other connections and just close it all together at the end. Or is there any easier way around this?
    Last edited by lilmark; Apr 20th, 2006 at 04:47 PM.

  2. #2
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,713

    Re: Error Mssage with recordset ADO

    Just test for the open state first before trying to close it.
    VB Code:
    1. If rs3.State = adStateOpen Then rs3.close
    2. Set rs3 = Nothing
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2006
    Posts
    367

    Re: Error Mssage with recordset ADO

    Quote Originally Posted by RobDog888
    Just test for the open state first before trying to close it.
    VB Code:
    1. If rs3.State = adStateOpen Then rs3.close
    2. Set rs3 = Nothing
    That did the trick, but I don't understand why. Why would I need to test it first? Why can't I just close it when I exit the form and the program calls the Form_Unload() where my closing and set to nothing coding is located?

    I'm a little confused with the closing and setting to nothing of the recordsets and connections and how that works.

    If I only had one database I would only need one connection and I could just leave that open until the entire program is closed, right?

    I would only need to make multiple recordsets to pull different data from the database, right?

    Or I could use a single recordset and just set it to NEW to get different data rather than make another recordset, right?

    I don't get when I'm supposed to Close them and set them to Nothing.

    And, why it emptys my grid when I do that. I figured since it was already displayed in the grid I could just close it, but than the data disappears.

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,501

    Re: Error Mssage with recordset ADO

    For whatever reason, rs3 wasn't open. You cant close something that is already closed (think of a door), and in code terms, this causes an error.

    You should close all recordsets/connections/... when you are finished with them, to clear up the memory and connections etc at the database end (which may be stopping other users from working with the database).

    Everything you "asked" about connections/recordsets is correct.


    The reason it empties the grids, is because the DataGrid control needs "live" data (as it is editable); it cannot be filled and then left alone without data to hold its hand.

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2006
    Posts
    367

    Re: Error Mssage with recordset ADO

    For whatever reason, rs3 wasn't open. You cant close something that is already closed (think of a door), and in code terms, this causes an error.
    I did have rs3 open. Below you can see the coding I have in my project. I even did a "Find" search of rs3 to make sure I didn't accidentally close it somewhere in my project and I did not have it closed anywhere.

    VB Code:
    1. Set cn3 = New ADODB.Connection
    2. cn3.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    3. "Data Source = C:\Documents and Settings\Marco\My Documents\Math\MathTutoring.mdb"
    4. cn3.Open
    5.  
    6. Set rs3 = New ADODB.Recordset
    7. rs3.CursorLocation = adUseClient
    8. rs3.Open strSQL, cn2, , , adCmdText
    9. Set dgEmployeeHistory.DataSource = rs3

    I'm glad I was right about all those other questions. See, I'm learning! I also figured the datagrid was using live data, but it's one thing to "assume" and another to "know."

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,501

    Re: Error Mssage with recordset ADO

    I would guess that the DataGrid closes it somehow, but I have no idea how true that is - the only times I've been willing to touch bound controls in the last 5 years is to answer questions here!

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2006
    Posts
    367

    Re: Error Mssage with recordset ADO

    Quote Originally Posted by si_the_geek
    I would guess that the DataGrid closes it somehow, but I have no idea how true that is - the only times I've been willing to touch bound controls in the last 5 years is to answer questions here!
    This is bound? I thought bound was when you used the Data control in your program and used the property windows to set the source, location, etc.

    Anyways, I fixed the problem with the error. I just redid the coding to a single connetion and several recordsets. Everything still works ok like before, but when I exit the program I don't get that annoying error popup anymore.

  8. #8
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,713

    Re: Error Mssage with recordset ADO

    This is binding it via code instead of the connection wizard.

    Set dgEmployeeHistory.DataSource = rs3
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

Posting Permissions

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



Click Here to Expand Forum to Full Width