MS AccessPath needed 2

[dirksm]

Hi All.

Does anybody know how to get the path to Microsoft Access in VB? I've been looking at the registry but I can't find any key that specifically indicates the path. The user could use any version of Access, so I would like to not have my search limited by Access's version number.

This is the code I have so far:

Dim strAppName As String

strAppName = Chr(34) & "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" & Chr(34) & " "

strAppName = strAppName & Chr(34) & App.Path & "\Reports.mdb" & Chr(34)

Call Shell(strAppName, vbMaximizedFocus)

I would like to change the "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" to the actual path of MS Access, because the user might have installed Access in a different folder.

I plan to use the Registry.GetKeyValue function to retrieve the path, but like I said, I don't know at what key to go and look.

Could you please help me?!
(Either with the key, or maybe there's a totally different/better way to do this.)

 

[lovell811]

What are you actually trying to do with access??? Are you wanting just to connect to it and store date in a table or what??

Joe

 

[sunaj]

Hi,
You can use the FindExecutable API. See e.g. thread222-177509 or a more advanced version at

http://216.26.168.92/files/tip522.html

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[Foada]

The Registry Key is under HKEY_LOCAL_MACHINE\Software\Microsoft\Office\9.0\Access\InstallRoot

The problem with that is the version number (9.0 in this case) will vary depending on the version of coarse. You could code to account for the version by enumerating the Office Key and then checking the version number to build the next key search but... you will be better of trying to find the exe. If you choose to battle wits with the witless be prepared to lose.

 

[dirksm]

Thanx Guys!

Just for completeness, I'm calling Access to do some reporting on a DB2 database with a Visual Basic interface.
I've found VB's reporting tool to be a bit limited, so I'm using Access.

Sunaj - This must be like the 100'th time I got some really great help from you! Thanx for everything!

 

[petermeachem]

You could just open your Access mdb. You presumably know where that is. Peter Meachem
peter@accuflight.com

 

[dirksm]

Oh Dear!

I tried the code but I keep on getting an empty string!
Actually, I get a return code of 2, but the MSDN doesn't tell me what that is supposed to mean. Does anybody know what the "two" means? Maybe you also know what I did wrong?

Here's the code:

Private Declare Function FindExecutable Lib "shell32.dll" Alias "FindExecutableA" (ByVal lpFile As String, ByVal lpDirectory As String, ByVal lpResult As String) As Long

Public Function GetExe(file As String) As String
Dim sExec As String * 255
Dim lRetVal As Long
Dim iFN As Integer
Dim sTemp As String
sExec = Space(255)
' Then find the application associated with it.
lRetVal = FindExecutable(file, sTemp, sExec)
' If an application return the name
If lRetVal <= 32 Or IsEmpty(sExec) Then ' Error
Stop
Else
GetExe = Left(Trim$(sExec), Len(Trim$(sExec)) - 1)
End If
End Function

strMyExec=GetExe(&quot;MDB&quot

 

[CajunCenturion]

An error code of 2 means &quot;File Not Found&quot;. The reason that you may be getting this error is that you are not passing a valid pathname to the function.

strMyExec=GetExe(&quot;MDB&quot

&quot;MBD&quot; is probably not a valid pathname. I would try something like the following:

strMyExec=GetExe(&quot;c:\dirname\database.MDB&quot
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[dirksm]

Oh Dear!

I tried the code but I keep on getting an empty string!
Actually, I get a return code of 2, but the MSDN doesn't tell me what that is supposed to mean. Does anybody know what the &quot;two&quot; means? Maybe you also know what I did wrong?

Here's the code:

Private Declare Function FindExecutable Lib &quot;shell32.dll&quot; Alias &quot;FindExecutableA&quot; (ByVal lpFile As String, ByVal lpDirectory As String, ByVal lpResult As String) As Long

Public Function GetExe(file As String) As String
Dim sExec As String * 255
Dim lRetVal As Long
Dim iFN As Integer
Dim sTemp As String
sExec = Space(255)
' Then find the application associated with it.
lRetVal = FindExecutable(file, sTemp, sExec)
' If an application return the name
If lRetVal <= 32 Or IsEmpty(sExec) Then ' Error
Stop
Else
GetExe = Left(Trim$(sExec), Len(Trim$(sExec)) - 1)
End If
End Function

strMyExec=GetExe(&quot;MDB&quot



I tried the suggesttion about just opening the mdb file like this:

strAppName = Chr(34) & App.Path & &quot;\Reports.mdb&quot; & Chr(34)
Call Shell(strAppName, vbMaximizedFocus)

But I get a run-time error '5', 'Invalid Procedure Call', any ideas?

 

[CajunCenturion]

You don't need the quotes

strAppName = App.Path & &quot;\Reports.mdb&quot;
Call Shell(strAppName, vbMaximizedFocus)
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[dirksm]

OK!

I finally get the path to the exe, but it gets cut off at the first space, or so I presume. The result for *.mdb file just gives &quot;C:\Program&quot;, I suppose the &quot;Files\Office\etc.&quot; gets cut of by VB because of the space.

How do I fix this?
Or, following on petermeachem's suggestion, how do I just run the *.mdb file itself? (Without getting the error I mentioned before.)

Thanx for all the help!

 

[CajunCenturion]

As far as opening the MDB file directly, my previous post provided a suggestion of removing the quote strings, but here it is again, with an additional check for a backslash


strAppName = App.Path
If (Right(strAppName, 1) <> &quot;\&quot Then
strAppName = strAppName & &quot;\Reports.mdb&quot;
Else
strAppName = strAppName & &quot;Report.mdb&quot;
End If
Call Shell(strAppName, vbMaximizedFocus)

With regards to the API, I would initialize your sExecvariable using the String function - not using the Space Function, and I would initialize sTemp with vbNullString. Finally, I would extract the exe pathname a little differently.


sExec = String(254, 32)
sTemp = vbNullString
' Then find the application associated with it.
lRetVal = FindExecutable(file, sTemp, sExec)
' If an application return the name
If lRetVal <= 32 Or IsEmpty(sExec) Then ' Error
Stop
Else
GetExe = Left(sExec, InStr(sExec, Chr$(0)) - 1)
End If
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[dirksm]

Sorry if my previous post seemed a bit out of place - asking a question you have allready answered! - but my internet connection has been going a bit screwy, so I posted the next question before I received you answer.

Sorry! (And by the time this is posted, I might have missed some replies again, so sorry again!)

I copied your code:

strAppName = App.Path
If (Right(strAppName, 1) <> &quot;\&quot Then
strAppName = strAppName & &quot;\Reports.mdb&quot;
Else
strAppName = strAppName & &quot;Reports.mdb&quot;
End If
Call Shell(strAppName, vbMaximizedFocus)

The only thing I changed was adding &quot;Dim strAppName as String&quot; before the copied code. But I get a run-time error '53', 'File not Found'. I KNOW the file exists, because I displayed the strAppName variable in the immediate window just before the Call Shell function. I then copied the variable's contents, pasted it into Window's Start/Run window, and it ran successfully! The only thing I noticed is that in the Start/Run window, I had to add quotes for the command to work, or else it would also cut of the command at the first space.

Maybe I defined my strAppName incorrectly?

Please help!

 

[CajunCenturion]

Apparently, the intrinsic Shell function has problems with long filename which contain spaces - therefore, I would suggest using the ShellExecute API - here is an example (note that appname is now only the directory

Public Declare Function ShellExecute Lib &quot;shell32.dll&quot; Alias &quot;ShellExecuteA&quot; (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 SW_SHOWNORMAL = 1

then from a form (use a form because we need the hWnd of the form)

strAppName = App.Path
If (Right(strAppName, 1) <> &quot;\&quot Then
strAppName = strAppName & &quot;\&quot;
End If
ShellExecute Me.hwnd, vbNullString, strAppName & &quot;Reports.mdb&quot;, vbNullString, strAppName, SW_SHOWNORMAL
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[dirksm]

Thanx CajunCenturion!!!!!!!!!

It finally works perfectly!!

You really stuck with me for a long time through this, thank you very, very much for all your time and effort. It is greatly appreciated.