Running an Access 2003 macro from VB Script

  • Thread starter Thread starter Brad Baker
  • Start date Start date
B

Brad Baker

I'm trying to schedule a VB script to run via task manager. The script
should kick off an access macro and then close down when done. However I am
getting the two following error messages when I try to run the script:

-------------------------
(i) You can't exit Microsoft Office Access now.
If you're running a Visual Basic module that is using OLE or DDE, you may
need to interupt the module.
-------------------------

-------------------------
(i) The RunMacro action was canceled.
You used a method of the DoCmd object to carry out an action in Visual Basic
but then clicked Cancel in a dialog box.

For example, you used the Close method to close a changed form, then clicked
Cancel in the dialog box that asks if you want to save the changes you made
to the form.

[OK]
-------------------------

Below is my VB script. Maybe I'm coding something wrong? Could anyone
provide any suggestions?

Thanks
Brad


Dim DBName, MacroName
DBName = WScript.Arguments(0)
MacroName = WScript.Arguments(1)

On Error Resume Next
Dim AcApp
Set AcApp = CreateObject("Access.Application")
If Val(AcApp.Version) >= 11 Then
AcApp.AutomationSecurity = 1 ' msoAutomationSecurityLow
End If
AcApp.Visible = True
AcApp.OpenCurrentDatabase DBName
If AcApp.CurrentProject.FullName <> "" Then
AcApp.UserControl = True
Else
AcApp.Quit
MsgBox "Failed to open '" & DBName & "'."
End If
AcApp.DoCmd.RunMacro MacroName
acApp.CloseCurrentDatabase
AcApp.Close
AcApp.Quit
Set appAcc = Nothing
 
I believe you need to introduce a wait in there to allow the macro to finish
before trying to close the database.

That having been said, I don't know how you'd do that through VBScript.
 
I considered that, but from what I can tell the macro is finishing (I can
see the macro running in access after I call the script). It doesn't close
immediately....

Brad


Douglas J. Steele said:
I believe you need to introduce a wait in there to allow the macro to
finish before trying to close the database.

That having been said, I don't know how you'd do that through VBScript.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Brad Baker said:
I'm trying to schedule a VB script to run via task manager. The script
should kick off an access macro and then close down when done. However I
am getting the two following error messages when I try to run the script:

-------------------------
(i) You can't exit Microsoft Office Access now.
If you're running a Visual Basic module that is using OLE or DDE, you may
need to interupt the module.
-------------------------

-------------------------
(i) The RunMacro action was canceled.
You used a method of the DoCmd object to carry out an action in Visual
Basic but then clicked Cancel in a dialog box.

For example, you used the Close method to close a changed form, then
clicked Cancel in the dialog box that asks if you want to save the
changes you made to the form.

[OK]
-------------------------

Below is my VB script. Maybe I'm coding something wrong? Could anyone
provide any suggestions?

Thanks
Brad


Dim DBName, MacroName
DBName = WScript.Arguments(0)
MacroName = WScript.Arguments(1)

On Error Resume Next
Dim AcApp
Set AcApp = CreateObject("Access.Application")
If Val(AcApp.Version) >= 11 Then
AcApp.AutomationSecurity = 1 ' msoAutomationSecurityLow
End If
AcApp.Visible = True
AcApp.OpenCurrentDatabase DBName
If AcApp.CurrentProject.FullName <> "" Then
AcApp.UserControl = True
Else
AcApp.Quit
MsgBox "Failed to open '" & DBName & "'."
End If
AcApp.DoCmd.RunMacro MacroName
acApp.CloseCurrentDatabase
AcApp.Close
AcApp.Quit
Set appAcc = Nothing
Click to expand...
Click to expand...
 
Hello Brad,

I agree with Douglas that some delay might help. You may want to add delay
before and after macro running to test the situation

wscript.sleep 5000
AcApp.DoCmd.RunMacro MacroName
wscript.sleep 5000

Also, I'd like to know if the issue only occurs with a specific macro? If
you use some simple macro to test, does the issue persists?

If you have any update, please feel free to let's know. Thanks.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top