Solved

Upload file to server using MS Access

Posted on 2008-10-02
6
1,384 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
  • 2
  • 2
6 Comments
 
LVL 10

Expert Comment

by:slamhound
Comment Utility
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
Comment Utility
I am not good enough with VB to make this work.
0
 

Author Comment

by:jochs
Comment Utility
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
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now