Solved

Upload file to server using MS Access

Posted on 2008-10-02
6
1,390 Views
Last Modified: 2013-11-28
I am building a licensing database in MS Access 2003 that tracks licensing for my company. I would like to add the ability to browse to a license file (a .pdf file) and upload it. Ideally what would happen is the user would click a command button and be prompted with a browse window. They would then browse to the directory of the license and click upload. This would then rename the file to a dynamic name defined by a field on my form. It would then copy that file to a directory also defined by a dynamic field on my form. This field for right now would contain a static directory but at a later time could be re-written with a dynamic directory.

Example:
"      Original file name: License.pdf
"      Name of field on form that contains new file name: [txtRenewalFileName]
"      After renaming the file copy it to [txtUploadDirectory]

Then I would just use the FollowHyperlink Me.txtFileLocation command to call the file with a command button.

See attached screenshot for screen example.

Thanks,
-jochs

Form.JPG
0
Comment
Question by:jochs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
6 Comments
 
LVL 10

Expert Comment

by:slamhound
ID: 22629957
Below are a bunch of file handling functions that I forgot where I found them. The first function calls the FineFile function which uses a bunch of others. From this first function, you will have to copy the file from the location that the user supplied to the destination location.

Private Sub btnBrowse_Click()
    Dim strFile As String
    strFile = FindFile("", "Locate Calibration Spreadsheet", "*.xls", "*.xls")
    If strFile <> "" Then
        Me.SysCalibrationSheet = strFile
        strSQL = "UPDATE tblSystem SET SysCalibrationSheet='" & Me.SysCalibrationSheet.Value & "'"
        DoCmd.RunSQL strSQL
        DoEvents
    End If
End Sub
 
Private Type MSA_OPENFILENAME
    ' Filter string used for the Open dialog filters.
    ' Use MSA_CreateFilterString() to create this.
    ' Default = All Files, *.*
    strFilter As String
    ' Initial Filter to display.
    ' Default = 1.
    lngFilterIndex As Long
    ' Initial directory for the dialog to open in.
    ' Default = Current working directory.
    strInitialDir As String
    ' Initial file name to populate the dialog with.
    ' Default = "".
    strInitialFile As String
    strDialogTitle As String
    ' Default extension to append to file if user didn't specify one.
    ' Default = System Values (Open File, Save File).
    strDefaultExtension As String
    ' Flags (see constant list) to be used.
    ' Default = no flags.
    lngFlags As Long
    ' Full path of file picked.  When the File Open dialog box is
    ' presented, if the user picks a nonexistent file,
    ' only the text in the "File Name" box is returned.
    strFullPathReturned As String
    ' File name of file picked.
    strFileNameReturned As String
    ' Offset in full path (strFullPathReturned) where the file name
    ' (strFileNameReturned) begins.
    intFileOffset As Integer
    ' Offset in full path (strFullPathReturned) where the file extension begins.
    intFileExtension As Integer
End Type
 
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' Function FindFile
'   Inputs: SearchPath - Initial Path to set dialog to
'           Title - Title of the dialog box
'           Filtername - frendly name for type of files to be located (E.G. "Excel Files")
'           Filter - Wildcard Patern for Files (E.G. *.XLS)
'   Outputs:Returns the full path to File.
'   Comments: Displays the Open dialog box for the user to locate
'             a File.
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Public Function FindFile(SearchPath As String, Title As String, FilterName As String, Filter As String) As String
    Dim msaof As MSA_OPENFILENAME
    
    ' Set options for the dialog box.
    msaof.strDialogTitle = Title
    msaof.strInitialDir = SearchPath
    msaof.strFilter = MSA_CreateFilterString(FilterName, Filter)
    
    ' Call the Open dialog routine.
    MSA_GetOpenFileName msaof
    
    ' Return the path and file name.
    FindFile = Trim(msaof.strFullPathReturned)
    
End Function
 
Private Function MSA_GetOpenFileName(msaof As MSA_OPENFILENAME) As Integer
' Opens the Open dialog.
    
    Dim of As OPENFILENAME
    Dim intRet As Integer
 
    MSAOF_to_OF msaof, of
    intRet = GetOpenFileName(of)
    If intRet Then
        OF_to_MSAOF of, msaof
    End If
    MSA_GetOpenFileName = intRet
End Function
 
Private Function MSA_CreateFilterString(ParamArray varFilt() As Variant) As String
' Creates a filter string from the passed in arguments.
' Returns "" if no argumentss are passed in.
' Expects an even number of argumentss (filter name, extension), but
' if an odd number is passed in, it appends "*.*".
    
    Dim strFilter As String
    Dim intRet As Integer
    Dim intNum As Integer
 
    intNum = UBound(varFilt)
    If (intNum <> -1) Then
        For intRet = 0 To intNum
            strFilter = strFilter & varFilt(intRet) & vbNullChar
        Next
        If intNum Mod 2 = 0 Then
            strFilter = strFilter & "*.*" & vbNullChar
        End If
        
        strFilter = strFilter & vbNullChar
    Else
        strFilter = ""
    End If
 
    MSA_CreateFilterString = strFilter
End Function

Open in new window

0
 

Author Comment

by:jochs
ID: 22634198
I am not good enough with VB to make this work.
0
 

Author Comment

by:jochs
ID: 22650852
Is there a cleaner way I can do this? I do not think I can alter this code to do what I need.
0
 
LVL 10

Accepted Solution

by:
slamhound earned 500 total points
ID: 22655836
I don't know of a simpler way. Try copying all this code (apart from the first function) into a module. Then create a buton with the code below. I've made it a little more generic for you.

The module code should run without you having to touch it. You've just got to do something with the file when the system finds it (save it, write to database etc)
Private Sub btnBrowse_Click()
    Dim strFile As String
    strFile = FindFile("", "Locate the file", "*.xls", "*.*")
    If strFile <> "" Then
        Me.CtrlFileName= strFile
    End If
End Sub

Open in new window

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question