Saving files in Network Drive instead of attachment

[Hanz]

I'm new with access and I want to use the features of the attachment to my database. I did look for tutorials and found some useful tips on how to work with attachments. on my search I also read some comments that this feature in access will dramatically increase my db file. I did my test and on my test, I check my db file size before attachment and it's around 8,000kb after I did my attachment my db size goes up to around 11,000kb. I'm having a conclusion that this attachment feature in access won't work for me and look for some other options. i searched previous post on this forum and found this:


https://www.access-programmers.co.uk/forums/showthread.php?t=33877&highlight=attachment


https://access-programmers.co.uk/forums/showthread.php?s=&threadid=32130&highlight=link+documents


but it doesn't really help me because the first link only works in 32 bit. I learned to use access through online tutorials and I need a detailed tutorial how to work with attaching files in my access form, save it in network drive and will be able to view that file somewhere in my form. I will be working with PDF attachment only.

 

[June7]

PDF file can only be viewed in form with internet browser control.

Save path to file in text field.

 

[Hanz]

Hi June7, Thanks for idea. I got the code below and i think this works fine for me.

Private Sub btnUploadFile_Click()
On Error GoTo errmessage
Dim File As FileDialog
Dim FSO As Object
Dim FromLoc As String
Dim ToLoc As String
Dim FName() As String
Dim db As Database
Dim rs As Recordset
Dim DataErr As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_Library")
Set File = Application.FileDialog(msoFileDialogFilePicker)
Set FSO = CreateObject("Scripting.FileSystemObject")
File.AllowMultiSelect = False
File.Show
FromLoc = File.SelectedItems.Item(1)
ToLoc = "G:\ScannedFiles"

If Len(FromLoc) = 0 Then
MsgBox "No File Selected", vbOKOnly, "Swiftly"
Exit Sub
End If

FSO.CopyFile Source:=FromLoc, Destination:=ToLoc
FName = Split(FromLoc, "")

rs.AddNew
rs!JobNumber = Me.txtJobNumber
rs!DocumentTypeID = Me.txtDocType
rs!DocumentOriginatorID = Me.txtDocOriginator
rs!FileLocation = ToLoc & FName(UBound(FName))
rs.Update
errmessage:

Select Case Err.Number
Case 3022
MsgBox "You already uploaded this type of document for this job", vbOKOnly
Case 5
MsgBox "No File Selected", vbOKOnly
Case Else
On Error Resume Next

End Select


DoCmd.Close

End Sub