Access not closing when using VB script

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

bsncpAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
omgangConnect With a Mentor IT ManagerCommented:
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
 
omgangIT ManagerCommented:
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
 
clarkscottCommented:
Make sure all you objects are set to nothing before quitting.  Anything still 'alive' will cause your Access issue.

Scott C
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
bsncpAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Where do you define

AccessApp

?

mx
0
 
omgangIT ManagerCommented:
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
 
bsncpAuthor Commented:
I didn't have success with these suggestions.  I also think this last post is a little sarcastic, not very helpful.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I really don't think OMG's comment was sarcastic.  Also, you never answered my question:

Where do you define

AccessApp

?

mx
0
 
bsncpAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
This is email. There are no vocal inflections.  It's easy to mis-interpret someone's 'tone'.  

mx
0
 
bsncpAuthor Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
 
PaulHewsCommented:
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
 
PaulHewsCommented:
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
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
LOL.  MAN ... I must have been staring at that waaaaaay to loooooooong, cuz I missed both of those obvious things!!  Geeze !!!

mx
0
 
bsncpAuthor Commented:
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
 
bsncpAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.