Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-24-2018, 06:41 AM   #16
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,612
Thanks: 136
Thanked 1,516 Times in 1,488 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Closing a Recordset from another sub

Quote:
Originally Posted by cage4000 View Post
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

__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is online now   Reply With Quote
Old 04-24-2018, 06:45 AM   #17
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,591
Thanks: 89
Thanked 1,619 Times in 1,508 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Closing a Recordset from another sub

Quote:
Originally Posted by minty View Post
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

Last edited by isladogs; 04-24-2018 at 07:23 AM.
isladogs is offline   Reply With Quote
Old 04-24-2018, 12:45 PM   #18
static
Newly Registered User
 
Join Date: Nov 2015
Posts: 823
Thanks: 4
Thanked 192 Times in 179 Posts
static will become famous soon enough static will become famous soon enough
Re: Closing a Recordset from another sub

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.

static is offline   Reply With Quote
Old 04-24-2018, 01:41 PM   #19
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,307
Thanks: 62
Thanked 1,150 Times in 1,051 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Closing a Recordset from another sub

Quote:
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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 04-24-2018, 01:42 PM   #20
cage4000
Newly Registered User
 
Join Date: Oct 2015
Posts: 49
Thanks: 16
Thanked 0 Times in 0 Posts
cage4000 is on a distinguished road
Re: Closing a Recordset from another sub

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?
cage4000 is offline   Reply With Quote
Old 04-24-2018, 01:51 PM   #21
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,591
Thanks: 89
Thanked 1,619 Times in 1,508 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Closing a Recordset from another sub

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Web links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 04-24-2018, 02:52 PM   #22
cage4000
Newly Registered User
 
Join Date: Oct 2015
Posts: 49
Thanks: 16
Thanked 0 Times in 0 Posts
cage4000 is on a distinguished road
Re: Closing a Recordset from another sub

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.

cage4000 is offline   Reply With Quote
Old 04-24-2018, 02:59 PM   #23
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,307
Thanks: 62
Thanked 1,150 Times in 1,051 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Closing a Recordset from another sub

Quote:
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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 04-24-2018, 03:02 PM   #24
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,591
Thanks: 89
Thanked 1,619 Times in 1,508 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Closing a Recordset from another sub

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Web links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 04-24-2018, 03:10 PM   #25
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,307
Thanks: 62
Thanked 1,150 Times in 1,051 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Closing a Recordset from another sub

Quote:
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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 04-24-2018, 03:18 PM   #26
cage4000
Newly Registered User
 
Join Date: Oct 2015
Posts: 49
Thanks: 16
Thanked 0 Times in 0 Posts
cage4000 is on a distinguished road
Re: Closing a Recordset from another sub

Thank you Doc i appreciate your feedback.

ridders, i disagree. Also, I thought you killed your connections to this thread.
cage4000 is offline   Reply With Quote
Old 04-25-2018, 01:08 AM   #27
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,612
Thanks: 136
Thanked 1,516 Times in 1,488 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Closing a Recordset from another sub

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.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is online now   Reply With Quote
Old 04-25-2018, 06:03 AM   #28
cage4000
Newly Registered User
 
Join Date: Oct 2015
Posts: 49
Thanks: 16
Thanked 0 Times in 0 Posts
cage4000 is on a distinguished road
Re: Closing a Recordset from another sub

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.
cage4000 is offline   Reply With Quote
Old 04-25-2018, 06:16 AM   #29
static
Newly Registered User
 
Join Date: Nov 2015
Posts: 823
Thanks: 4
Thanked 192 Times in 179 Posts
static will become famous soon enough static will become famous soon enough
Re: Closing a Recordset from another sub

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?
static is offline   Reply With Quote
Old 04-25-2018, 06:44 AM   #30
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,591
Thanks: 89
Thanked 1,619 Times in 1,508 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Closing a Recordset from another sub

Quote:
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:
Quote:
a public procedure so it can be called at any time from any place
was a direct response to your comment
Quote:
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.

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Web links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Reply

Tags
recordset

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
closing this recordset icemonster Modules & VBA 4 04-07-2012 08:29 AM
Closing two Recordset issue Thinh Modules & VBA 23 12-29-2006 12:13 PM
Unwanted additional recordset after closing a form bokib Forms 4 11-15-2006 05:10 AM
Opening and closing the recordset, is there an easier way??? King_kamikaze Modules & VBA 6 02-10-2006 05:35 AM
Keep recordset open from Db start-up until Db closing jayke Modules & VBA 2 03-09-2005 07:17 PM




All times are GMT -8. The time now is 08:34 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World