Solved

Upload file to server using MS Access

Posted on 2008-10-02
6
1,389 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone 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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

738 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