?
Solved

Access not closing when using VB script

Posted on 2008-06-11
18
Medium Priority
?
592 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
  • +2
18 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 21760788
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
ID: 21761016
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
ID: 21761139
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 75
ID: 21761348
Where do you define

AccessApp

?

mx
0
 
LVL 28

Expert Comment

by:omgang
ID: 21761376
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
ID: 21813845
I didn't have success with these suggestions.  I also think this last post is a little sarcastic, not very helpful.
0
 
LVL 75
ID: 21814096
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
ID: 21814154
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
 
LVL 75
ID: 21814274
This is email. There are no vocal inflections.  It's easy to mis-interpret someone's 'tone'.  

mx
0
 

Author Comment

by:bsncp
ID: 21814386
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
ID: 21814472
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
ID: 21815098
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
ID: 21815139
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
ID: 21815179
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 1000 total points
ID: 21815398
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
ID: 21815620
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
ID: 21983259
thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

777 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