How can I know the exact location of msaccess.exe?

[hollifd]

If I wanted to open access on a user's machine, how can I know the exact path to the MSAccess.exe file.

I want to open access from a batch file like:

C:\Program Files\Microsoft Office\Office11\MSAccess.exe

but the path may be different for some users.

What VBA code can I use in order to always know the correct path?

Thanks,

David

 

[hollifd]

Norie,

Have I finally stumped you?

Concerning your questions...
There will be many records in the database and I am trying to provide an easy way for users to go to the record that needs their attention. The database will be stored on a server and will need to be updated by many different users. All users will be using Outlook.

I can tell the user in the email which tool number to look up but I am trying to come up with a more automatic way. Actually, by using the command line arguments, I can already accomplish my task but I realized that some users could have a different version of Access or have Access loaded in a different folder than what my VBA code expects. My solution with the command line arguments is very impressive if I could only figure out a good way to always know the path to msaccess.exe

If I had some code to look up the full path to msaccess.exe on any computer, I think I could call this problem solved.

Thanks again for your help,

David

 

[Norie]

David

If you already have code that uses the command line method I don't quite see why you can't use similar code that runs when the database is opened.

By the way it's still unclear as to where you are running the code.

Is it from Access/Outlook?

Is it a batch file, as you mentioned in the 1st post?

 

[hollifd]

Norie,

I am sorry for any confusion. It has been difficult explaning my problem so that you can fully understand it.

All of the code that I have mentioned to this point is running from Access. Any batch files that I have talked about are created by Access VBA. Any link that I may create in the email messages are created in Access and written to the email message using VBA. I am not familiar with Outlook VBA.

I am so close to solving this problem. I only need a way to search for and know the full path to msaccess.exe.

Thanks,

David

 

[Norie]

David

I really think you need to forget about the idea that you need the path for msaccess.exe.

But then again this sort of thing is really hard to help with since I don't know the exact set up you have, so I might be wrong.

You've mentioned servers which indicates you're on a network.

I've no recent experience of this sort of thing in a network environment. ie automating Outlook, which I think might be what you want to do.

 

[VicRauch]

David,
I just read through this thread as it looked interesting, and it is interesting. The question I have, after reading this:

The way I am currently accomplishing the above is...
I attach a text file to the email. The text file contains the record information. I ask the user in the email message to save the text file to C:\Temp and then click on the link to open the database. When the database opens, it looks for this text file.

Where is this "link" you have refered to here, and what is in this link? You say it opens Access, then reads the text file that the user had to save. So, what is this "link?"
Thanks,

 

[Norie]

Vic

Good question.

Whenever I think of links I think of hyperlinks.

Or I used to think of sausages, but I'm a vegetarian now so that's a no go.

 

[hollifd]

Guys,

Thanks for taking an interest in my problem. Please be patient with my ideas as I am not a professional developer. I am a Manufacturing Engineer that gets involved with application development when our IT guys either Can't, Don't or Won't complete our projects when we need them to.

It is Tuesday morning 7:30 am and I am back in the office.

You had asked about the "link". Here is my code that creates the email from Access VBA and creates the "link":

Private Sub cmdCreateEmail_Click()

Open "C:\Temp\OOSN.txt" For Output As #1
Print #1, Forms!frmMain.ToolNumber
Close #1

Let MyDBShortcut$ = "\\dbd03\nccode\Router_Proc\OOSN.bat"

'Format and display email below...
Set ol = CreateObject("Outlook.application")
Set NewMessage = ol.CreateItem(olMailItem)

With NewMessage
    'Read in email addresses below...
    Dim dbsMYDB As Database
    Dim rstMYDIRList As DAO.Recordset
    
    Set dbsMYDB = CurrentDb
    Set rstMYDIRList = dbsMYDB.OpenRecordset("tblEmailList")
    With rstMYDIRList
        .MoveFirst
        While Not rstMYDIRList.EOF
            With NewMessage
                .Recipients.Add rstMYDIRList!EmailAddress
            End With
            .MoveNext
        Wend
    End With
    DoCmd.Hourglass 0
    rstMYDIRList.Close
    dbsMYDB.Close
    'Read in email addresses above...
    .Attachments.Add "C:\Temp\OOSN.txt"
    .Subject = "Urgent Out-of-Service Notice!  There is a tool that needs to be taken Out-of-Service..."
    'Insert body and Hyperlink below...
    .Body = "Please save the attachment(s) to C:\Temp and then click on the link below." & vbCrLf & vbCrLf & _
        "<file:" & MyDBShortcut$ & ">" & vbCrLf & vbCrLf & _
        "Thank you."
    .Importance = 2 'This is a High Priority
    .Display
End With

End Sub

This code is run from a command button on my Access form. When a user of the database enters data into my form, he will click this button and format an email that gets sent to several recipients for their review.

