Closing a Recordset from another sub (1 Viewer)

cage4000

Registered User.
Local time
Yesterday, 23:13
Joined
Oct 14, 2015
Messages
49
Hi Everyone, i was not able to locate this question in the forms so i apologize if this was asked and answered in the past.

I have a recordset that i open from my SQL Server that i load as the record source to my access form. I need the recordset to stay open while the form is open but once i close the form i need to recordset to close as well so i stop getting the error that says i can't save changes.

What i need to know is how i can reference the recordset in my Form_load sub so that i can close it in my Form_Close Sub.
 

isladogs

MVP / VIP
Local time
Today, 06:13
Joined
Jan 14, 2017
Messages
18,186
If your form control source is the recordset you are referring to, it will be closed automatically when the form is closed.

However, if you are opening a recordset from within your form using code similar to

Code:
Set rst = CurrentDb.OpenRecordset ....

With rst
..
End With

then that should be followed by the line
Code:
Set rst = Nothing
to ensure the recordset is closed
 

cage4000

Registered User.
Local time
Yesterday, 23:13
Joined
Oct 14, 2015
Messages
49
Thank you for your quick response ridders

so if i try to put the set rst = nothing in the form_load statement it will not let me save records in it. if i put the set rst = nothing in my form_close statement it errors out on me because it can not find rst. how would i call from the form_load sub in order for it to reference the rst to close it?
 

cage4000

Registered User.
Local time
Yesterday, 23:13
Joined
Oct 14, 2015
Messages
49
or is there code that would look up all open recordsets in access and close them out?
 

isladogs

MVP / VIP
Local time
Today, 06:13
Joined
Jan 14, 2017
Messages
18,186
If you read the first sentence in my previous reply again, you'll se I've already given the answer. You don't need to do anything.
When the form is closed, so is the recordset used as it's control source.
 

cage4000

Registered User.
Local time
Yesterday, 23:13
Joined
Oct 14, 2015
Messages
49
here is how i have the recordset in my code. if me.recerdset is the control source than yes it is.

Code:
Private Sub Form_Load()

Dim sqlMain As String

sqlMain = "SELECT * FROM tblPRF Where PRF_ID is null AND PRFType = 'PRF'"

Set rsMain = sqlDB().OpenRecordset(sqlMain, dbOpenDynaset, dbSeeChanges)

Set Me.Recordset = rsMain

End Sub
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 01:13
Joined
Feb 28, 2001
Messages
27,001
Looking at this, I am suddenly VERY confused and I would bet that Access is too.

A form opens a recordset based on the Me.Recordsource property. The recordset that is opened based on that property is found via Me.Recordset at the time that the form is opened. The recordset opens just after the form itself opens and by the time the form's .Current event fires, the recordset IS open. In order to reach a point where a Subform CAN be involved, I think the parent's .Current event must have already fired.

For you to attempt to change the Me.Recordset to anything other than that which was opened via the Me.Recordsource is legal (assuming the fields match). That action is going to force Access to update the Me.Recordsource, Me.RecordsetType, and Me.RecordLocks properties to be consistent with the new recordset.

However, the original recordset implied by the original Me.Recordsource is still open at the moment you are doing this change of .Recordset, and Access will try to close it - if for no other reason than to keep from having a dangling file-lock associated with the departing recordset. If at the moment of closure there was a change pending from the previously active recordset, then it may be that it is the previous instantiation of the form's recordset that can't be saved. I don't know (and I'm not sure how YOU could know) which recordset is complaining.
 

isladogs

MVP / VIP
Local time
Today, 06:13
Joined
Jan 14, 2017
Messages
18,186
I'm equally bewildered by this response.

Please try the following:
Make a COPY of your form & open it in Design view
Now open the property sheet & look at its record source
Check whether its equal to sqlMain i.e.
Code:
SELECT * FROM tblPRF Where PRF_ID is null AND PRFType = 'PRF'

If its not, replace the record source with the above code
Whichever was true, now DELETE all your code in Form_Load event
Save the form
Open it normally
Does it work correctly?

If not, why not?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 01:13
Joined
Feb 28, 2001
Messages
27,001
After re-reading the thread, I caught the fact that this is in a Form_Load event routine. I therefore misunderstood when this was happening - but I looked up the details for _Load events and the recordset IS open by that time. In fact, by the time the _Open event code is called, the recordset is already open because you can test for the recordset being empty and cancel the _Open if appropriate.

Therefore, though I misunderstood exactly when this was happening, I stand by my comments as being appropriate. If the recordsource stored with the form doesn't match the query you are using, then you are dealing with a questionable process anyway. If you were going to discard the .Recordsource's recordset anyway, why even bother to attempt the dynamic redirection?

And Colin is absolutely right in post #2 of this thread. If the .Recordsource is correct initially, then the closure of the form will automagically close the recordset without ANY intervention whatsoever.

I suspect that the failure to close properly (if that is your real complaint) is caused by this clunky design that discards the recordset before you even use it.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 17:13
Joined
Jan 20, 2009
Messages
12,849
here is how i have the recordset in my code. if me.recerdset is the control source than yes it is.

Code:
Private Sub Form_Load()

Dim sqlMain As String

sqlMain = "SELECT * FROM tblPRF Where PRF_ID is null AND PRFType = 'PRF'"

Set rsMain = sqlDB().OpenRecordset(sqlMain, dbOpenDynaset, dbSeeChanges)

Set Me.Recordset = rsMain

End Sub

rsMain goes out of scope when the Load procedure terminates. The recordset is persisted only as Me.Recordset.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 01:13
Joined
Feb 28, 2001
Messages
27,001
At least we can say that the recordset as seen through rsMain can be easily closed since that pointer is never actually used for any kind of modification. That situation would not lead to the close error. At least, I don't think it will, because I don't see anything that needs saving through rsMain.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 17:13
Joined
Jan 20, 2009
Messages
12,849
I have a recordset that i open from my SQL Server that i load as the record source to my access form. I need the recordset to stay open while the form is open but once i close the form i need to recordset to close as well so i stop getting the error that says i can't save changes.

Once rsMain has been set as the form's recordset it is no longer needed because it will be persisted by the form. It doesn't need to be separately kept open.

Seems to me that you are not declaring the variable and it disappears when it goes out of scope at the end of the Load procedure. It is doesn't exist when you refer to it from the Close procedure.

What i need to know is how i can reference the recordset in my Form_load sub so that i can close it in my Form_Close Sub.

To keep any variable in scope outside of a procedure it needs to be declared at the object module level before the first procedure. Or at the application level which is via the declarations before the first procedure in a Standard Module.
 

cage4000

Registered User.
Local time
Yesterday, 23:13
Joined
Oct 14, 2015
Messages
49
Hi Everyone. it looks like i have sparked quite the converstaion over this. if it helps this is where i have declared the recordset is coming from in a sepreate module.

Code:
Public Function sqlDB() As Database
    Static mCurrSqlDb As Database
    If mCurrSqlDb Is Nothing Then
        Set mCurrSqlDb = OpenDatabase("", False, False, "Driver={SQL Server};Server=L563931F034C53\ICQADB,1433;Database=" & DLookup("DC_NBR", "[DC_NBR]") & "QA;Uid=QALogIn;Pwd=QALogout;")
        Debug.Print Now, "static mCurrSqlDB inited for ", hWndAccessApp
    End If
    Set sqlDB = mCurrSqlDb
End Function

i'm not a high level coder by any means, i just what to make the system stop locking up on me with the "You have to open the database as exclusive" error. and you are right, i'm not sure where the lock is coming from weather it is staying connected to the SQL Server link from above or the currentDb link from below. when building my code i only use the 2 for reference to help with this error. it usually does not give me any problems until now.

Code:
Public Function CurrDB() As Database
    Static mCurrDb As Database
    If mCurrDb Is Nothing Then
        Set mCurrDb = CurrentDb
        Debug.Print Now, "static mCurrDB inited for ", hWndAccessApp
    End If
    Set CurrDB = mCurrDb
End Function

the only code i have to help debug this is:

Code:
Public Sub OpenedSqlRST()
    Dim rst As DAO.Recordset

    For Each rst In sqlDB().Recordsets
       Debug.Print rst.Name
    Next rst
End Sub

and

Code:
Public Sub OpenedRST()
    Dim rst As DAO.Recordset

    For Each rst In CurrDB().Recordsets
       Debug.Print rst.Name
    Next rst
End Sub
 

cage4000

Registered User.
Local time
Yesterday, 23:13
Joined
Oct 14, 2015
Messages
49
i guess the only thing i'm asking for now is how do i close all open recordsets / open database connections with one peace of code that i can call at any time.
 

isladogs

MVP / VIP
Local time
Today, 06:13
Joined
Jan 14, 2017
Messages
18,186
Whilst there has certainly been a conversation, I think there isn't a single person who has replied indicating your approach makes sense.
If it works at all, its unnecessarily convoluted.

I'm not sure that any of the additional code adds clarity - at least it doesn't for me

Back in post #8, I asked you to test a simpler solution which is effectively the standard way of assigning a record source to a form.
Have you tried it? Please could you do so and let us know what the results are.

If it works, then the question you asked in your last post is redundant (see my comment in post #2)
If it doesn't work, then an explanation of what happens might assist our understanding of your setup

Otherwise, in the end, I think you will need to upload your database for someone to look at.
At the moment all anyone can do is make educated guesses based on experience .... and frankly I think none of us have experience of setting up a form in such an 'unusual' way
 
Last edited:

Minty

AWF VIP
Local time
Today, 06:13
Joined
Jul 26, 2013
Messages
10,355
i guess the only thing i'm asking for now is how do i close all open recordsets / open database connections with one peace of code that i can call at any time.

Pretty sure this works...
Code:
Private Sub CloseAllOpenObjects()

Application.Quit 

End Sub
 

isladogs

MVP / VIP
Local time
Today, 06:13
Joined
Jan 14, 2017
Messages
18,186
pretty sure this works...
Code:
private sub closeallopenobjects()

application.quit 

end sub

lol ....
Best make it a public procedure so it can be called at any time from any place :D
 
Last edited:

static

Registered User.
Local time
Today, 06:13
Joined
Nov 2, 2015
Messages
823
Since mCurrSqlDb is static it won't go out of scope and be disposed.
And everytime you call sqlDB you will reconnect it.

You get 'open exclusive' errors with ADO; not sure what you're using...

Link the tables and use DAO.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 01:13
Joined
Feb 28, 2001
Messages
27,001
how do i close all open recordsets / open database connections with one peace of code that i can call at any time.

Code:
Application.Quit

If you are going to close all database connections, you might as well exit. What we are trying to say is that your "dynamic" approach to everything is probably the cause of your problem. If you use statically defined record sources and statically defined forms, then you don't need any special code to do anything. Access will take care of itself.

If you REALLY had to do something forceful to close the recordsets, it is a sure sign from Access that you are doing something wrong, abnormal, outside the normal way of doing things. It is not my intent to dampen your enthusiasm, but if you would channel it a bit more away from spiffy dynamic designs that potentially leave recordset structures dangling, I think you would get far better results.

If our warnings fall on deaf ears and you ache so badly that you HAVE to do something to close all open recordsets, they are a collection of the database object. If you have more than one database object open, each one can have its own set of recordsets.

CurrentDB.Recordsets is the collection of all open recordsets for the current database, so a "For... Each" loop could be used to close them all.

Beware if that if you do that, you have undercut EVERY OPEN FORM, EVERY OPEN REPORT, and any attempt at creating a "persistent connection" between a front-end and back-end. You might not be able to do this if you are dealing with system-table records that Access itself has opened. This is a potential crash-bang-zoom situation. If you are going to do this, do it first on a copy of the DB because otherwise you might end up with a corrupted pile of dyspeptic dragon droppings.
 

cage4000

Registered User.
Local time
Yesterday, 23:13
Joined
Oct 14, 2015
Messages
49
Hi ridders. to answer your question on # 8, no it does not show up there and nor do i want it to. if i was to use it that way i would need to create a link in the ODBC Data Sources and link all the tables i need in this front-end DB (In which i do not have admen rights to do so, nor do i want to maintain that for over 300 PC's). When i distribute this database i will be turning it into a ACCDE file with no linked tables in it and only DAO.Recordsets that call directly from the SQL Server in the VBA code. This will take away the temptation from anyone who would like to tamper with it at the end user side.

This is why i have made it necessarily convoluted.

Since you have made it clear that you think none of the posters have experience of setting up a form in such an 'unusual' way, than i will pose my question in a different way:

forget all the info above. In access in general is there a peace of code that will kill all open DAO.Recordsets and DAO.Databases? if so what is it?
 

Users who are viewing this thread

Top Bottom