Solved

Access not closing when using VB script

Posted on 2008-06-11
18
584 Views
Last Modified: 2013-11-27
I use Windows Task Scheduler to call an Access Visual Basic script file that runs an Access macro each night.  When I come in each morning, the database is closed, but Access is still open.  When I try to exit, the window just blanks and Access re-appears.  The only way to exit is to kill the application in Task Manager.  I am using the Close method for the Access application...is this incorrect?  The macro is extensive and I may need to look in some of the macro steps to see if I am causing Access to hang somwewhere, so any guidance there is much appreciated.
Const DBPath = "P:\Databases\GoldmineUpdating.mdb"

Const MacroName = "01_mcrUpdateSurvAcctInfo"
 

Set AccessApp = GetObject(DBPath, "Access.Application")

AccessApp.DoCmd.RunMacro MacroName

AccessApp.Quit

Open in new window

0
Comment
Question by:bsncp
  • 6
  • 5
  • 3
  • +2
18 Comments
 
LVL 28

Expert Comment

by:omgang
Comment Utility
Couple of things you might try:

add to your Access macro the Quit command as the last command
destroy the AccessApp object variable in your script, i.e. Set AccessApp = Nothing

OM Gang
0
 
LVL 20

Expert Comment

by:clarkscott
Comment Utility
Make sure all you objects are set to nothing before quitting.  Anything still 'alive' will cause your Access issue.

Scott C
0
 

Author Comment

by:bsncp
Comment Utility
I've added the Quit command to the Access macro.  I've also set the objects to nothing in my script.  See below.  Now I get a scripting host error on line 5, character 1 that says "...the RunMacro action was canceled..."
Const DBPath = "P:\Databases\GoldmineReporting.mdb"

Const MacroName = "Test2"
 

Set AccessApp = GetObject(DBPath, "Access.Application")

AccessApp.DoCmd.RunMacro MacroName

Set AccessApp = Nothing

Set MacroName = Nothing

AccessApp.Quit

Open in new window

0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Where do you define

AccessApp

?

mx
0
 
LVL 28

Expert Comment

by:omgang
Comment Utility
And I'm pretty sure you're not going to be able to quit the AccessApp object after it no longer exists.

OM Gang
0
 

Author Comment

by:bsncp
Comment Utility
I didn't have success with these suggestions.  I also think this last post is a little sarcastic, not very helpful.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
I really don't think OMG's comment was sarcastic.  Also, you never answered my question:

Where do you define

AccessApp

?

mx
0
 

Author Comment

by:bsncp
Comment Utility
I didn't define it...I corrected that and still it didn't work.  I think the issue is in one my other scripts where I am not setting the objects to nothing.  I've triple-checked, though, so I'm definitely missing something.

If that comment isn't sarcastic, I don't know what is.  If there is clearly something wrong with the script, how about showing me the correct syntax?  A flippant comment has no place in a "help" forum.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
This is email. There are no vocal inflections.  It's easy to mis-interpret someone's 'tone'.  

mx
0
 

Author Comment

by:bsncp
Comment Utility
I'll grant you that...so maybe we can then debate its merits as helpful input.  No sweat on this end...I was just trying to give feedback to the gurus.  In this case, I was looking not only for what was wrong...but a suggestion as to what is right.  I only got the first half of that.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Neither of use know what 'is right', or of course we would have suggested something.  It's one of those off  the wall errors that can be hard to track down.

Sorry ...
0
 
LVL 38

Expert Comment

by:PaulHews
Comment Utility
I'm pretty sure that all omgang meant was that you can't use the Quit method after you've set the object to nothing:

Const DBPath = "P:\Databases\GoldmineReporting.mdb"
Const MacroName = "Test2"
 
Set AccessApp = GetObject(DBPath, "Access.Application")
AccessApp.DoCmd.RunMacro MacroName
AccessApp.Quit
Set AccessApp = Nothing
Set MacroName = Nothing

>The macro is extensive and I may need to look in some of the macro steps to see if I am causing Access to hang somwewhere, so any guidance there is much appreciated.

I'm pretty sure the problem lies with the Macro.  Setting the object variables = nothing has no effect on whether access will hang when using automation.  If we saw more of the macro code, we might be able to help you more.

0
 
LVL 38

Expert Comment

by:PaulHews
Comment Utility
Oh, and this statement:

Set MacroName = Nothing

Shouldn't be there... Just delete it.  (You can't change the value of a constant, and it will cause an error.)
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
LOL.  MAN ... I must have been staring at that waaaaaay to loooooooong, cuz I missed both of those obvious things!!  Geeze !!!

mx
0
 
LVL 28

Accepted Solution

by:
omgang earned 250 total points
Comment Utility
Sorry for the confusion!

Set AccessApp = Nothing
Set MacroName = Nothing
AccessApp.Quit

you are destroying the object variable AccessApp and then attempting to execute a method of the same object.  this should throw an error.

wouldn't we be remiss if we did not point out errors in you code other than those pertaining to the specific Q?
OM Gang
0
 

Author Comment

by:bsncp
Comment Utility
Agreed.  I definitely appreciate the error identifying!  I've tried the corrected syntax and still have this issue.  I don't think the issue is with this script...I believe it is related to some of the codes within the macro.  I've gone through all the scripts in each step and believe I've set all objects = Nothing in each one.  So either my weary eyes are missing one of those or there is some other kind of error.
0
 

Author Comment

by:bsncp
Comment Utility
thanks
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now