Solved

Upload file to server using MS Access

Posted on 2008-10-02
6
1,385 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
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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…

914 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

14 Experts available now in Live!

Get 1:1 Help Now