Dynamic Target Path of MS Access Front End file (1 Viewer)

Alhakeem1977

Registered User.
Local time
Today, 04:42
Joined
Jun 24, 2017
Messages
308
Hi,

How can I make the Desktop shortcut for Front End file target folder as a dynamic path?
Like there are some users they don't have a 'D' Drive they have 'F' instead.

The below VBA code specifying the D Drive is the target folder how can I make it dynamic?


Code:
FileName = "MyShortcut"
Set oShell = CreateObject("WScript.Shell")
Set Shortcut = oShell.CreateShortcut(oShell.SpecialFolders("Desktop") & "\DMS_Doc. Register.lnk")
Shortcut.Description = "My shortcut"
[COLOR="Red"]Shortcut.TargetPath = "D:\Document Dispatch Register.accdb"[/COLOR]
Shortcut.WindowStyle = 1
Shortcut.Hotkey = ""
Shortcut.IconLocation = "A:\Databases\ProgramFile\Doc.ico, 0"
Shortcut.Save

Thanks in advance!
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:42
Joined
Oct 29, 2018
Messages
21,358
Hi. You could try to put the path in an environment variable and then use the variable in the shortcut.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:42
Joined
May 7, 2009
Messages
19,169
if you only want the drive letter, aside from drive C:
Code:
Function getNextHDD() As String
    Dim d As Object
    Dim arrDrives() As String
    Dim i As Integer
    With CreateObject("Scripting.FileSystemObject")
        For Each d In .Drives
            i = i + 1
            ReDim Preserve arrDrives(0 To i)
            arrDrives(i) = d
            'Debug.Print d
        Next
    End With
    getNextHDD = arrDrives(2)
    Erase arrDrives
End Function
so your code will changed to:
Code:
…
…
Shortcut.TargetPath = getNextHDD() & "\Document Dispatch Register.accdb"
 

Alhakeem1977

Registered User.
Local time
Today, 04:42
Joined
Jun 24, 2017
Messages
308
if you only want the drive letter, aside from drive C:
Code:
Function getNextHDD() As String
    Dim d As Object
    Dim arrDrives() As String
    Dim i As Integer
    With CreateObject("Scripting.FileSystemObject")
        For Each d In .Drives
            i = i + 1
            ReDim Preserve arrDrives(0 To i)
            arrDrives(i) = d
            'Debug.Print d
        Next
    End With
    getNextHDD = arrDrives(2)
    Erase arrDrives
End Function
so your code will changed to:
Code:
…
…
Shortcut.TargetPath = getNextHDD() & "\Document Dispatch Register.accdb"

Thank you arnelgp for your earliest response.

You mean now I can place the front end file in any drive? like F or S and it will create a shortcut on the desktop automatically?
 

Micron

AWF VIP
Local time
Yesterday, 21:42
Joined
Oct 20, 2018
Messages
3,476
I would just try using a UNC path if all who have different drive letters are mapped to the same server - like
\\serverName\folderName\folderName\...
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:42
Joined
May 7, 2009
Messages
19,169
not exactly, it only gives the next available hdd letter.
if you are installing the app on the Root folder of any hard/sd drive, other than c:
and only one copy can be found on one drive:
Code:
    FileName = "MyShortcut"
    Set oShell = CreateObject("WScript.Shell")
    Set Shortcut = oShell.CreateShortcut(oShell.SpecialFolders("Desktop") & "\DMS_Doc. Register.lnk")
    Shortcut.Description = "My shortcut"
    Shortcut.TargetPath = getPath("Document Dispatch Register.accdb")
    Shortcut.WindowStyle = 1
    Shortcut.Hotkey = ""
    Shortcut.IconLocation = "A:\Databases\ProgramFile\Doc.ico, 0"
    Shortcut.Save

