Solved

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

Posted on 2002-05-17
21
367 Views
Last Modified: 2010-05-18
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
0
Comment
Question by:suz123
  • 9
  • 6
  • 6
21 Comments
 
LVL 1

Expert Comment

by:aaronkempf
ID: 7017353
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 7017428
I agree, but it is scary the first time you try ;)
0
 

Author Comment

by:suz123
ID: 7017539
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
 
LVL 1

Expert Comment

by:aaronkempf
ID: 7017546
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
 

Author Comment

by:suz123
ID: 7017574
I can't seem to get the shell to work.  Can you confirm the syntax for me?  

Thanks a bunch.

Suz123
0
 
LVL 1

Accepted Solution

by:
aaronkempf earned 150 total points
ID: 7017584
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 7017905
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
 

Author Comment

by:suz123
ID: 7018125
Aaron

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

Still getting a syntax error.

Suz
0
 

Author Comment

by:suz123
ID: 7018129
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 7018480
I tryed the code before post it and it works...
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 7018482
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
 

Author Comment

by:suz123
ID: 7018515
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
 
LVL 1

Expert Comment

by:aaronkempf
ID: 7021844
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
 

Author Comment

by:suz123
ID: 7023871
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
 
LVL 1

Expert Comment

by:aaronkempf
ID: 7025258
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
 
LVL 1

Expert Comment

by:aaronkempf
ID: 7025267
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 7025447
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
 

Author Comment

by:suz123
ID: 7025460
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
 

Author Comment

by:suz123
ID: 7025901
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 7027072
take note that sometimes shell function doesn't "like" long filenames as argument.
So , surrounding with double-quotes could solve the problem.
0
 

Author Comment

by:suz123
ID: 7027140
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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

706 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

19 Experts available now in Live!

Get 1:1 Help Now