473,322 Members | 1,781 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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

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.

Expand|Select|Wrap|Line Numbers
  1.    Private Sub cmdbtnupload_Click()
  2. Dim fDialog As Office.FileDialog
  3.    Set fd = Application.FileDialog(msoFileDialogFilePicker)
  4.    Dim varFile As Variant
  5.  
  6.    ' Set up the File Dialog. '
  7.    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
  8.     'fd.InitialFileName = Application.CurrentProject.Path
  9.     fd.InitialFileName = "c:\"
  10.    With fDialog
  11.  
  12.       ' Allow user to make multiple selections in dialog box '
  13.       .AllowMultiSelect = False
  14.  
  15.       ' Set the title of the dialog box. '
  16.       .Title = "Please select a File to Attache"
  17.  
  18.       ' Clear out the current filters, and add our own.'
  19.       .Filters.Clear
  20.       .Filters.Add "All Files", "*.*"
  21.  
  22.       ' Show the dialog box. If the .Show method returns True, the '
  23.       ' user picked at least one file. If the .Show method returns '
  24.       ' False, the user clicked Cancel. '
  25.       If .Show = True Then
  26.       ' This section takes the selected image and copy's it to the generated path'
  27.       ' 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'
  28.      FileCopy .SelectedItems(1), Application.CurrentProject.Path & "\Files\" & Combo153 & "\" & Dir(Trim(.SelectedItems.Item(1)))
  29.  
  30. Me.attachmentpath.SetFocus
  31. RunCommand acCmdInsertHyperlink
  32.  
  33.       Else
  34.  
  35.       End If
  36.    End With
  37. End Sub
anyone can hand me? Thank you very much
Dec 11 '15 #1
10 4061
zmbd
5,501 Expert Mod 4TB
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\<username>\Documents\ExamplePSFile.ps1

and you've copied it to
\\server\Users\<username>\Documents\CursorRefresh. ps1

then the most basic string would be
"#\\server\Users\<username>\Documents\CursorRefres h.ps1#"

if you wanted the file name to be displayed in the field:
"CursorRefresh.ps1#\\server\Users\<username>\Docum ents\CursorRefresh.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.
Dec 12 '15 #2
Thank you very much
im not pro at codes,what i meant was user select a file like "c:\note.pdf" 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.pdf" 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 ;)
Dec 12 '15 #3
zmbd
5,501 Expert Mod 4TB
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.
Dec 12 '15 #4
Thank you
The code is ok ,just need to enable "msoFileDialogFilePicker" 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 ;)
Dec 15 '15 #5
zmbd
5,501 Expert Mod 4TB
Juan,
I don't think you are following me.
You will omit line 31 in the original post
(RunCommand acCmdInsertHyperlink)

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.
Dec 15 '15 #6
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
Dec 16 '15 #7
zmbd
5,501 Expert Mod 4TB
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:
Expand|Select|Wrap|Line Numbers
  1.  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.

Expand|Select|Wrap|Line Numbers
  1.  zStr = .SelectedItems(1), Application.CurrentProject.Path & "\Files\" & Combo153 & "\" & Dir(Trim(.SelectedItems.Item(1)))
  2. '
  3. 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.
Dec 16 '15 #8
Thx man,now it's in the way i like, you rock ;)
Expand|Select|Wrap|Line Numbers
  1.      FileCopy .SelectedItems(1), Application.CurrentProject.Path & "\files\" & "\" & Dir(Trim(.SelectedItems.Item(1)))
  2.  
  3.      For Each varItem In .SelectedItems 'There will only be 1
  4.        'Extract Caption and and add Address of Hyperlink (Caption#Address)
  5.           strSelectedFile = varItem
  6.           strfilename = Mid$(varItem, InStrRev(varItem, "\") + 1)
  7.           strHyperlinkFile = strfilename & "#" & "files\" & strfilename & "#"
  8.         Me![attachmentpath] = strHyperlinkFile
  9.      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
Dec 17 '15 #9
zmbd
5,501 Expert Mod 4TB
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:
Expand|Select|Wrap|Line Numbers
  1. format(Now(),"yyyy_mm_dd_ss"
into the string for the destination

Basic syntax for the FileCopy function:
Expand|Select|Wrap|Line Numbers
  1. Dim SourceFile As String
  2. Dim DestinationFile As String
  3. SourceFile = "SRCFILE"   ' Define source file name.
  4. DestinationFile = "DESTFILE"   ' Define target file name.
  5. FileCopy(SourceFile, DestinationFile)   ' Copy source to target.
Dec 17 '15 #10
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 ?
Dec 17 '15 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: Dave Smithz | last post by:
Hi There, I have a PHP script that sends an email with attachment and works great when provided the path to the file to send. However this file needs to be on the same server as the script. ...
23
by: Francesco Zavatarelli | last post by:
I downloaded a free c-compiler and I'm trying to access a file in a windows directory c:\program files\... but I get an error when I run the code. Probably my declaration char* NAME is wrong....
2
by: Anthony | last post by:
Hi, I need to print word doc, excel, pdf, images(tiff), html. If this is impossible? Please suggest what i should do? - like handle each type in code separately? thanks
0
by: Rich G | last post by:
I am using vb.net and the Systm.io namespace for a document management project. The program receives files named with a GUID and an XML file loaded with metadata. One the fields is the real name of...
4
by: norm4h8 | last post by:
Hi everyone! I have a problem with trying to open a file in C. The following line in my code is suppoed to open a specified file if it exists and create a new one with this name if one doesn't...
0
by: smanisankar | last post by:
hi, the following is the full page code for uploading a file to server. since i got no idea to overwrite the file, i want delete the file if the file is already uploaded. i got the folder name...
0
by: Skywalker | last post by:
Hi. Can you please help me? I have problem;-) I am copying from one computer to another 50 MB large text file. For now is everything working. My question is, if I can in VBA for MS ACCESS show to...
7
by: Cheekie | last post by:
No one at my companies help desk can help me with this, so I thought I'd go to the experts =) I have an Access 2000 file that I've been using Access 2003 to open. I have a laptop and a desktop at...
12
by: dbuchanan | last post by:
Hello, (Is this the proper newsgroup?) === Background === I am building a solution with two projects. One project is my data access layer which contains my DataSet as an xsd file. The XSD...
3
by: prashantdixit | last post by:
Hi, I have a form "FormA" which three browse button and texbox control. There two radio buttons at the top with option option1: Auto option2: Manual These option decides how to select a...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.