My ultimate goal is to provide a link to a piece of software (database, VB or something else) on the network that will determine the user's path to msaccess.exe and then create a batch file that opens the real database and automatically navigates to the correct record.

Here is another attempt at describing the entire process so that you may understand...

A user enters a record into my database. Let's say it is record 500 out of 1000 records. The tool number that this user is entering information about is tool number "1234". After entering some general data about this tool, the user presses the "Create Email" button. The code about creates a email message and the user presses the send button in Outlook.

Next, one of the recipients recieves the email. When he opens the email, there is a link inside. When he clicks on the link, the link will open up a piece of software on the network. The only purpose in this software that opens up is to get the path to the user's msaccess.exe and then create a batch file that will open up my database on the network using the full path like:

"C:\Program Files\Microsoft Office\Office11\Msaccess.exe" "C:\Temp\TestDB.mdb" /cmd "Hello World"

After creating the batch file, the software will call and execute the batch file which will ultimately open my database and automatically navigate to the correct record in the database.

When my database opens it will use this code to navigate to the correct record...

'This code looks for a text file that contains the part number to search
'for when the form opens up.  If the text file cannot be found, then the
'form opens up and displays the first record.

Dim rs As Object
If Len(Dir("C:\Temp\OOSN.txt")) > 0 Then
    Open "C:\Temp\OOSN.txt" For Input As #1
    While Not EOF(1)
        Line Input #1, Search$
    Wend
    Close #1
    Kill "C:\Temp\OOSN.txt"
    If Search$ <> "" Then
        'Now find the record that matches Search$
        'Dim rs As Object
        Set rs = Me.Recordset.Clone
        rs.FindFirst "[ToolNumber] = '" & Search$ & "'"
        If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End If
End If

'This code gets any command line arguments that may exist
Let Search$ = Command
If Search$ <> "" Then
    'Now find the record that matches Search$
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ToolNumber] = '" & Search$ & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End If

By using the full path to Access, I have figured out that I can use command line parameters to gain access to data. The data will be used for navigating to the correct record in the database. In the example above, the data is "Hello World". If "Hello World" was a tool number, then I could just check the command line parameter to see if it is <> "", then navigate to the record named "Hello World".

Because there could be users that have a different version of Access or have Access stored in some other directory structure, I need a way to format a dynamic batch file that will always know the correct path to msaccess.exe from any user's computer.

If I can get this to work (and I will today), I can get rid of the need to have the user save the text file attachment in the email to their C:\Temp folder and start using the more automated approach using command line parameters. If you need to know more about command line arguments, look up "command" in VBA help.

I am tired of typing now. Does this help explain what I want to do? And if it does, are there any ideas how could accomplish this any easier.

Thanks,

David

 

[SydneyGeek]

Not sure if this will help, but if you are in Access, this will give the application path:
Application.SysCmd(acSysCmdAccessDir)

This will give the path to the active DB:
CurrentProject.Path

Denis

 

[hollifd]

SydneyGeek,

Thanks for the help. That may do the trick and is pretty simple. I will set my hyperlink in the email to open a batch file on the server that will copy a database down to the local user's computer and then open the database. When the database opens, it will use your code get the path to Access and then create a batch file to open up the "real" database. This should work on any computer that any user at my company may have.

Thanks for your code snipet. I will post again after I get the entire system working.

Thanks,

David

 

[hollifd]

It seems that I have overlooked a few things with this project. The hyperlink that I was planning to include in the automated email will not recognize the command line arguments. For example, I wanted to add a hyperlink to a file like: C:\Temp\MyDatabase.mdb /cmd "30095". The 30095 is the information that I need in order to open up my "real" database so that I can automatically navigate to the 30095 record.

When I add this hyperlink to the email, the /cmd gets changed to \cmd and when I click on the hyperlink, the file will not open. Even if the file would open, I have remembered that in order to use the command line arguments, it seems that it is necessary to call out the full path to msaccess.exe. Since some users may have different versions of Access, I am out of luck.

I am going to give up for now and use my original idea and require the users to save a text file attachment from their email message to their C:\Temp folder and then open up the database. If the users do not want go through that trouble, then they can open up the database and search for the record manually using a search dropdown combobox that I have added.

Thanks to all that tried to help. If I dream up some way to add this automatic functionality to my database at some point in the future, I will post back again and share my solution.

Let me know if anyone has any ideas for me to try.

Just for summary...
I need a way to allow users of my database to go to a record in the database and click a button to create a email message and send it to other people on our network. When the recipients open their email and click on a link (or some other method), the link should open my database and automatically navigate to the correct record that the email was generated for. All VBA code must be in Access VBA and cannot be in Outlook as I am not familiar with Outlook VBA and not sure if it is even allowed at my company.

David