Error msg "You can't carry out this action at the present time"

I'm using VB as my front end, which includes a data entry form.  The data is stored in an Access database.
 
Before exiting the VB form I'd also like to run an Access macro stored in this currently opened database.  

My VB code is as follows:

Set myAccess = New Access.Application
myAccess.Visible = True
myAccess.DoCmd.RunMacro "welcomeletter"

An error message displays as follows...

"You can't carry out this action at the present time"

How do I go about resolving this?

Suz123
suz123Asked:
Who is Participating?
 
aaronkempfConnect With a Mentor Commented:
yeah... i personally dont know where you have MSACCESS.exe installed...

and im horribly sorry, I didnt include the /X...

"C:\Program Files\Microsoft Office\Office10\MSACCESS.EXE" C:\_AARON\ElJeffe.mdb /X DailyProcess
0
 
aaronkempfCommented:
why dont you just call the macro through the CLI?

shell("C:\Program Files\Office\Msaccess.exe" C:\MyDatabase.mdb /MACRONAME")

ive done it a zillion times. it works like a charm.

but i dont understand-- if you are building a VB app that depends on the existence of Access--- why dont you just build this whole thing in Access???

It is the best platform out there (everyone knows that it is much better performing & easier/quicker to develop with than VB)
0
 
Richie_SimonettiIT OperationsCommented:
I agree, but it is scary the first time you try ;)
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
suz123Author Commented:
I'll try the shell instruction.

The macro add on is a simple program enhancement to an  existing VB application, therefore no way would I want to redo everything in Access.  

Back to you soon.
0
 
aaronkempfCommented:
send it to me then and i will..

ive seen many resources that claim the Access is 3x as easy to develop with as VB.

i believe it. i love it.

i know that it is true-- especially for Access Data Projects & SQL Server.

DotNet is fine and good and it will finalyl begin to make VB a viable platform for application development..

but for now i just think that it has no benefits over Access & thats why Im employed and I have a dozen VB friends that aren't..

At least thats the way i see it..
0
 
suz123Author Commented:
I can't seem to get the shell to work.  Can you confirm the syntax for me?  

Thanks a bunch.

Suz123
0
 
Richie_SimonettiIT OperationsCommented:
You don't need to pass Access path. As an example:
Shell "start C:\Misdoc~1\copyprot.mdb", vbNormalFocus
This works for every file that has an associated program to run.
0
 
suz123Author Commented:
Aaron

Shell ("C:\Program Files\Microsoft Office\office\Msaccess.exe" C:\cmcodes3.mdb /x welcomeletter)

Still getting a syntax error.

Suz
0
 
suz123Author Commented:
Richie

Shell "start C:\cmcodes3.mdb /x welcomeletter", vbNormalFocus

I'm getting error 53 "file not found" yet the mdb file does esist in this location.

Suz
0
 
Richie_SimonettiIT OperationsCommented:
I tryed the code before post it and it works...
0
 
Richie_SimonettiIT OperationsCommented:
optionally, you could use the ShellExecute API that works fine.

'#############################################################
'# This code was written by Emmett Dixson (c)1999. You may alter
'# this code, trade, steal, borrow, lend or give away this code.
'# However, this code has been regisered with the Library of
'# Congress as a literary acheivement and as such excludes it
'# from being known or proclaimed as "PUBLIC DOMAIN".
'#---------------You may NOT remove this header---------------
'#------------------You may NOT SELL this work----------------
'#----YES! You MAY use this work for commercial purposes------
'#---This code MAY NOT be sold or redistributed for profit----
'#-------- I wish you every success in your projects ---------
'#------------------------ Visit me at -----------------------
'#------------------http://developer.ecorp.net ---------------
'#-----------------FREE Visual Basic Source Code -------------
'##############################################################

'Call Shell("Whatever.txt") 'if the Whatever is in the same
'directory folder as the .EXE that you compile.

'or

'Call Shell("C:\wherever\it\is.htm")

Option Explicit

Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As _
String, ByVal lpFile As String, ByVal lpParameters As String, _
ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

'For best results paste everything into a NEW MODULE and be sure
'you SAVE the module to your project.

'Works for Win3.x, Win95,Win98,WinNT and EVEN Win2000(don't ask!)

'Don't change anything...just paste everything into ONE
'MODULE that you can add to a project.
           
