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

 

[Norie]

David

Did you ever consider the idea of having code run when the database opens?

It could look for the file and goto the record.

It might even be possible to look for the file in Outlook.

 

[hollifd]

Norie,

Yes. In fact, that is how it is currently working. The email that gets generated from Access contains a text file attachment. The text file attachment contains the tool number. When the database opens, it looks for this text file in the user's C:\Temp folder. If the text file is found, then the database opens the text file and gets the tool number and then finds the matching record in the database. This method works good except that I wanted to provide the users a more automatic way without having to save the text file to their c:\Temp folder.

You mentioned looking for the file in Outlook. What do you mean by that?

As a continuation of my original idea...Do you know how to add a hyperlink to a email message so that the link will recognize a command line argument? I wrote a very quick Visual Basic .exe file to get the text that is typed after the .exe filename.

Example: Project1.exe 30095
When I run this from the Windows Start, Run
My program runs and displays 30095.

When I add a hyperlink to a email message and then click the link, I get a message that says "Cannot open the specified file".

Thanks again for your interest and trying to solve the problem.

David

 

[Norie]

David

I don't know how to pass an argument to Access via a hyperlink.

By the way, why do you think you need the path to msaccess.exe for a hyperlink?

If you have a hyperlink to a file then it should automatically be opened by the associated application.

As to the Outlook thing, what I mean is that there is code that you could use to look through all the emails and attachments.

Now I'm a single user and I can do that quite easily, but since you're on a network I'm not sure how it would be done exactly.

Here's some sample code.

Sub SaveAttachments()
    Dim myOlapp As Outlook.Application
    Dim myNameSpace As Outlook.NameSpace
    Dim myFolder As Outlook.MAPIFolder
    Dim myItem As Outlook.MailItem
    Dim myAttachment As Outlook.Attachment
    Dim I As Long
    
    Set myOlapp = CreateObject("Outlook.Application")
    Set myNameSpace = myOlapp.GetNamespace("MAPI")
    Set myFolder = myNameSpace.GetDefaultFolder(olFolderInbox)
    Set myFolder = myFolder.Folders("Job Stuff")

    For Each myItem In myFolder.Items
        If myItem.Attachments.Count <> 0 Then
            For Each myAttachment In myItem.Attachments
                I = I + 1
                myAttachment.SaveAsFile "C:\MailTest\Attachment" & I & ".csv"
            Next
        End If
         
    Next
End Sub

Note this code is in Outlook VBA but could just as well be done from Access.

 

[giacomo]

OK, I scanned through all the previous posts, and I have a couple ideas. My apologies if these suggestions are repeats, my eyes tend to glaze over at 3 pages of posts (actually sometimes they glaze over at 1). Note both of my suggestions will require that your users have to log into your db.

Problem: I don’t see how this will ever work the way that you’re intending because person A is sending an email to Person B and since the link is being created in Person A’s computer how will it know the msaccess.exe path on Person B’s computer?

Solution #1: When Person B log’s into your database store the location of msaccess.exe on their computer in a user table. Then when it’s time for Person A to send an email to Person B you can retrieve the path and create the link

Solution #2: Don’t bother sending the link. Instead send a generic email letting the user know that there are updates that they need to be aware of. When the user logs in present them with a form that details the updates that that user needs to know about.

Hth,
Giacomo

 

[hollifd]

Norie,

The reason I think I need the path to msaccess.exe is because in order to use the command line arguments for opening access, I have only been successful when I call out the full path for opening access and my database.
For example: Create a database on your c:\Temp folder called Junk.mdb
Next, Click Start, Run and type in C:\Temp\Junk.mdb /cmd "Hello World"
When your database opens, go to Tools, Options, Advanced tab and look in the Command Line Arguments field. It should be blank.

Now, Click Start, Run and type in "C:\Program Files\Microsoft Office\Office11\msaccess.exe" C:\Temp\Junk.mdb /cmd "Hello World"
When your database opens, go to Tools, Options, Advanced tab and look in the Command Line Arguments field. It should contain "Hello World"

This is why I think I need the full path to msaccess.exe

As for going through all of the user's email and finding the attachment, I think this may not work because users could have more than one email pertaining to tools and the code will not know which email is the correct one.

Still looking for a solution. If I could hyperlink to my C:\Temp\Project1.exe 30095 file then I would be on my way.

Any other ideas / comments?

David

 

[VicRauch]

Does the person opening the email need to do some updates within Access? Or is this just an approval type of thing? I worked on an application about 8 years ago that needed management approval so the Access database would send e-mails to the managers, then the e-mails would allow the manager to select approval, or disapprove and return the e-mail. The person in charge of the database would then receive these e-mails, press the correct button within Access that would read any e-mail returned from these managers and the database would be updated based on what was returned from the managers. So, it really depends on how much needs to be done by the receiving person.
HTH,

 

[hollifd]

Giacomo,

Thanks for the offering. You are correct and I have realized the difficulty in trying to dertermine the path to msaccess.exe for other user's computers. I will have to think about your ideas to see if they might work.

Thanks for your input,

David

 

[hollifd]

Vic,

This is mostly an approval type of system but sometimes the users will need to make changes and add additional information to the records in the database.

David

 

[Norie]

David

A hyperlink is not the same as typing in a command using Run...

As to the code not being able to identify the attachment there are probably ways round that.

 

[Norie]

David

Another idea, why not just have a flag in Access that indicates which records have to be looked at?