Solved

Access not closing when using VB script

Posted on 2008-06-11
18
585 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
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
 
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 250 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

867 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

17 Experts available now in Live!

Get 1:1 Help Now