Function Shell(Program As String, Optional ShowCmd As Long = _
vbNormalNoFocus, Optional ByVal WorkDir As Variant) As Long

    Dim FirstSpace As Integer, Slash As Integer

    If Left(Program, 1) = """" Then
        FirstSpace = InStr(2, Program, """")


        If FirstSpace <> 0 Then
            Program = Mid(Program, 2, FirstSpace - 2) & _
              Mid(Program, FirstSpace + 1)
            FirstSpace = FirstSpace - 1
        End If

    Else
        FirstSpace = InStr(Program, " ")
    End If

    If FirstSpace = 0 Then FirstSpace = Len(Program) + 1

    If IsMissing(WorkDir) Then

        For Slash = FirstSpace - 1 To 1 Step -1
            If Mid(Program, Slash, 1) = "\" Then Exit For
        Next

        If Slash = 0 Then
            WorkDir = CurDir
        ElseIf Slash = 1 Or Mid(Program, Slash - 1, 1) = ":" Then
            WorkDir = Left(Program, Slash)
        Else
            WorkDir = Left(Program, Slash - 1)
        End If

    End If

    Shell = ShellExecute(0, vbNullString, _
    Left(Program, FirstSpace - 1), LTrim(Mid(Program, _
    FirstSpace)), WorkDir, ShowCmd)
    If Shell < 32 Then VBA.Shell Program, ShowCmd 'To raise Error

End Function


0
 
suz123Author Commented:
Richie,

Yes the code works and so does the following providing the database is not opened.  

s = "Cmd /k " & "C:\cmcodes3.mdb /x welcomeletter"
Shell s

But in this case the database is already opened by the existing VB application.   The error message indicates that the  "process can not access the file because it is being used by another process".

What code changes are required to run a macro in a currently opened database rather than from a closed one?

Suz
 




0
 
aaronkempfCommented:
ok suzie

Shell ("C:\Program Files\Microsoft Office\office\Msaccess.exe" C:\cmcodes3.mdb /x welcomeletter)


you need to:
a). replace this:
C:\Program Files\Microsoft Office\office\Msaccess.exe
    with the path to MSACCESS.exe (i dont know where it is on your machine)
b). replace this:
C:\cmcodes3.mdb
    with the path to your MDB
c). replace this:
welcomeletter
    with the name of the macro

BTW, my offer to recreate this in MSACCESS is still standing-- if you require the app to open Access through Automation; i dont see _any_ benefit to using VB.
0
 
suz123Author Commented:
Aaron,

The Shell syntax is already correct.  The msAccess executable in this folder, the database is at the root of C: and the macro name is welcomeletter.

As to your comment pertaining to a rewrite in Access...

I too am fluent in Access but a rewrite is not the answer in this situation.  I will in the near future require other shell commands to access other prewritten reports and macros, therefore I need to code to work in VB.  
This VB program has some data stored in the Access database and some data is in Random Access files and still other data is being transferred back and forth between VB and ASP on our web site.

Suz
0
 
aaronkempfCommented:
you can shell from access.

do you have access installed on this machine?

i know that the path to my Access.exe is
C:\Program Files\Microsoft Office\Office10

i was thinking that the path to MSACCESS.exe on a 2000 machine included the folder 1033 (for English)
0
 
aaronkempfCommented:
sorry i wasnt reading i guess.

you probably need to try to open the database readonly.

i know that this is another tag, i believe /RO

0
 
Richie_SimonettiIT OperationsCommented:
Do a last try:

Private Declare Function ShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As _
String, ByVal lpFile As String, ByVal lpParameters As String, _
ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

shellexecute 0,"open", "C:\cmcodes3.mdb","/x welcomeletter",vbnullstring,vbnormal
0
 
suz123Author Commented:
Aaron

Yes, Access is installed on the PC that is running the VB program.  

I tried the /RO parameter and the message is the same as before...

"The process cannot access the file because it is being used by another process."

Suz
0
 
suz123Author Commented:
Richie,

I will try your suggestion.

I have since discovered that VB passes control to Access when the syntax is changed as follows...

Shell "C:\Program Files\Microsoft Office\Office\Msaccess.exe C:\cmcodes3.mdb /x welcomeletter", vbNormalFocus

The complete shell syntax needs to be enclosed within one set of double quotes and by adding   , vbNormalFocus


rather than the database and macro name outside the quotes as seen below....

Shell ("C:\Program Files\Microsoft Office\office\Msaccess.exe" C:\cmcodes3.mdb /x welcomeletter)
 
Suz
0
 
Richie_SimonettiIT OperationsCommented:
take note that sometimes shell function doesn't "like" long filenames as argument.
So , surrounding with double-quotes could solve the problem.
0
 
suz123Author Commented:
Aaron,

The code you provided was the right code although the syntax needed to be altered as follows..

Shell "C:\Program Files\Microsoft Office\Office\Msaccess.exe C:\cmcodes3.mdb /x welcomeletter", vbNormalFocus

Worked like a dream.  Thanks for your assistance.

Suz
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.