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

What exactly are you doing?

You mention a batch file, then you mention VBA.

 

[hollifd]

Norie,

I was hoping you would see my post.

Ultimately, I want to use VBA to create a email message that has a link to a database in it. I will format the link to open Access and then the database on a server. When the email is sent out to users, they can click on the link and the link will open Access and automatically search and display the correct record.

I have discovered that you can include command line arguments with Access if you open it from a command line. For example:

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

Will open Access and store "Hello World" as a command line argument. So my VBA code will use this command line argument data to search for and display the correct record when the database opens. But one problem that I may run into is that some users may not have Office11, they may have a different version of Access or it may be stored somewhere other than where my link specifies.

Does any of this make sense?

Thanks,

David

 

[Norie]

David

Why not use CreateObject to create an instance of Access?

You could then use subsequent code to do what you want, without having to use command line arguments.

 

[hollifd]

Norie,

I am not sure I understand you suggestion. Let me try to explain again what I need to do.

I have a database that users will enter a tool number when they want to remove a tool from production. When this happens, there are other people that need to know so that they can plan extra inventory to cover the amount of time that the tool is going to be out of production. My plan was to have the database create a email message that would contain a link. The email would then get sent out to several people that need to know about tools that are taken out of production. When these people get their emails, I wanted them to be able to click on the link which would open Access and automatically find the record in the database that matches the email.

Do you have any ideas on how I could accomplish the above in the simplest way?

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. If the text file is found, it opens it up and gets the record information and then does a search on the database to find the record and display it. I want to avoid requiring the user to save this text file containing the record information.

Thanks again for listening and your suggestions,

David

 

[mdmilner]

Something like this from within Access would give you the full path to the database. I don't think this answers your question, but it might get you closer.

Sub testpath()

Debug.Print Application.CurrentDb.Name

End Sub

 

[hollifd]

mdmilner,

Thanks. That is very close but I think I need the path to Access rather than the path to the current database. I will keep looking. There must be some easy way to accomplish my task.

Thanks,

David

 

[Norie]

David

It's still not 100% sure where you want to do all this.

You've mentioned VBA, Access, email, text files, batch files etc.

It's all getting a little confusing.

Plus I don't see how you'll be able to utilize the text file without saving it somehow.

I realise you probably don't want to rely on the user there but there are ways via code to do that sort of thing. ie save an attachment

 

[hollifd]

Norie,

You have been very patient. I know you will have a way to solve my problem. You always have a solution. I'll try again...

I need a way to send an email from Access using VBA. (I know how to do this) The email will tell the recipient about a problem with a tool.

When the recipeints of the email open their emails, I need a way for them to click on a link (or some other method) that will open up the database on the server and the database will automatically go to the corrrect record that pertains to their email. The best idea I could come up with involves using the startup command line parameters with Access. This way I can tell Access to startup and I can use the startup parameters for knowing which record to display on the form.

Any ideas on how I can easily implement this fuctionality?

David

 

[Norie]

David

I'm going to have to have a think about this.

I think I can see what you want to do, but how to implement it is a different matter.

There are various issues/questions I can think of.

Where is the database located?

Is there anything that could be used to identify the email with the attachment with the required information?

What email client are you using?

As to a solution one idea might be to have code that runs when the database opens, checks for the attachment/email and using the data goes to that record.

Another idea might be to take a different approach.

Why not send more data by email, allowing the user to check it?

They could then send another email with information on what's to happen.

Any solution is probably dependent on why you want the user to goto the particular record in the database.

Is it just for review/information or will they be altering/updating/adding to the record?