gakiss2
Registered User.
- Local time
- Today, 05:09
- Joined
- Nov 21, 2018
- Messages
- 168
I've made a lot of progress with pulling attachments out and saving them in the newly revised database that uses the 'old fashioned' method of saving the files in a folder on the server then saving the file path in the database so users can still easily get to the files but the files don't live in your database, making it HUGE. We busted the 2G limit in less than two years.
Most of the code to allow users to pick files using the 'MSO' function I found on the internet, and didn't do a great job of giving credit. But since then I have integrated that into the working database. Recently launched the upgrades and all that seems to be working great.
Now I am working on a procedure to move through all of the previously entered records, pull out the attachments, save them to the approp. folder (by supplier name) then save the filepath. The goal is for the user to pull up an old record and have the files that he/she previously attached to the database now show up in the list of saved files that are available.
Actually I am most of the way there. I have the written the below code and gotten it to do everything perfectly for the first record in The Table. The Table is the main table that contains the records which currently have attachments saved in them, it is named tblDocsIssued. The table that holds the file path is tblAttachmentPaths.
Now I need to get the procedure to move through the records one by one and complete the steps mentioned. When I step through it, I can see each record being selected one after the other but the code only operates on the first record in the table over and over. Part of the procedure is to delete the attachments after they are moved to the proper folder and the file path saved. this causes the procedure to do nothing after the first go 'round. this is as it should be since it loops through the attachments until end of file.
As you might imagine, if I delete the first record of the table then the next time I run it, it starts on the new first record. I just did that to confirm. I can't do that for real since I want to maintain these records, just without those pesky attachments.
Any and all help appreciated.
Most of the code to allow users to pick files using the 'MSO' function I found on the internet, and didn't do a great job of giving credit. But since then I have integrated that into the working database. Recently launched the upgrades and all that seems to be working great.
Now I am working on a procedure to move through all of the previously entered records, pull out the attachments, save them to the approp. folder (by supplier name) then save the filepath. The goal is for the user to pull up an old record and have the files that he/she previously attached to the database now show up in the list of saved files that are available.
Actually I am most of the way there. I have the written the below code and gotten it to do everything perfectly for the first record in The Table. The Table is the main table that contains the records which currently have attachments saved in them, it is named tblDocsIssued. The table that holds the file path is tblAttachmentPaths.
Now I need to get the procedure to move through the records one by one and complete the steps mentioned. When I step through it, I can see each record being selected one after the other but the code only operates on the first record in the table over and over. Part of the procedure is to delete the attachments after they are moved to the proper folder and the file path saved. this causes the procedure to do nothing after the first go 'round. this is as it should be since it loops through the attachments until end of file.
As you might imagine, if I delete the first record of the table then the next time I run it, it starts on the new first record. I just did that to confirm. I can't do that for real since I want to maintain these records, just without those pesky attachments.
Code:
Private Sub transfer()
' Code to Save Attachment files in designated folder and save path in new Db
' Instantiate the parent recordset.
' sFolder = "C:\Users\gakis\Desktop\Attachments\" & SupFolder & "\QDAttach\"
Dim rsAttachs As Recordset
Dim i As Double
Dim db As Database
Set db = CurrentDb
Dim rsDocs As Recordset
' findstr = Me.NewLBTrackNo
Dim strFile As String
DoCmd.OpenTable "tblDocsIssued"
For i = 1 To 3
DoCmd.GoToRecord acDataTable, "tblDocsIssued", acNext
Set rsAttachs = db.OpenRecordset("tblDocsIssued")
' Get Supplier Folder
' SupFolder = Tables("tblDocsIssued").AssignedVendor ' Didn't work trying dlookup next
Dim SupFolder As Variant
SupFolder = DLookup("[AssignedVendor]", _
"tblDocsIssued")
Dim NewLBNo As String
NewLBNo = DLookup("[NewLBTrackNo]", "tblDocsIssued")
Debug.Print "Doc Number " & NewLBNo
Debug.Print "vendor " & SupFolder
' Check if folder exists
chkFolder = "C:\Users\gakis\Desktop\Attachments\" & SupFolder & "\QDAttach\"
If Dir(chkFolder, vbDirectory) = vbNullString Then
MsgBox "Supplier Folder does not exist"
Exit Sub
End If
' Instantiate the child recordset.
Set rsDocs = rsAttachs.Fields("Document").Value
' Loop through the attachments.
While Not rsDocs.EOF
' Code to capture filename of attachment
strFile = "C:\Users\gakis\Desktop\Attachments\" _
& SupFolder & "\QDAttach\" & rsDocs![FileName]
Debug.Print "file " & strFile
' Save current attachment to disk in correct supplier folder.
rsDocs.Fields("FileData").SaveToFile "C:\Users\gakis\Desktop\Attachments\" _
& SupFolder & "\QDAttach\"
' Save the path to the attachment path table
' Original Code: If CopyFile(sFile, sFolder & GetFileName(sFile)) = True Then
'Commenting out IF Then logic rsDocs gets error that its wrong type, makes sense but how to fix??
'If CopyFile(rsDocs, chkFolder & GetFileName(rsDocs)) = True Then
' Append FullFileName to tblAttachmentPaths
DoCmd.SetWarnings False
' Code to Delete the attachments from the Table
rsDocs.Delete
DoCmd.RunSQL "INSERT INTO tblAttachmentPaths (FullFileName, LBTrackNo) " _
& vbCrLf & "VALUES('" & strFile & "','" & NewLBNo & "')"
DoCmd.SetWarnings True
'Else
' MsgBox "HI 'Probably should report something here about the File Copy failing"
'End If
rsDocs.MoveNext
Wend
Next i ' next record
End Sub
Any and all help appreciated.