Payin' Cost for using Attachment (1 Viewer)

gakiss2

Registered User.
Local time
Today, 14:47
Joined
Nov 21, 2018
Messages
124
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.

Code:
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:

arnelgp

error reading drive A:
Local time
Tomorrow, 05:47
Joined
May 7, 2009
Messages
9,273
not tested yet.
Code:
Private Sub CmdSaveAttach_Click()
    Dim db As DAO.Database
    Dim rsAttachs As DAO.Recordset2
    Dim rsDocs As DAO.Recordset2
    Dim SupFolder As String
 
    ' modify this to point to the subfolder
    ' DO NOT remove <pFolder>, this will be Replace() in our code
    ' with the correct Vendor folder.
    Const chkFolder As String = "\\Lbcixs1\lbi1_d\deptqa\Suppliers\Supplier Specific\<pFolder>\QDAttach"
 
    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
     
        With rsAttachs
            If Not (.BOF And .EOF) Then _
                .MoveFirst
            ' … Code to move to desired employee
         
         
            ' Loop through the attachments.
            While Not .EOF
                '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.
                ' 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.
             
                SupFolder = Replace(chkFolder, "<pFolder>", !AssignedVendor)
             
                ' Force create the Folders
                Call ForceCreateFolder(subfolder)
             
                ' Instantiate the child recordset.
                Set rsDocs = rsAttachs.Fields("Document").Value
             
                With rsDocs
                    If Not (.BOF And .EOF) Then _
                        .MoveFirst
                     
                    While Not .EOF
                        If Dir(SuppFolder & .Fields("FileName")) <> "" Then Kill (SuppFolder & .Fields("FileName"))
                        .Fields("FileData").SaveToFile SupFolder & .Fields("FileName")
                        .MoveNext
                    Wend
                    .Close
                End With
                Set rsDocs = Nothing
                .MoveNext
            Wend
            .Close
        End With
        Set rsAttachs = Nothing
    'Next i
    Set db = Nothing
    MsgBox "Don saving attachments!"
End Sub

