Ms Access Browse for file, copy to DB path, Create hyperlink for file in DB Path

[juan503]

I am trying to setup a MS Access DB in server side, users can attach (hyperlink) his/her local files to DB that all users can see files, so it should upload selected user's file from his/her computer to server and server change it to a hyperlink from current DB path. I've found and change this code to do this by open file dialog to select file ( it's uploading file to "FILES" folder in DB path ) and then open hyperlink dialog to select file from "Files" folder in DB path.i want remove 2nd dialog box (hyperlink) and user just pick a file from his/her computer and hyperlink will build and add pragmatically to desired object in form.

   Private Sub cmdbtnupload_Click()
Dim fDialog As Office.FileDialog
   Set fd = Application.FileDialog(msoFileDialogFilePicker)
   Dim varFile As Variant

   ' Set up the File Dialog. '
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    'fd.InitialFileName = Application.CurrentProject.Path
    fd.InitialFileName = "c:\"
   With fDialog

      ' Allow user to make multiple selections in dialog box '
      .AllowMultiSelect = False

      ' Set the title of the dialog box. '
      .Title = "Please select a File to Attache"

      ' Clear out the current filters, and add our own.'
      .Filters.Clear
      .Filters.Add "All Files", "*.*"

      ' Show the dialog box. If the .Show method returns True, the '
      ' user picked at least one file. If the .Show method returns '
      ' False, the user clicked Cancel. '
      If .Show = True Then
      ' This section takes the selected image and copy's it to the generated path'
      ' the string takes the file location, navigates to the image folder, uses the combo box selection to decide the file category, then uses the name from the filedialog to finish the path'
     FileCopy .SelectedItems(1), Application.CurrentProject.Path & "\Files\" & Combo153 & "\" & Dir(Trim(.SelectedItems.Item(1)))

Me.attachmentpath.SetFocus
RunCommand acCmdInsertHyperlink

      Else

      End If
   End With
End Sub

anyone can hand me? Thank you very much

[zmbd]

1) Your posted code has some minor errors, please follow the basic trouble shooting steps found here:https://bytes.com/forums/feedback/91...g-vba-sql-code you will need to repeat the debug compile until it does so without errors. The debugger stops on the very first error it finds.

2) If I understand correctly:

+ User opens the dialogbox

+ Selects the file

+ upon selection
++ you are copying the file to the network on the network path given in Line 28
...>>> I highly advise not to build your string for the FileCopy() directly in the function!
...a) Doing so makes it impossible to properly trouble-shoot if there is a malformed string.
...b) Once the function completes, the string is gone... you cannot reuse it.
++ you want to store the UNC for the file copied to the network in the cell directly as a Hyperlink.


3) First lets take a look at what a hyperlink field looks like:
(allen browne to the rescue again!) Introduction to Hyperlink fields

From the afore mentioned article we see that we need
display text # file name # any reference within the file

Note the use of the "#" as a separator... any address containing this symbol in the path will cause you errors when you try to store the string to the hyperlink field.

So build your string and store it to the hyperlink field....
> You need the file location currently being built in Line28 of your code
> Optionally you need a display text such as the file name.

Build a string using the aforementioned template.

Update your record-set's hyperlink field with the string.

so say your user selected:
C:\Users\<usern ame>\Documents\ ExamplePSFile.p s1

and you've copied it to
\\server\Users\ <username>\Docu ments\CursorRef resh.ps1

then the most basic string would be
"#\\server\User s\<username>\Do cuments\CursorR efresh.ps1#"

if you wanted the file name to be displayed in the field:
"CursorRefresh. ps1#\\server\Us ers\<username>\ Documents\Curso rRefresh.ps1#"

Either add/update via recordset or update action query methods.


>> This presupposes that there is a field in your table set to be used as a hyperlink field.

[juan503]

Thank you very much
im not pro at codes,what i meant was user select a file like "c:\note.pd f" with line 28 and a hyperlink dialog will open uppon line 31 to select the file from "files" folder and it will change the path to "Files\note.pdf " (it's availalb to open the file from every computer and if i moved DB and files its possible again)
so i want DB change "c:\note.pd f" to "Files\note.pdf " as a hyperlink without 2nd dialogbox ( hyperlink) ;)
just one dialog box that user picked the file from his/her computer -> file copied to server "files" folder and hyperlink to this file automatically generate ;)

