Upload files to a DB table using a button on a form

[A380b747]

Hi everyone

Hope you are doing well.

I´m struggling with VBA code. Since i´m a beginner with Access, I don´t know how to proceed with this.

So basically, I have a form and there is a button inside that form. So everytime, the user clicks on it, it should be able to upload an external file and store it in a database. The file must be stored in the record (row) that it is currently open with the form. I use an unique ID to differentiate each record on the table. The value of that unique register is shown in a textbox called "Evento_ID" in the form.

The table name is "GC_Eventos"
Table field is "Contrato"

I have been trying with codes I have found in the Internet but I have not got success yet. Could you help me??

I would really appreciate it

 

[theDBguy]

Hi. What is the data type of the field that will store the uploaded file? Storing files in a table will make your database grow faster. It's usually recommended not to store files in tables. Can't you just store the file's location instead?

 

[strive4peace]

hi @A380b747

echoing @theDBguy's comment ... best not to store external files IN the database. Better to store path\filename and render with Image control if pictures or maybe Web Browser control for other types of files.

I like to put all files referenced by the database in a folder under the back-end, so when a file is identified, it is either copied or moved (if they're really big) to that location

 

[A380b747]

Hi. What is the data type of the field that will store the uploaded file? Storing files in a table will make your database grow faster. It's usually recommended not to store files in tables. Can't you just store the file's location instead?

hi @A380b747

echoing @theDBguy's comment ... best not to store external files IN the database. Better to store path\filename and render with Image control if pictures or maybe Web Browser control for other types of files.

I like to put all files referenced by the database in a folder under the back-end, so when a file is identified, it is either copied or moved (if they're really big) to that location

It´s a PDF file.

Indeed, attached files make the database to grow faster but in this case, the file is needed to be attached because other users need to have access to the file and the db is not large.

Or where can they store the files not using the Access DB and at the same time, another user can have access to the file using a register in a table on Access???

 

[strive4peace]

hi @A380b747

> other users need to have access

is your database split into Front-End and Back-End? If shared filed are placed in a folder under the back-end location, maybe called 'Files', then a relative path can be stored that is different for each user but still points to the same place.

 

[A380b747]

hi @A380b747

> other users need to have access

is your database split into Front-End and Back-End? If shared filed are placed in a folder under the back-end location, maybe called 'Files', then a relative path can be stored that is different for each user but still points to the same place.

Well, I do not know.

The users will upload the file and later, if they want to see the file, they should be able to see and download the file. It is just a simple program where the final user is requested to upload a file through a form when clicking on a button in the form. The file needs to be stored somewhere and the final user should be able to see the file.

Other users should be able to see the file (The leader of that person and the one who is creating the program (myself)

 

[Pat Hartman]

Even small numbers of embedded files can bloat the database so the best practice as the others have said is to link to them. Define a folder on the server. You can make it a child of the folder that holds the db as has already been suggested. That way, the application can always control the path and you won't have to store it. You only need to store the file name. The app will then get the path to the BE using various means we can describe and concatenate the stored file name and use the FollowHyperlink method using that concatenated string.

 

[A380b747]

Even small numbers of embedded files can bloat the database so the best practice as the others have said is to link to them. Define a folder on the server. You can make it a child of the folder that holds the db as has already been suggested. That way, the application can always control the path and you won't have to store it. You only need to store the file name. The app will then get the path to the BE using various means we can describe and concatenate the stored file name and use the FollowHyperlink method using that concatenated string.

I got it. So the idea is to has like a "folder hub" where all the files the users upload will be placed in. Access will only store the location of the file within the folder hub but without compromising the other files other users have uploaded. That means that a specific user can only get access to its files and other users cannot see what is stored in the "folder hub". The user can only see its files clicking on the location path on access

Am I right?

Sounds good for me

 

[Pat Hartman]

If you need separation of the folders, that increases the complexity because you would need external security. I'm assuming you have security in the Access app that already handles this so that users cannot look at records created by other users.

If you have IT support, you should explain the problem to them and they can help with setting up access to the folders. In your Users table where you define user security, store the path for each user. That saves a lot of room especially if you lean toward long path names since you store the path once per user instead of with each linked file.

 

[A380b747]

If you need separation of the folders, that increases the complexity because you would need external security. I'm assuming you have security in the Access app that already handles this so that users cannot look at records created by other users.

If you have IT support, you should explain the problem to them and they can help with setting up access to the folders. In your Users table where you define user security, store the path for each user. That saves a lot of room especially if you lean toward long path names since you store the path once per user instead of with each linked file.

Indeed, The Access app already has a security filter which filter the records for a specific user when logging in.

So, how to make this? Where to place the folder hub? Share Point??

 

[LarryE]

If you need code to search for and open a pdf file, then you can use this:

Public Function SelectPDF()
ChDir CurrentProject.Path
Dim ImportFileDialog As FileDialog
Set ImportFileDialog = Application.FileDialog(msoFileDialogFilePicker)
Dim SelectedFile As Variant
Dim db As DAO.Database
Set db = CurrentDb
With ImportFileDialog
.AllowMultiSelect = True
.Title = "Select A File To Import"
.ButtonName = "Import"
.Filters.Clear
.Filters.Add "PDF Files", "*.pdf", 1
.FilterIndex = 1
If .Show = -1 Then 'If user selected a file
    For Each SelectedFile In .SelectedItems
        Application.FollowHyperlink SelectedFile
    Next
Else 'If user cancelled
    Set ImportFileDialog = Nothing
    Exit Function
End If
End With
Exit Function
End Function

 

[Pat Hartman]

So, how to make this? Where to place the folder hub? Share Point??

It doesn't matter. Place it wherever you want. Share Point isn't necessary and will certainly complicate the issue. If the files are NEVER shared, the files can stay on the users local drive, assuming that is regularly backed up. Most of the time it isn't so it is better to put the files on a network drive. put them ANYWHERE you want. You will, however, need IT support to secure the folders if they cannot be viewed or updated by others.

 

[A380b747]

It doesn't matter. Place it wherever you want. Share Point isn't necessary and will certainly complicate the issue. If the files are NEVER shared, the files can stay on the users local drive, assuming that is regularly backed up. Most of the time it isn't so it is better to put the files on a network drive. put them ANYWHERE you want. You will, however, need IT support to secure the folders if they cannot be viewed or updated by others.

I have no IT support. If this solution needs IT support, I would rather go for the option of storing the file in the DB. The DB is not large and will not grow enough to become a potential issue. Just one the easiest and simplest option.

 

[Pat Hartman]

Why can't the others all see the documents?

 

[A380b747]

Why can't the others all see the documents?

Because it is a personal process, the users are actually people who make contracts with their customers. There is confidential information in the files they need to upload. But I was thinking that this might work because they have already been separated when they access the program ... so a user will only have access to the records the user have processed .

So now i have started building the VBA code. The first part was already provided by Larry, this part of the code let me select the PDF file. How to store the selected file in the folder or table??? Or where does the file is stored using the code below??

Private Sub Command878_Click()

ChDir CurrentProject.Path
Dim ImportFileDialog As FileDialog
Set ImportFileDialog = Application.FileDialog(msoFileDialogFilePicker)
Dim SelectedFile As Variant
Dim db As DAO.Database
Set db = CurrentDb
With ImportFileDialog
.AllowMultiSelect = True
.Title = "Select A File To Import"
.ButtonName = "Import"
.Filters.Clear
.Filters.Add "PDF Files", "*.pdf", 1
.FilterIndex = 1
If .Show = -1 Then 'If user selected a file
For Each SelectedFile In .SelectedItems
Application.FollowHyperlink SelectedFile
Next
Else 'If user cancelled
Set ImportFileDialog = Nothing
Exit Sub
End If
End With
Exit Sub

End Sub

 

[strive4peace]

@A380b747 as long as you're browsing for the file anyway, why not just copy it into a Files folder under the back-end database and store the path\filename? You can also include the initials of the person who added the file to make it easier to see all of their files ~

 

[Pat Hartman]

If you have no one to set up proper security on the server, just have people save the files on their personal drives. Make sure they are properly backed up though. Carbonite is an excellent product. I use it for all my computers. It silently backs up all changed files.

 

[A380b747]

@A380b747 as long as you're browsing for the file anyway, why not just copy it into a Files folder under the back-end database and store the path\filename? You can also include the initials of the person who added the file to make it easier to see all of their files ~

I have tried something similar. Actually I am trying in many ways. I get the file location and then placed it in a textbox (txtLocation) in the form. And then, when I try to update the table using a query, it does not let me continue because something is broken. Perhaps because the query is defined as a string and the pathfile is in another type of format??? I do not know. Because using the Immediate window for debug, the query actually does what I want

Private Sub Command870_Click()
Dim strSql7 As String

Dim f As Object
Dim strFile As String
Dim strFolder As String
Dim varItem As Variant

Set f = Application.FileDialog(3)
f.AllowMultiSelect = True
If f.Show Then
For Each varItem In f.SelectedItems
strFile = Dir(varItem)

strFolder = Left(varItem, Len(varItem) - Len(strFile))
MsgBox "Folder" & strFolder & vbCLf & _
"File:" & strFile

txtLocation = strFolder + strFile

strSql7 = "UPDATE GC_Eventos SET Cont = " & Me.txtLocation & " WHERE Evento_ID = " & Me.Evento_ID & ""
Debug.Print strSql7
CurrentDb.Execute strSql7

Next
End If

Set f = Nothing


End Sub


Immediate Wondow:
"UPDATE GC_Eventos SET Cont = C:\Users\user.name\Documents\kkkkkk.pdf WHERE Evento_ID = 789"

So i want to copy the blue text (file´s location) to the table

 

[A380b747]

If you have no one to set up proper security on the server, just have people save the files on their personal drives. Make sure they are properly backed up though. Carbonite is an excellent product. I use it for all my computers. It silently backs up all changed files.

Actually the users have the files on their computers! I only want to include the file (or file location) they have on their computers in my program.

 

[Pat Hartman]

Why are you running a query to update the location of the file?