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,

Is there anything that you can think of that I can put into a email body that would similar to Start, Run...

As for flagging records in the database that need to be looked at, I could add that. My only thought is that each record needs to be looked at and approved by several people and the flagging might get a little complicated tracking which people have approved / viewed which records. By the way this thread is going, this may be the best option.

If you want to give up on this problem, I will understand. Don't feel bad as it seems to be going nowhere. I am so close though.

Thanks,

David

 

[Norie]

David

I actually think the best way would be to flag the records in the database.

This also makes sense for monitoring purposes.

Also I don't think it would be too complicated, unless you made it so.

You can easily track who makes changes to records, and you can always include things like date/time stamps etc

 

[hollifd]

OK Norie.

I will consider that. If any other ideas come to you, please let me know.

Thanks again for all of your help and time.

David

 

[VicRauch]

One more suggestion.

If everyone has access to the same network, then you could create a folder to store what I'm about to suggest. Create a new Access database within this folder, name the file with a date/time stamp, prefixed by "temp" So, for right now, the name would be "temp20061010120715" It is Oct 10, 2006, 12:07:15PM. That name could be passed as your hyperlink with the path to that folder in front of the name of the file. If the path uses the \\server\folder protocal, then your users would be able to open that database in which you would have already stored the tool number. That database would then be able to, using the code Denis suggested "Application.SysCmd(acSysCmdAccessDir)" to build the batch file with the correct path to MSAccess for the calling PC, and the correct /cmd xxxxx for the tool number that the temp database was already given.
HTH,

 

[hollifd]

Vic,

You just gave me another idea, but to continue with your idea first...

Do you mean create a new "temp" database each time a new record is added to my database?...and then each new temp database will hold the tool number?

David

 

[VicRauch]

Yes.

You will have to be deligent in keeping these "temp" databases deleted. After the last e-mail recipient has looked at the database, or after 7 days, or 2 weeks, or whatever, be sure and delete the database. Otherwise, you will have a network admin type on you, as I'm sure you already know.

I would create a table in the database called tblToolNumber with 1 field, ToolNumber. Then you will always know exactly where to get the tool number the email recipient will be looking for.

 

[hollifd]

Vic,

Thats sounds like it will work. What do you think about this idea?

Whenever a user adds a new record to the database, I create a very small batch file on the server named to their tool number like: 30095.bat Inside the batch file, I write the commands to print the tool number to a text file in C:\Temp\OOSN.txt and also call the batch file that opens up the "real" database.

I add a link to this 30095.bat batch file in the email. When the user clicks on the email link. The link opens up the 30095.bat batch file and the batch file creates the OOSN.txt text file containing the tool number and then starts up the "real" database. When the "real" database opens it checks for this OOSN.txt text file. If the OOSN.txt text file is found, then the "real" database opens it and gets the tool number and then navigates to the correct record in the database.

AH-Ha!!!!! I think that will work and it will be totally automatic.

I can't wait to try it but it is time for me to go as I have family in town and they are waiting for me.

I'll post again tomorrow and let you all know if it works.

Let me know if you see any issues with this latest idea.

Thanks again to all that offered help.

David

 

[VicRauch]

David,

That sounds like a great idea! I like it better than mine in that .bat files will execute quicker than opening a second database from the server. Plus, I think creating the .bat files will be a lot easier than doing the table in Access. Good job!

Hope you had a great time with the family.

 

[hollifd]

To all,

The batch file idea works perfectly. It may not be the most professional way to add the functionality that I needed, but it works and should be flawless. Thanks to all for your ideas and help.

To summarize what I needed and what was done to solve the problem...

I needed a way to allow users of my database to enter records and then send out emails to people that need to know about these new records. When the recipients open their emails, I wanted a way for them to be able to click on a hyperlink that would open up the database and automatically navigate to the correct record that the email pertained to. I could not use any Outlook VBA. All VBA had to reside on the Access side.

Here is what I did to solve the problem (with help and ideas from others on this forum):

On my database form, I added a button that would create a email message and a batch file (on the network). The batch file contained the command to write the tool number to a text file on the user's C:\Temp folder. The command to do this in the batch file is:

ECHO 30095 > C:\Temp\OOSN.txt

Where 30095 is the tool number from the form where the create email button was pushed.

In the email, I created a hyperlink to the 30095.bat batch file on the network. When the email gets sent to recipients, they open it and click on the link. The link opens the batch file on the network. It creates the OOSN.txt text file on the user's C:\Temp folder and then opens my database. The database (when opened) looks for the OOSN.txt file on ther user's C:\Temp folder. If the OOSN.txt file is found, the database opens the text file and reads the tool number to search for from the text file and then the database searches for and displays the record that matches the tool number found in the text file.

This has provided the automation that I was looking for to help users automatically go to the correct records in my database.

It has been a challenging task and it feels good to finally have a solution. I hope that others may find a use for this functionality in their projects.

Thanks again for all of the help.

David

 

[mccer]

Try strPath = SysCmd(acSysCmdAccessDir)