Public Function ForceCreateFolder(ByVal pPath As String)
    Dim arr As Variant
    Dim var As Variant
    Dim s As String
    arr = Split(pPath, "\")
    On Error Resume Next
    For Each var In arr
        s = s & var & "\"
        'Debug.Print s
        VBA.MkDir s
    Next
End Function
 
Last edited:

gakiss2

Registered User.
Local time
Today, 14:47
Joined
Nov 21, 2018
Messages
124
not tested yet.
Code:
Private Sub CmdSaveAttach_Click()
    Dim db As DAO.Database
    Dim rsAttachs As DAO.Recordset2
    Dim rsDocs As DAO.Recordset2
    Dim SupFolder As String

    ' modify this to point to the subfolder
    ' DO NOT remove <pFolder>, this will be Replace() in our code
    ' with the correct Vendor folder.
    Const chkFolder As String = "\\Lbcixs1\lbi1_d\deptqa\Suppliers\Supplier Specific\<pFolder>\QDAttach"

    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
    
        With rsAttachs
            If Not (.BOF And .EOF) Then _
                .MoveFirst
            ' … Code to move to desired employee
        
        
            ' Loop through the attachments.
            While Not .EOF
                '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.
                ' 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.
            
                SupFolder = Replace(chkFolder, "<pFolder>", !AssignedVendor)
            
                ' Force create the Folders
                Call ForceCreateFolder(subfolder)
            
                ' Instantiate the child recordset.
                Set rsDocs = rsAttachs.Fields("Document").Value
            
                With rsDocs
                    If Not (.BOF And .EOF) Then _
                        .MoveFirst
                    
                    While Not .EOF
                        If Dir(SuppFolder & .Fields("FileName")) <> "" Then Kill (SuppFolder & .Fields("FileName"))
                        .Fields("FileData").SaveToFile SupFolder & .Fields("FileName")
                        .MoveNext
                    Wend
                    .Close
                End With
                Set rsDocs = Nothing
                .MoveNext
            Wend
            .Close
        End With
        Set rsAttachs = Nothing
    'Next i
    Set db = Nothing
    MsgBox "Don saving attachments!"
End Sub

Public Function ForceCreateFolder(ByVal pPath As String)
    Dim arr As Variant
    Dim var As Variant
    Dim s As String
    arr = Split(pPath, "\")
    On Error Resume Next
    For Each var In arr
        s = s & var & "\"
        'Debug.Print s
        VBA.MkDir s
    Next
End Function

Thank you very much. I am going to try to try it out later today and I will let you know how it goes.
 

gakiss2

Registered User.
Local time
Today, 14:47
Joined
Nov 21, 2018
Messages
124
quiet morning so I had a chance to try it. I did put it in its own module so I had to change me. to point it to the control that has 'assigned vendor. There was a minor typo that is fixed, SuppFolder > SupFolder. Testing I found the 'assigned vendor' got added to the filename and the file got saved in the folder named after 'assigned vendor' rather than ...vendor\QDAttach\. but Debug.Print right before CAll ForceCreateFolder shows the path to be correct. It is progressin through the records properly.

Sorry I didn't get the whole story out in the initial post: Previous DB allowed user to type any assigned vendor. You can imagine some misspelling or just wrong format, AFG, American Foundry Group, Am. Foundry and etc. I have a table with all the legit SupFolder names. So I'll code in a search to see if its there then an input box so I can put in correct. this operation should be 'one time' so I don't need to make it 'userproof'.

After this success I am thinking that I should be able to run the file through the new 'attach' process and avoid the next process of putting these all through the new 'attach' process manually. By now I feel this is becoming a new problem. Not sure of the protocol. Close this and start new or let it evolve / expand. I'm happy to hear your guidance on that question.
 

June7

AWF VIP
Local time
Today, 13:47
Joined
Mar 9, 2014
Messages
2,924
Not clear to me what the 'new problem' is.
 

gakiss2

Registered User.
Local time
Today, 14:47
Joined
Nov 21, 2018
Messages
124
Not clear to me what the 'new problem' is.
Sorry, I'm not clear. I mean that I don't know at what point the scope of the original 'issue / problem / challenge' ceases to be the original and becomes a candidate to be a fresh new thread rather than just a continuation. Your help has definitely moved me past the place I was stuck. So now I move on to the next 'issue / problem / challenge'.

Right Now I am backed up a few steps since I realize I was saving the file path in the front - end DB and not on the backend where it is needed so all can access (pun?) the files that are 'attached'.

After that I will get back to pulling the old truly attached files out and getting them 'attached' (file path saved) into the new one.

I am always happy to hear ideas. But I am not at all sure I have provided anything close to a well defined problem.

Much Thanks for your help.
 

June7

AWF VIP
Local time
Today, 13:47
Joined
Mar 9, 2014
Messages
2,924
Why would it be saved into frontend? Aren't you saving to a linked table?
 

gakiss2

Registered User.
Local time
Today, 14:47
Joined
Nov 21, 2018
Messages
124
Why would it be saved into frontend? Aren't you saving to a linked table?
It was basically a mistake on my part. I got some of that code from online which launches the file picker, saves the file and the path. So I stitched it into my existing database. the code to do that should be in the front end. I just made a new table in the front end and had the code put the path in there. Hindsight is 20/20. Now I really can't say why I did that, just not thinking I guess. I have made some progress with it. I changed the datasource of the subform where the user selects the new file and it is saving the file path in the backend Db and table. I just realized that field has to hold multiple filepaths so, so far I have tried making it a longtxt, append only field and am just ready to test that in a bit. What Fun!
 

gakiss2

Registered User.
Local time
Today, 14:47
Joined
Nov 21, 2018
Messages
124

gakiss2

Registered User.
Local time
Today, 14:47
Joined
Nov 21, 2018
Messages
124
saving the paths in the back end table is working well now. I'm still working on a procedure to transfer the existing attached files to the folders with the file paths saved in the retooled Db. Ill need to work on it some more before I can formulate a specific question. At least now I can move through the records thanks to arnelgp. Thanks all for the help.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom