Yes my Db is HUGE! because I used attachments in all the wrong ways. I retooled my Db so now it stores file paths instead of files. Now I need to migrate a LOT of attached files to their new proper location and store the paths in my newly retooled Db. One of the fields in the record is now the name of a folder on a path where those files go. There are dozens of folders but they are all in place and ready to receive new attachments. But I also want to get the old attachments out of the old database and into the new one. the attachments are in the back end Db and my retooled Db uses the same backend Db. So far I have played with 'SaveToFile' and it works but I need to figure out how to loop through all the records and pull them all out. Here is the code, it doesn't work now after I added the loop and DoCmd.GoToRecord… acNext. I thought that would push me along from one record to the next but, no. Here is the code I am working on.
thank You for your help
Private Sub CmdSaveAttach_Click() MsgBox "lets get saving" ' Instantiate the parent recordset. Set db = CurrentDb findstr = Me.NewLBTrackNo ' this was an attempt to first go to a record (which is displayed on the form this button is on) then move the attached files for that record. ' Now I think it may be better to do them all with one operation so I went to the for loop. there are a lot more than 10, just trying it out. For i = 1 To 10 DoCmd.GoToRecord acDataTable, "tblDocsIssued", acNext ' this is my latest addition, Don't work. Set rsAttachs = db.OpenRecordset("tblDocsIssued") ' This was in code I borrowed where I learned (just a little obvs) about SaveToFile ' … Code to move to desired employee ' Instantiate the child recordset. Set rsDocs = rsAttachs.Fields("Document").Value ' Loop through the attachments. While Not rsDocs.EOF ' Get Supplier Folder ' SupFolder = Forms("frmDocDetail").AssignedVendor.Column(0) SupFolder = Me.AssignedVendor.Column(0) ' I made a new folder for each Supplier to hold attachments. This is where I want to put the attached files I pull out. Also the location the newly retooled Db puts the files that I am saving the path for. chkfolder = "\\Lbcixs1\lbi1_d\deptqa\Suppliers\Supplier Specific\" & SupFolder & "\QDAttach" If Dir(chkfolder, vbDirectory) = vbNullString Then ' This worked at point, probably still does MsgBox "Supplier Folder does not exist" Exit Sub End If ' Save current attachment to disk in the "My Documents" folder. ' You can see this is modified to point the folder I want rsDocs.Fields("FileData").SaveToFile _ "C:\Users\gkissick\Desktop\testfolder" ' "\\Lbcixs1\lbi1_d\deptqa\Suppliers\Supplier Specific\" & SupFolder & "\QDAttach" rsDocs.MoveNext Wend ' I've gotten it to work on the first record in the table but that is a far as I have gotten. Next i End Sub
thank You for your help
Last edited by a moderator: