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)

 

[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

 

[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

 

[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.

 

[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 ~

 

[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.

 

[A380b747]

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

To update the table Gc_Eventos in which I am updating the field "Cont" and placing the location of the file there. But it doesn´t work anyway. I don´t know why is the cause of the error??

 

[A380b747]

Don't use the multi-select option. Just log one at a time.

Do I need to set it as FALSE or remove it from the code. If i removed it, would i need to remove or modificate the code reltated to "varItem"??

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

Set f = Application.FileDialog(3)
f.AllowMultiSelect = True (REMOVE??))
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

 

[A380b747]

It is extremely hard to read code that is not formatted. Please use the code tool when uploading code.

Your logic is garbled. If you are selecting multiple files, you need to use an append query to add a new row for each file. If you only select one at a time, you don't need a query at all. You just put the path into the bound text field of your subform.

I don´t want to add a new row since the row has already been created before. Just want to update the table with the new data using UPDATE command. Which part do i need to remove?

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

 

[LarryE]

I was not aware of where these files were stored in the first place. If you want to store the location of the selected file, then just replace:
Application.FollowHyperlink SelectedFile
with
Me.txtLocation=SelectedFile

So you will need two VBA routines. One to locate and store the file location and the other to locate and open the file for viewing.

 

[A380b747]

Thanks for the like @A380b747 but that doesn't help anyone who finds this thread later. If my solution helped you, please use your words and tell people how you implemented it.

Good news!! I could finally save the file location in the table!! Thanks for your help Pat! That step is done.

I could also manage to store the table on SharePoint instead of Ms Access, Access is only the front-end section of the program but the table is stored in other place.

Now, I´m trying to use this code to attach the file (not location) in the table but it is not working. Something is missing??' Could you help me with this??


field name: Contrato
Table name: GC_Eventos
Filepath / location= txtLocation

Dim db As Database
Dim rst_PDF As Recordset
Dim rst_Attachment As Recordset
Dim fld_att As Field

    Set db = CurrentDb
    Set rst_PDF = db.OpenRecordset("Select Contrato FROM GC_Eventos WHERE Evento_ID = Me.Evento_ID")
    Set fld_att = rst_PDF("Attachment")
    rst_PDF.MoveFirst

    Set rst_Attachment = fld_att.Value

    rst_PDF.Edit
    rst_Attachment.AddNew
    rst_Attachment("FileData").LoadFromFile rst_PDF!(Me.txtLocation)
    rst_Attachment.Update
    rst_PDF.Update
    rst_PDF.MoveNext

    rst_Facturas.Close
    db.Close

    Set fld_att = Nothing
    Set rst_Attachment = Nothing
    Set rst_PDF = Nothing
    Set db = Nothing

 

[A380b747]

We've all told you to not store the file inside the database. If you want help, you are at least going to tell us what the error you are getting is.

I know and I agree with everyone here but the person who is responsible of this program wants to store the file inside the database using the code I have shared. That is what I´m trying to do.

Here is the error that comes out: Perhaps something is missing...