Shell() specifics... 1

[Robotron]

I am using the following code to open an access database with the Shell function...

Private Sub cmdOpenDatabase_Click()

    Dim strPath As String
    Dim RetVal As Double

    strPath = "c:\progra~1\micros~2\office10\msaccess.exe " & Me.Location
    RetVal = Shell(strPath, 1)
    
End Sub

I get an error when Me.Location = "D:\Documents and Settings\tex1pde\My Documents\EmpCencus.mdb"

I believe it has something to do with the spaces in 'Documents and Settings' and 'My Documents'. Any help appreciated.

Phil Edards

 

[PHV]

Have you tried this:
strPath = "c:\progra~1\micros~2\office10\msaccess.exe " & Chr(34) & Me.Location & Chr(34)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lewds]

Hi..
not sure here

is Me.Location a textbox ?

Should the assigment be,

strPath = "c:\progra~1\micros~2\office10\msaccess.exe " & Me.Location.Text

 

[Robotron]

Me.Location is a text box. This works on other paths. The only path I have encountered a problem with is the one I listed above. Again, I think its the spaces.

Phil Edwards

 

[CajunCenturion]

You might want to try the following:

RetVal = Shell(chr$(34) & strPath & chr$(34), 1)

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Robotron]

How is chr$(34) going to help?

Phil Edwards

 

[CajunCenturion]

By enclosing the entire command line in quotes, the command interpreter will not use any spaces between the quote marks as separators between parameters.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Robotron]

Ok. Got it. When I did that, it opens up Access but fails to open the particular database that I supplied which is the value in Me.Location...
"D:\Documents and Settings\tex1pde\My Documents\EmpCencus.mdb"

Any other ideas?

Phil Edwards

 

[CajunCenturion]

Can you show us the code that actually used?

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Robotron]

    Dim strPath As String
    Dim RetVal As Double
    
    strPath = "c:\progra~1\micros~2\office10\msaccess.exe " & Me.Location
    RetVal = Shell(Chr$(34) & strPath & Chr$(34), 1)

This is the code I am using.

Phil Edwards

 

[Robotron]

Actually, I get the message 'Invalid procedure call' when I run that.

Phil

 

[CajunCenturion]

Did you try:

strPath = "c:\progra~1\micros~2\office10\msaccess.exe " & chr(34) & Me.Location & chr(34)

and I would change the declaration of RetVal from double to long.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Robotron]

Ok, I changed it. I now get the message 'The command line you used to start Microsoft Access contains an option that Microsoft Access does not recognize.' I get that message 3 times and then I get 'Microsoft Access can't find the database file '\Documents.mdb'.' This is strange considering the fact that the file is named EmpCencus.mdb.

Phil Edwards

 

[CajunCenturion]

Give this a shot:

strPath = "c:\progra~1\micros~2\office10\msaccess.exe " & chr(34) & "D:\Documents and Settings\tex1pde\My Documents\EmpCencus.mdb" & chr(34)
RetVal = Shell(strPath, 1)

If that works, then your problem seems to be in the value of Me.Location.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Robotron]

I finally got this to work. I used the following code from PlanetSourceCode.com.

http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=761&lngWId=1

Thanks for your help.

Phil Edwards

 

[CajunCenturion]

Glad you got it working.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[SiJP]

Another way to open an access database that I find useful, which uses the windows API for opening any file type...

(Code credits within module)

Create a module with the following:

'****************************************************************
'
' Description:
' To enable opening a Word/Excel/Picture/Any Other File from Access
'
' Dependancies:
' None
'
' History:
' 17/05/04      SiJP       Created module
'
' Credits:
' Code Courtesy of Dev Ashish, [URL unfurl="true"]http://www.mvps.org/access/[/URL]
'
'****************************************************************


Private Declare Function apiShellExecute 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

Public Const WIN_NORMAL = 1         'Open Normal
Public Const WIN_MAX = 2            'Open Maximized
Public Const WIN_MIN = 3            'Open Minimized

Private Const ERROR_SUCCESS = 32&
Private Const ERROR_NO_ASSOC = 31&
Private Const ERROR_OUT_OF_MEM = 0&
Private Const ERROR_FILE_NOT_FOUND = 2&
Private Const ERROR_PATH_NOT_FOUND = 3&
Private Const ERROR_BAD_FORMAT = 11&

Function fHandleFile(stFile As String, lShowHow As Long)
Dim lRet As Long, varTaskID As Variant
Dim stRet As String
    'First try ShellExecute
    lRet = apiShellExecute(hWndAccessApp, vbNullString, _
            stFile, vbNullString, vbNullString, lShowHow)
            
    If lRet > ERROR_SUCCESS Then
        stRet = vbNullString
        lRet = -1
    Else
        Select Case lRet
            Case ERROR_NO_ASSOC:
                'Try the OpenWith dialog
                varTaskID = Shell("rundll32.exe shell32.dll,OpenAs_RunDLL " _
                        & stFile, WIN_NORMAL)
                lRet = (varTaskID <> 0)
            Case ERROR_OUT_OF_MEM:
                stRet = "Error: Out of Memory/Resources. Couldn't Execute!"
            Case ERROR_FILE_NOT_FOUND:
                stRet = "Error: File not found.  Couldn't Execute!"
            Case ERROR_PATH_NOT_FOUND:
                stRet = "Error: Path not found. Couldn't Execute!"
            Case ERROR_BAD_FORMAT:
                stRet = "Error:  Bad File Format. Couldn't Execute!"
            Case Else:
        End Select
    End If
    fHandleFile = lRet & _
                IIf(stRet = "", vbNullString, ", " & stRet)
End Function

Then...

 Call fHandleFile("My database path and name", WIN_NORMAL)

------------------------
Hit any User to continue

 

[Robotron]

Seems like more code than is necessary SiJP.

Phil Edwards

 

[SiJP]

There does seem to be, but when you consider the re-usability of it, it is worth it.

By simply allowing your application to invoke any program/file directly from your database you're giving it serious abilities

But I suppose it depends on what you are trying to acheive.. hoopefully if you do come across the need in the future to do something fo the sort, then this will help you

SiJP

------------------------
Hit any User to continue

 

[CajunCenturion]

Robotron - There's not that much code there, in fact, the key statement in the following line:

lRet = apiShellExecute(hWndAccessApp, vbNullString, stFile, vbNullString, vbNullString, lShowHow)

Every thing else is either comments, declarations, and error handling.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein