Closing a Recordset from another sub

[isladogs]

I doubt I can make the point any more clearly than I've already done or than Doc has just done.

Nobody here is supporting you in this approach because it is the wrong way to proceed. There are well established ways of setting up connections to both Access & SQL Server BE databases including DNS-less connection strings which can be setup using code, work equally well with ACCDE front ends and require no maintenance at any of the 300 PCs or how ever many are on the network.

In answer to your specific question, I've no idea as its not something that anyone should ever need to do ... unless they want to quit the database ...when it will happen anyway. Try a Google search and see what you find

Good luck with your project - I'm going to kill my connections to this thread now

 

[cage4000]

Thank you Doc and Static for your feedback and advice. Being new to trying to code a backend in using a Dynamic vs Static approach is something new to me. Being under a deadline to produce this Database does not give me the luxury to redo all of my code, so i'm stuck with the way it is. I will try to incorporate all that you said.

ridders and Minty, thank you for your helpful insights on my problem and shame on you for your arrogant condescending remarks towards it. The internet is a big place and we do not need another pair of rude users mouthing off at others who may or may not be at their level of programming. In other words, if you are NOT going to be helpful than do not bother wasting other peoples time with responding.

 

[The_Doc_Man]

is there a peace of code that will kill all open DAO.Recordsets and DAO.Databases? if so what is it?

Read the last half of my post #19. Like Ridders, I find your insistence on a questionable path of action to be puzzling since we are telling you that in this design, you are your own worst enemy. But to be honest, I've been in places where I felt as though I had been painted into a corner. The problem is that your method is so convoluted that I really don't see where it is going and that makes it very difficult for me to give good advice. If I can't see it in my head, I can't draw you a roadmap. Besides which, I'm out of my element with SQL server. My shop used ORACLE more often than anything else before I retired.

I am not asking you to post even a dumbed-down copy of the database in question because I categorically do not open databases. I've been burned too often - thank goodness for Kaspersky Internet Security at its "questionable behavior" detectors. Like Ridders, I have to walk away because I just don't understand what you are trying to do. Your explanations are at the level where none of us can see the whole forest because too many trees are in the way.

 

[isladogs]

All the advice given by everyone in this thread has been intended as helpful.
I don't believe anyone, including myself, has at any time been condescending.

However, all who have contributed are very experienced and long standing forum members who give up a lot of time assisting others.
If you don't like our advice, fine - you can ignore it.
But until your last post, nobody has been in any way negative

 

[The_Doc_Man]

Being under a deadline to produce this Database does not give me the luxury to redo all of my code

If it doesn't work when you deliver it, how much time will you have afterwards?

As to getting miffed because you think Ridders and Minty were condescending, I wish to point out that if you try to do something that is dreadfully wrong, it is not condescending to try to tell you that you are looking at the problem wrong. It may be unwelcome due to the deadlines you face, and I GET THAT! But both Minty and Ridders were trying to guide you away from your design problems. You have fixated on a design. OK, I get that, too.

I wish you luck with this problem and further do not wish unfortunate consequences of having something that isn't working by the time your deadline rolls around. So I really do hope you solve this problem.

At the same time, I must say that your design evades my comprehension. Given my 40+ years of professional computer programming; systems analysis; device-driver design and implementation; database design; and application creation, I've seen some real doozies out there in both private sector and public sector. Your design concept seems to be somewhere I've never been before.

I don't speak for anyone else but myself, so I will not "poison" the thread. But I have to step away because I can see that my approach will not be productive for you, and in respect for your time I will step away from the thread too.

 

[cage4000]

Thank you Doc i appreciate your feedback.

ridders, i disagree. Also, I thought you killed your connections to this thread.

 

[Minty]

I wasn't being flippant or condescending. You asked for a piece of code, and I and Doc provided the only sensible way we know of closing everything in the database.

As has been suggested by people with a shed load more experience than me, the approach you are taking appears unnecessary.

I have just had to reconfigure a database with 240 tables to use DSN-less connections, and whilst it wasn't a ten minute task, it has been a quite insightful learning experience. Especially as I didn't write a lot of the original code.

You can, in VBA, create a list of your current tables and the databases they connect to - save it to a locally saved table. Then create the connections (again in VBA) to make the database work again.

There is a myriad of information about this technique and it would be completely transparent to the end users, without you jumping through self made blocking hoops.

http://www.accessmvp.com/djsteele/DSNLessLinks.html

I hope you resolve your issue.

 

[cage4000]

Hi Minty, when you and ridders are making a joke at my expense (as seen in post #16 and #17) especially after I have been banging my head on this issue for a month now, it does not leave a good test in the mouth.

I appreciate your follow up, recommendations along with the link and your experience on this issue. I will go through it and see if it will work for my code.

 

[static]

I hadn't properly read your code before I posted last night.
I had assumed that you were opening a recordset on a sql server connection.
But it looks like you are opening the database. (?!)
I didn't even know this was possible. And if it is possible I can't think why you'd want to do it or what repercussions it might have.

Even if you wanted to query data from another Access database you could just use IN in the SQL. Failing that you'd create an actual connection. And if you were doing it wrong you'd open the database... so,

Is there a reason you aren't opening recordsets from a connection?

 

[isladogs]

Hi Minty, when you and ridders are making a joke at my expense (as seen in post #16 and #17) especially after I have been banging my head on this issue for a month now, it does not leave a good test in the mouth.

If you are going to keep referring to me critically, I feel obliged to respond.
Sorry to disillusion you, there wasn't a joke at your expense.

In fact stating it should be:

a public procedure so it can be called at any time from any place

was a direct response to your comment

one peace of code that i can call at any time.

As I said before, every reply to your original question by each contributor was a valid response. It wasn't what you wanted to hear but that doesn't make any of it negative.

Nobody has criticised you personally. Now please stop making unnecessary and unjustified criticisms of others.

 

[cage4000]

Hi static, I’m not sure, I thought I was but I must be off. I will take a look at that. Thank you for pointing that out.

ridders, I disagree with you, the proof is black and white on this thread, and I’m done debating this with you. NOW please stop posting on this thread unless you have something constructive to add. If you do keep going on about this than all you are proving is that, you need to have the last word, not that you are right.

 

[isladogs]

ridders, I disagree with you, the proof is black and white on this thread, and I’m done debating this with you. NOW please stop posting on this thread unless you have something constructive to add. If you do keep going on about this than all you are proving is that, you need to have the last word, not that you are right.

I expect my wife would say there's some truth in your last sentence.

As I evidently haven't learned how to be polite and show humility even after 4500+ posts and over 1100 thanks, I would be extremely grateful if you could give up a little of your valuable time to point out some of the proof that you indicate is in black and white on this thread.
Doing that will I'm sure help me to provide more valuable assistance for other users in the future
No need to refer to posts 16 & 17 again which as both minty and I have explained wasn't intended as a joke at your expense though you interpreted it as that.
I look forward to your helpful advice on this matter if you would be so kind.

 

[Frothingslosh]

You know, Minty is an Access expert, Galaxiom is a freaking master with this application, Doc was a goddamn Microsoft MVP (as in the guy who TAUGHT the masters), and ridders not only runs a business developing Access applications for schools and other businesses (and is pretty much a master himself), but spends most of his evenings here helping people just because he can. He's also one of the most even-tempered, NON-condescending people I've seen post here.

You are, quite honestly, completely out of line, and doing nothing but killing your chances of getting effective help either here or on any other Access specific site, since most of the volunteers here are the main volunteers on the other sites, too.

As a rule, when those four tell you somethihng either cannot be done or is otherwise just a bad idea, then you should listen to them. In fact, they are trying to HELP you by teaching you to make your app faster, more responsive, and way, way more stable. You should, perhaps, accept that the approach you're taking is not going to be feasible and try their suggestions.

As to your questions on this thread (assuming you've read past the above):

Any recordset, whether built into the form or created and attached during the Form_Open event, is closed when that form is closed unless you have given it global scope. It's possible that assigning it to a static variable will keep it open as well, but I've never felt the need to do something that silly.

Where I work, I have seen precisely one application that does what you're trying to do. It relies on a 5000+ line module that creates, updates, and maintains asynchronous ADO connections to DB2 databases, and it was poorly written, completely undocumented, and damned near beyond *MY* ability to figure it out (and while I'm not in the same weight class as the other four posters, I'm no slouch). It created links as database objects and held them in memory when the database was opened, and just generally made the application far less efficient, since it was mostly just doing things Access already does. And as I work with tables with hundreds of fields and billions of records (don't look at me like that Galaxiom, I didn't build them), it just KILLED performance. You should NEVER use that kind of approach if there's any possible way to avoid it.

Why precisely aren't you allowed to create links to the server in your app? You claim you don't have admin rights, but you don't need them just to create a link. If your concern is maintaining a DSN on each machine, we can teach you both how to set up DSN-less connections, and methods to have the application set them up itself so you don't have to do a thing.

Once you've done that, you can just put the necessary sql into each form's RecordSource property (or use saved queries), and let Access handle the opening and closing of each recordset. (Alternately, we can show you better ways to attach a recordset to a form when it opens - I have a few forms with no permanent recordset assigned and instead assign one as part of Form_Open, but it's due to very specific business requirements.)

Basically, stop being so defensive and work with us. Just because you're being told that what you want isn't feasible, it doesn't follow that a better solution can't be found.

 

[cage4000]

Thank you Frothingslosh for the explanation and a appreciate all you and all of the time your co posters put into this problem. you have given me a lot to think about and i will look at this as a viable solution.

i guess the only thing i don't understand is that if a recordset closes when you close out the form why does my database keep telling me that i can not save design changes and that i have to be in exclusive mode after i close out of the form. according to every response i have received on this thread, it should not do that. i don't have any other forms open, no code running but it keeps locking up after i open the form. all i want it to do is for it to stop locking up on me and making me have to close the program all tougher just to be able to edit the forms or code again.

 

[Frothingslosh]

Design changes can be caused by ANY modification, including, say, changing the width of a column if you use datasheet view. The easiest way around that particular message box is to simply disable the close button in each form's properties, and then provide a command button that does the same thing. The code attached to the button is either this:

DoCmd.Close , , acSaveNo

or, if you want to put in all the optional parameters, this:

DoCmd.Close acForm, Me.Name, acSaveNo

The recordsets have nothing to do with whether or not you can make design changes. In fact, unless you've attached the recordset to a variable that remains in-scope with the form closed, it's not even open when you have the form in design mode!

(On a side note, if you create any object and assign it to a variable, it's good practice to clean them up when you're done with them. Access does a good job of cleaning up when you close forms, but it's not always perfect. It is SUPPOSED to close recordsets assigned to variables and set all object references to Nothing automatically when a form closes, but in certain situations (especially when errors get thrown), it fails to do that. As a rule of thumb and just to play it safe, anything that gets opened should be closed when you're done with it, and any assigned object variable should be set to nothing. Some people (*cough*IncludingSomeModsHere*cough*) will argue that there's no need to close recordsets created during the running procedure, but I personally prefer to play it safe - it takes just a second or so to type out 'rs.Close', after all.)

As to the 'exclusive' thing, is anyone else using it at the same time? If anyone else at all is in the database, then you cannot save changes to it. If that's not the issue, then our normal next recommendation is to post a stripped down version of the database (just a form where you have the issue, any necessary code the form requires to work, and dummy data for it) so that people can take a look at it.

 

[The_Doc_Man]

why does my database keep telling me that i can not save design changes and that i have to be in exclusive mode after i close out of the form

I said I would stay out of this because I had nothing else to offer, but that turns out to be wrong. I can take a shot at that specific question because the way you framed it clarifies something that I apparently missed earlier.

When you directly assign a new recordset to an open form, it is an action that carries some baggage with it. I think that baggage is the source of your problem.

Your .Recordset change implies subsequent changes to the .RecordSource, .RecordType, and a couple of other properties of the form that would normally be changed on the form's Properties (design-mode) window. The action you are performing with that overlay of Me.Recordset therefore IS a design change. The problem is that you have the form open and actively running code in Form View mode, not in Design mode, and therefore you have some repercussions to address.

In that context, Access is telling you that you can't make that change AND SAVE IT unless you are in Design mode (which you are not) and have the database open in Exclusive mode (again, apparently not the case). The complaint stems from the fact that a "default" Close operation implies the SAVE option. For Microsoft, think about this: Every other Office program (Word, Powerpoint, Excel, etc) saves a document when you close it because that is the default for them. Why would Access be different?

You can change the .ControlSource or .RowSource properties on a form dynamically, but when I last did that, I had to take the approach described by Frothy. I built a CLOSE command button that included an acSaveNo as part of its closure command so that Access would quietly discard all of those source-record changes and revert to my "default" sources. I trapped ALL closures of the form using the Form_Unload event (which has a Cancel option) and disallowed closure unless you used the CLOSE command button to do it. The CLOSE command button "click" event set a flag and then issued the form closure via DoCmd. The other methods of form closure, because they didn't set the flag, got cancelled. FYI, it was adequate to put that flag in the Form's Declaration area at the top of the form's class module.

Frothy's comments about "close what you open" are not 100% popular because there is some truth to the idea that many objects automatically close when their base variables go out of scope. Therefore, your recordset variable in that subroutine would go out of scope the moment you hit the End Sub.

You are playing with a tricky situation - adding multiple references to an open recordset. By that I mean that temporarily, your recordset variable and your form's recordset property are BOTH pointing to the same recordset structure (since you didn't declare a NEW in any part of the process). So a "reference" counter gets ticked for each entity that has that same recordset open. I would as a matter of principle just close the variable-based recordset before I exited the routine. But then again, I am (probably even rightly) accused at times of being somewhat anal-retentive about that. But I happen to second Frothy's suggestion to close what you open.

 

[cage4000]

Thank you for both the insight and clarification of what my database is doing behind the scenes. This fixed my issue and I am able to move forward. Being self-taught, I find these insights extremely valuable and I will not forget them. In addition, on my next project I will follow all your advice on building a DSN-less connection so that my architecture is not as messy and clunky. Thank you both again for your time and for effort on this issue.