Code:
Function getPath(strDBName As String) As String
    Dim d As Object
    Dim i As Integer
    Dim strFile As String
    On Error Resume Next
    With CreateObject("Scripting.FileSystemObject")
        For Each d In .Drives
            i = i + 1
            If i > 1 Then
                strFile = Dir(d.Path & "\" & strDBName)
                If strFile <> "" Then
                   getPath = d.Path & "\" & FileName
                    Exit For
                End If
            End If
        Next
    End With
End Function
 

Alhakeem1977

Registered User.
Local time
Today, 04:42
Joined
Jun 24, 2017
Messages
308
not exactly, it only gives the next available hdd letter.
if you are installing the app on the Root folder of any hard/sd drive, other than c:
and only one copy can be found on one drive:

Thank you so much arnelgp for your help I owe you a lot. :)
 

Alhakeem1977

Registered User.
Local time
Today, 04:42
Joined
Jun 24, 2017
Messages
308
not exactly, it only gives the next available hdd letter.
if you are installing the app on the Root folder of any hard/sd drive, other than c:
and only one copy can be found on one drive:
Code:
    FileName = "MyShortcut"
    Set oShell = CreateObject("WScript.Shell")
    Set Shortcut = oShell.CreateShortcut(oShell.SpecialFolders("Desktop") & "\DMS_Doc. Register.lnk")
    Shortcut.Description = "My shortcut"
    Shortcut.TargetPath = getPath("Document Dispatch Register.accdb")
    Shortcut.WindowStyle = 1
    Shortcut.Hotkey = ""
    Shortcut.IconLocation = "A:\Databases\ProgramFile\Doc.ico, 0"
    Shortcut.Save

Code:
Function getPath(strDBName As String) As String
    Dim d As Object
    Dim i As Integer
    Dim strFile As String
    On Error Resume Next
    With CreateObject("Scripting.FileSystemObject")
        For Each d In .Drives
            i = i + 1
            If i > 1 Then
                strFile = Dir(d.Path & "\" & strDBName)
                If strFile <> "" Then
                   getPath = d.Path & "\" & FileName
                    Exit For
                End If
            End If
        Next
    End With
End Function

Hi arnelgp,

I have tried the code you provided to me firstly: I got an error in the Function in this line: [ getPath = d.Path & "" & FileName ] then I have added in the declaration [ Dim FileName as String ] I do not know if it's correct?

Secondly, it opens only the "D" Drive not the target front end file even if move it to another Drive like A or F still the same.

Thanks a lot in advance!
 

vba_php

Forum Troll
Local time
Yesterday, 20:42
Joined
Oct 6, 2019
Messages
2,884
al,

I think he meant to say:
Code:
getPath = d.Path & "\" & strDBname
instead of:
Code:
getPath = d.Path & "\" & FileName
FileName is not a dec'd variable. I'm not sure if it is even automatically included as a property or attribute of the FSO. strDBname needs to be just the name of the accdb file plus the file extension. If it is in any directory *on* the drive, then that dir string also has to preceed the file name and extension. That's what I can see anyway.
 

Alhakeem1977

Registered User.
Local time
Today, 04:42
Joined
Jun 24, 2017
Messages
308
al,

I think he meant to say:
Code:
getPath = d.Path & "\" & strDBname
instead of:
Code:
getPath = d.Path & "\" & FileName
FileName is not a dec'd variable. I'm not sure if it is even automatically included as a property or attribute of the FSO. strDBname needs to be just the name of the accdb file plus the file extension. If it is in any directory *on* the drive, then that dir string also has to preceed the file name and extension. That's what I can see anyway.

Thanks for your prompt response, I modified the Function as you suggest but unfortunately, it creates the short cut in the desktop but even not opening the front end file.
 

vba_php

Forum Troll
Local time
Yesterday, 20:42
Joined
Oct 6, 2019
Messages
2,884
Thanks for your prompt response, I modified the Function as you suggest but unfortunately, it creates the short cut in the desktop but even not opening the front end file.
sorry about that. I don't know about the rest of the guys here, but I would have to see what you've got in order to help you out.
 

Alhakeem1977

Registered User.
Local time
Today, 04:42
Joined
Jun 24, 2017
Messages
308
sorry about that. I don't know about the rest of the guys here, but I would have to see what you've got in order to help you out.
Thank you so much.

Sent from my HUAWEI NXT-L29 using Tapatalk
 

vba_php

Forum Troll
Local time
Yesterday, 20:42
Joined
Oct 6, 2019
Messages
2,884
Thank you so much.

Sent from my HUAWEI NXT-L29 using Tapatalk
if you don't have a solution Alhakeem, I bet we could replicate the problem over here, and we might not even need a sample file from you to do it. but it might help. would you be willing to do that?
 

Alhakeem1977

Registered User.
Local time
Today, 04:42
Joined
Jun 24, 2017
Messages
308
if you don't have a solution Alhakeem, I bet we could replicate the problem over here, and we might not even need a sample file from you to do it. but it might help. would you be willing to do that?
Sure why not!
How can I make the Desktop shortcut to open the Front End file as a dynamic path?

Like there are some users they don't have a 'D' Drive they have 'F' instead.

The below VBA code specifying the D Drive is the target folder to open the located front end file, how can I make it dynamic?

Code:
 FileName = "MyShortcut"

Set oShell = CreateObject("WScript.Shell")

Set Shortcut = oShell.CreateShortcut(oShell.SpecialFolders("Desktop") & "\DMS_Doc. Register.lnk")

Shortcut.Description = "My shortcut"

Shortcut.TargetPath = "D:\Document Dispatch Register.accdb"

Shortcut.WindowStyle = 1

Shortcut.Hotkey = ""

Shortcut.IconLocation = "A:\Databases\ProgramFile\Doc.ico, 0"

Shortcut.Save

Sent from my HUAWEI NXT-L29 using Tapatalk
 

vba_php

Forum Troll
Local time
Yesterday, 20:42
Joined
Oct 6, 2019
Messages
2,884
does this work?
Code:
Function set_target_path()

    FileName = "MyShortcut"
    Set oShell = CreateObject("WScript.Shell")
     Set shortcut = oShell.CreateShortcut(oShell.SpecialFolders("Desktop") & "\DMS_Doc. Register.lnk") ' I don't think you can have a "." in the file name before ".lnk"

     shortcut.Description = "My shortcut"
    
        'this is added from previous code already given to you
        shortcut.targetpath = getPath("Document Dispatch Register.accdb")
    
    shortcut.WindowStyle = 1
    shortcut.Hotkey = ""
    shortcut.IconLocation = "A:\Databases\ProgramFile\Doc.ico, 0" 'I'm not sure what you're doing here.
    shortcut.Save

End Function

Function getPath(strDBName As String) As String
    Dim d As Object
    Dim i As Integer
    Dim strFile As String
    On Error Resume Next
    With CreateObject("Scripting.FileSystemObject")
        For Each d In .Drives
            i = i + 1
            If i > 1 Then
                strFile = Dir(d.Path & "\" & strDBName)
                If strFile <> "" Then
                   getPath = d.Path & "\" & strDBName
                   Exit For
                End If
            End If
        Next
    End With
End Function
 

Alhakeem1977

Registered User.
Local time
Today, 04:42
Joined
Jun 24, 2017
Messages
308
does this work?
Code:
Function set_target_path()

    FileName = "MyShortcut"
    Set oShell = CreateObject("WScript.Shell")
     Set shortcut = oShell.CreateShortcut(oShell.SpecialFolders("Desktop") & "\DMS_Doc. Register.lnk") ' I don't think you can have a "." in the file name before ".lnk"

     shortcut.Description = "My shortcut"
    
        'this is added from previous code already given to you
        shortcut.targetpath = getPath("Document Dispatch Register.accdb")
    
    shortcut.WindowStyle = 1
    shortcut.Hotkey = ""
    shortcut.IconLocation = "A:\Databases\ProgramFile\Doc.ico, 0" 'I'm not sure what you're doing here.
    shortcut.Save

End Function

Function getPath(strDBName As String) As String
    Dim d As Object
    Dim i As Integer
    Dim strFile As String
    On Error Resume Next
    With CreateObject("Scripting.FileSystemObject")
        For Each d In .Drives
            i = i + 1
            If i > 1 Then
                strFile = Dir(d.Path & "\" & strDBName)
                If strFile <> "" Then
                   getPath = d.Path & "\" & strDBName
                   Exit For
                End If
            End If
        Next
    End With
End Function
It's working but to create the shortcut in the first time I must open the front end file in the drive D then I can use that shortcut to open the file from any drive.

Sent from my HUAWEI NXT-L29 using Tapatalk
 

vba_php

Forum Troll
Local time
Yesterday, 20:42
Joined
Oct 6, 2019
Messages
2,884
It's working but to create the shortcut in the first time I must open the front end file in the drive D then I can use that shortcut to open the file from any drive.
I'm not sure I understand what you mean here bud. are you needing further assistance? are you saying you're running what I gave you from an access file and from VBA inside that file, and you don't wanna do that but want to run it from another medium like vbscript so you don't have to open an access file to do it the first time? and will someone please tell me what TapaTalk is!?
 

Alhakeem1977

Registered User.
Local time
Today, 04:42
Joined
Jun 24, 2017
Messages
308
I'm not sure I understand what you mean here bud. are you needing further assistance? are you saying you're running what I gave you from an access file and from VBA inside that file, and you don't wanna do that but want to run it from another medium like vbscript so you don't have to open an access file to do it the first time? and will someone please tell me what TapaTalk is!?
I am sorry for that because my mother language is not English it's a bit difficult for me to define my thoughts.

I have tried the code you provided to me it's working fine the shortcut is opening the front end file from any drive, but let us assume that I want to share the front end file to a user's machine that he has only a drive C and F, whereby I have only a drive C and D (the front end file in my machine is in the drive D) in my machine the shortcut file is opening fine but if I do the same with other user it will not work as expected.

But to make sure, let me check the code in the other machines tomorrow at the office.

I will let you know tomorrow.

Thanks a lot for your patience.

Sent from my HUAWEI NXT-L29 using Tapatalk
 

vba_php

Forum Troll
Local time
Yesterday, 20:42
Joined
Oct 6, 2019
Messages
2,884
that's fine. get back to us when you check on it.
but let us assume that I want to share the front end file to a user's machine that he has only a drive C and F, whereby I have only a drive C and D (the front end file in my machine is in the drive D) in my machine the shortcut file is opening fine but if I do the same with other user it will not work as expected.
you might be missing the point here my friend. what you just described is *exactly* why you were given this code from the other guy:
Code:
Function getPath(strDBName As String) As String
    Dim d As Object
    Dim i As Integer
    Dim strFile As String
    On Error Resume Next
    With CreateObject("Scripting.FileSystemObject")
        For Each d In .Drives
            i = i + 1
            If i > 1 Then
                strFile = Dir(d.Path & "\" & strDBName)
                If strFile <> "" Then
                   getPath = d.Path & "\" & strDBName
                   Exit For
                End If
            End If
        Next
    End With
End Function
if you're unsure of what that does....it acts like a *pointer* and checks to see which drive (out of all drives that are mapped to the network drives on the user's machine) the file is located on. this:
Code:
strFile = Dir(d.Path & "\" & strDBName)
                If strFile <> "" Then
returns TRUE if the file is found on the drive letter being checked, thus it assigns the file's full path to getPath() and quits executing because it's pointless to continue when what is desired is achieved. the *pointer* concept is an advanced one not really applicable to MS Access but I just used that in hopes that it would be another way in which you could understand what is going on.
 

Alhakeem1977

Registered User.
Local time
Today, 04:42
Joined
Jun 24, 2017
Messages
308
Dear vba_php,
I am so sorry for my delayed response, I've missed this thread.
I would like to inform you that the code you provided is working fine. 👍

Thank you so much for your efforts 🌹
 

Users who are viewing this thread

Top Bottom