How to start MS Access 2003 app from vb.net app

[moondaddy]

I have an MS Access 2003 application and now I want to be able to start it
up from a vb.net app. Additionally, the vb.net application will be
validating the user, checking for updated versions of the Access app and if
there are new versions, will prompt the user to download a zipped copy of
the new version via a web service. What I'm not sure about is how to
actually startup an instance of the Access app and once its open, close the
..net app. And the really tricky part, is that I need to pass in a few
variables from the .net app once the access app is open and running. These
variables will be the computer name, name of person logged into the
computer, and name of the person logged into the application (via the .net
app). I know it sounds overly complicated, but we have our reasons. so in
summary, here's what I would like to know:

1) how to start ms access app from vb.net app
2) how to pass in a few variable from the .net app to the Access app
3) close the .net app and leave the Access app running (this part might be a
no-brainer, but I haven't tried it yet).

Thanks.

 

[Brian]

I have an MS Access 2003 application and now I want to be able to start it
up from a vb.net app. Additionally, the vb.net application will be
validating the user, checking for updated versions of the Access app and if
there are new versions, will prompt the user to download a zipped copy of
the new version via a web service. What I'm not sure about is how to
actually startup an instance of the Access app and once its open, close the
.net app. And the really tricky part, is that I need to pass in a few
variables from the .net app once the access app is open and running. These
variables will be the computer name, name of person logged into the
computer, and name of the person logged into the application (via the .net
app). I know it sounds overly complicated, but we have our reasons. so in
summary, here's what I would like to know:

1) how to start ms access app from vb.net app
2) how to pass in a few variable from the .net app to the Access app
3) close the .net app and leave the Access app running (this part might be a
no-brainer, but I haven't tried it yet).

Thanks.

Use the Shell command in VS.Net to run Access. Specify Access command line
arguments as appropriate in the Shell command's "args" argument. Use the
/cmd argument in the Access command line to specify the values you want to
pass in. Use the Command function in Access to retrieve whatever was
specified on the command line. Have the .Net thingy close itself once it's
shelled Access.

 

[moondaddy]

Thanks!

--
(e-mail address removed)

Use the Shell command in VS.Net to run Access. Specify Access command
line
arguments as appropriate in the Shell command's "args" argument. Use the
/cmd argument in the Access command line to specify the values you want to
pass in. Use the Command function in Access to retrieve whatever was
specified on the command line. Have the .Net thingy close itself once
it's
shelled Access.

 

[moondaddy]

Thanks Brian, but I'm not having good luck with this. Can you trouble shoot
why this isnt working?

When I call the shell cmd, I get a "File Not Found"
error. here's my code:


Dim ClientPath As String = Directory.GetCurrentDirectory & "\" &
System.Configuration.ConfigurationSettings.AppSettings("ClientName")
If File.Exists(ClientPath) Then
'Code hits this line
Console.WriteLine("exists")
Else
Console.WriteLine("exists not")
End If
Dim ProcID As Integer
ProcID = Shell( ClientPath, AppWinStyle.NormalFocus)
'Also tried the line below
'ProcID = Shell("""" & ClientPath & """", AppWinStyle.NormalFocus)

Any ideas how I can get this running?

Thanks.

 

[Brendan Reynolds]

Does your 'ClientName' setting include the file extension?

 

[Guest]

Sorry to interject a question not related, but I'm running Office 2003. I
need to run Access from Office 2000. I just want access from 2000, but
everytime I install access 2000 it affects Office 2003 and I can't use
Outlook 2003. I can't open Access 2000 because it says I need mso9.dll - any
suggestions. Thanks

 

[moondaddy]

Yes it does. ClientPath is the full path, file name and file extention
(.mdb).

 

[Brendan Reynolds]

After some experimentation, the syntax that I got to work consists of the
path to MSACCESS.EXE, *without* surrounding quotes, followed by a space and
then the path to the mdb *with* surrounding quotes ...

Module Module1

Sub Main()

Dim ClientPath As String = "C:\Program Files\Microsoft
Office\OFFICE11\MSACCESS.EXE " & _
"""" & "C:\DSDATA\Northwind.mdb" & """"
'Note no quotes this time ...
If System.IO.File.Exists("C:\DSDATA\Northwind.mdb") Then
Console.WriteLine("exists")
Else
Console.WriteLine("exists not")
End If
System.Console.ReadLine()
Dim ProcID As Integer
ProcID = Shell(ClientPath, AppWinStyle.NormalFocus)
System.Console.ReadLine()

End Sub

End Module

 

[moondaddy]

Thanks. I found a similare code example in one of the .net user groups. My
task now is to pass in the name of a function (or macro if I really have to)
and its parameters.

Dim m_sAccessCmdLine As String
Dim sCrit As String = "myParam"
If File.Exists("c:\Program Files\Microsoft Office\Office11\msaccess.exe")
Then
m_sAccessCmdLine = "C:\Program Files\Microsoft
Office\OFFICE11\MSACCESS.EXE
D:\nwis\Apps\LandMan\VS\TransAct\TransAct\bin\TransAct_Stallion_Remote.mdb
/x Main(""" & sCrit & """)"
Shell(m_sAccessCmdLine, , True)
End If

So its opening Access OK, but it cant find the public function Main. I get
an message box in Access saying it cant find the Macro Main. I also created
a Macro called StartMain and used that in the command line as well and got
the same error. Can you tell me how to call a public module level function
from this command line?

Thanks!

 

[Brendan Reynolds]

I believe that what follows /x on the command line has to be the name of a
macro rather than a function. I can get a macro named Main to run using the
following syntax ...

Dim ClientPath As String = "C:\Program Files\Microsoft
Office\OFFICE11\MSACCESS.EXE " & _
"""" & "C:\DSDATA\Northwind.mdb" & """" & " /x Main"

Note that /x Main is outside the doubled quotes.

So we can run a maco, and you can use the RunCode action in a macro to
execute a VBA procedure from the macro. However, I'm not aware of any method
to pass a parameter to the macro. (I don't make much use of macros, so if
anyone else knows of a way, I hope they won't hesitate to say so.)

While I have never used it, while checking the help file for the syntax for
command line options, I noticed the /cmd option. So I tried modifying the
VB.NET code as follows ...

Dim ClientPath As String = "C:\Program Files\Microsoft
Office\OFFICE11\MSACCESS.EXE " & _
"""" & "C:\DSDATA\Northwind.mdb" & """" & " /cmd 42"

.... then using RunCode in an AutoExec macro in the Access MDB to call the
following function ...

Public Function MyFunction()

If Command$() = "42" Then
MsgBox "42"
Else
MsgBox "Not 42"
End If

End Function

In my tests, using Access 2003, this works. If the .NET code passes the
value "42", the message box displays the text "42". If the .NET code passes
any other value, the message box displays the text "Not 42".

However, according to the help file, it shouldn't work! The help file says
that the Visual Basic Command$() function is not available in Office
applications. I'm not sure, therefore, wether it is safe to recommend the
use of this function.

I'll ask around, and see if I can find any further information on whether
this function is supported in Access or not - or perhaps someone else
reading this may be able to comment on that?

 

[moondaddy]

Well now we know why you are a "MVP"! Thanks for all the info and effort in
making a good response. Actually, I wanted to call a function and not a
macro (I haven called a macro for more than 12 years and don't want to start
now), but that seemed to be my only option at the time. I'll work with what
you provided below and see if I can find documentation on any other switches
for the shell command. Thanks for all the help!

 

[Brian]

I believe that what follows /x on the command line has to be the name of a
macro rather than a function. I can get a macro named Main to run using the
following syntax ...

Dim ClientPath As String = "C:\Program Files\Microsoft
Office\OFFICE11\MSACCESS.EXE " & _
"""" & "C:\DSDATA\Northwind.mdb" & """" & " /x Main"

Note that /x Main is outside the doubled quotes.

So we can run a maco, and you can use the RunCode action in a macro to
execute a VBA procedure from the macro. However, I'm not aware of any method
to pass a parameter to the macro. (I don't make much use of macros, so if
anyone else knows of a way, I hope they won't hesitate to say so.)

While I have never used it, while checking the help file for the syntax for
command line options, I noticed the /cmd option. So I tried modifying the
VB.NET code as follows ...

Dim ClientPath As String = "C:\Program Files\Microsoft
Office\OFFICE11\MSACCESS.EXE " & _
"""" & "C:\DSDATA\Northwind.mdb" & """" & " /cmd 42"

... then using RunCode in an AutoExec macro in the Access MDB to call the
following function ...

Public Function MyFunction()

If Command$() = "42" Then
MsgBox "42"
Else
MsgBox "Not 42"
End If

End Function

In my tests, using Access 2003, this works. If the .NET code passes the
value "42", the message box displays the text "42". If the .NET code passes
any other value, the message box displays the text "Not 42".

However, according to the help file, it shouldn't work! The help file says
that the Visual Basic Command$() function is not available in Office
applications. I'm not sure, therefore, wether it is safe to recommend the
use of this function.

I'll ask around, and see if I can find any further information on whether
this function is supported in Access or not - or perhaps someone else
reading this may be able to comment on that?

Hi Brendan,

The Command() function is supported in Access, I've used it many times, it's
certainly described in A2002 help.

Brian

 

[Brendan Reynolds]

Thanks for that Brian. This is just my opinion based on a few cursory tests,
so I could be wrong, but the way it looks to me so far is that the help
topic is just poorly phrased. As far as I can see, Command/Command$ is
always available in VBA, but most Office applications (I checked Word and
Excel help, I haven't checked PowerPoint) don't support the /cmd start-up
switch, so when using these applications, while the Command function is
available, there's nothing for it to return. I expect (though I have not
tested this) that Command$() will always return an empty string in Office
applications other than Access. I have not tested whether Command() (without
the '$') returns an empty string or a Null value.

 

[Brian]

Thanks for that Brian. This is just my opinion based on a few cursory tests,
so I could be wrong, but the way it looks to me so far is that the help
topic is just poorly phrased. As far as I can see, Command/Command$ is
always available in VBA, but most Office applications (I checked Word and
Excel help, I haven't checked PowerPoint) don't support the /cmd start-up
switch, so when using these applications, while the Command function is
available, there's nothing for it to return. I expect (though I have not
tested this) that Command$() will always return an empty string in Office
applications other than Access. I have not tested whether Command() (without
the '$') returns an empty string or a Null value.

Hi again Brendan,

I've never tried to use it outside of Access, I daresay you are right.

I use it to do exactly what the original poster wanted, i.e. to create VB
"launcher" programs which set up an Access front end environment (copy in
the latest front end, set up various other files and so on) and then launch
the Access app. Unfortunately for him, I do it with VB6, not dotnet, which
is why I didn't just post my code!

Brian