[zmbd]

Then I followed your question

First you need to fix your code's errors as suggested in my prior post.

Second, either change your code to build the string for the network path and use that for the filecopy() or you can find a way to pull that information from the network drive. I personally would build the string first.

Third, build your hyperlink string as I mentioned above

Forth, update your record's hyperlink field with the string built in step three.

Finally, do a requery on the user's form so that the new information is shown in the form's window.

Once you have your current errors fixed and the basic code written for how you've chosen to update the hyperlink field you can post it back (formatted with the [CODE/] ) with any errors etc... and we can help you fine tune the scripting from there.

[juan503]

Thank you
The code is ok ,just need to enable "msoFileDialogF ilePicker" thus it wont error for me.
it will copy file to server with 1st file dialog and 2nd dialog open in DB path to allow user select file from "files" folder.for security reason and comfortably need to prohibit user to not see 2nd dialog and path, thus i need this code do copy file to server and make hyperlink in this format : "files/file.pdf" , beccause i need to move DB and files to another system even without network,server and any structure like this ( i need hyperlinks ) , THX ;)

[zmbd]

Juan,
I don't think you are following me.
You will omit line 31 in the original post
(RunCommand acCmdInsertHype rlink)

Instead, you will take the string value returned from your dialog box and use that to build the formatted string for the hyperlink field.

Once you have the formatted string available you can use one of several methods to either add a new record with this formatted string to your table or update an existing record in your table with the formatted string.

There should not be a second prompt.

[juan503]

yes, exactly.but i dont know how can use the given string or change it to hyperlink !!!
if i ommit line 31 this will just copy file to server.
if i have 2 hyperlink method like 1st: "c:\files\file. pdf" and 2nd: "files\file.pdf " then i choose 2nd one because can move db and file to any drive and system.thx

[zmbd]

Juan,

I'll be happy to assist you; however, you need to actually attempt to write the modified code - primarily because your situation is a unique application of the tool set.

Between the information in post#2 and post#4 you should be able to create a revised draft of your code.

Your current code builds the string within the function:

 FileCopy .SelectedItems(1), Application.CurrentProject.Path & "\Files\" & Combo153 & "\" & Dir(Trim(.SelectedItems.Item(1)))

You need to pull the file path string out of the function and assign it to a variable.

 zStr = .SelectedItems(1), Application.CurrentProject.Path & "\Files\" & Combo153 & "\" & Dir(Trim(.SelectedItems.Item(1)))
'
FileCopy zStr

now format the string according to the template given in post#2 per Allen Browne's tutorial and then use one of the various methods to either add or update the record-set with the new string.

[juan503]

Thx man,now it's in the way i like, you rock ;)

     FileCopy .SelectedItems(1), Application.CurrentProject.Path & "\files\" & "\" & Dir(Trim(.SelectedItems.Item(1)))
    
     For Each varItem In .SelectedItems 'There will only be 1
       'Extract Caption and and add Address of Hyperlink (Caption#Address)
          strSelectedFile = varItem
          strfilename = Mid$(varItem, InStrRev(varItem, "\") + 1)
          strHyperlinkFile = strfilename & "#" & "files\" & strfilename & "#"
        Me![attachmentpath] = strHyperlinkFile
     Next varItem

but another question is what else if there is a file with same name and size ?! i think if it can add date and time to the copied file name it's better.
i tried to add date to copy command but it's not working,just could change hyperlink name but the file still the old file name.
can you hand me again ?
Thx very much

[zmbd]

Again I see that you are building the string within the filecopy function and that will make your coding that much harder to accomplish The basic concept would be to insert something like:

format(Now(),"yyyy_mm_dd_ss"

into the string for the destination

Basic syntax for the FileCopy function:

Dim SourceFile As String
Dim DestinationFile As String
SourceFile = "SRCFILE"   ' Define source file name.
DestinationFile = "DESTFILE"   ' Define target file name.
FileCopy(SourceFile, DestinationFile)   ' Copy source to target.

[juan503]

Thx,i don't know other method !
in this way i should struggle with .selecteditem copy > rename > make hyperlink.
how can do copy with other method ?