Split Database - New Problems Reading Records

[sirhannick]

My database was working fine until I split the database and now I'm having issues with this function:

Public Function OpenFirstAttachmentAsTempFile(ByRef rstCurrent As DAO.Recordset, ByVal strFieldName As String) As String

    Dim rstChild As DAO.Recordset2
    Dim fldAttach As DAO.Field2
    Dim strFilePath As String
    Dim strTempDir As String
    
    'On Error GoTo ErrorHandlingCode
    
    '-------------------------Export Attached Text File to Temp Folder-------------------------------
    
    strTempDir = Environ("Temp") ' Get the Temp directory from the environment variable.
    
    If Right(strTempDir, 1) <> "\" Then strTempDir = strTempDir & "\" ' Make sure the path always ends with a backslash.
        Set rstChild = rstCurrent.Fields(strFieldName).Value ' the .Value for a complex field returns the underlying recordset.
        strFilePath = strTempDir & rstChild.Fields("FileName").Value ' Append the name of the first (and only) attached file to temp dir.
        If Dir(strFilePath) <> "" Then ' the file already exists--delete it first.
        VBA.SetAttr strFilePath, vbNormal ' remove any file attributes (e.g. read-only) that would block the kill command.
        VBA.Kill strFilePath ' delete the file.
    End If

    Set fldAttach = rstChild.Fields("FileData") ' The binary data of the file.
    fldAttach.SaveToFile strFilePath
    rstChild.Close ' cleanup
    'VBA.Shell "Explorer.exe " & Chr(34) & strFilePath & Chr(34), vbNormalFocus ' Use Windows Explorer to launch  the file.
    OpenFirstAttachmentAsTempFile = strFilePath 'Return temp file path
    Exit Function
    
ErrorHandlingCode:
    MsgBox ("There was a problem in reading the EEPROM text attachment. Please contact your administrator.")
    
End Function 'OpenFirstAttachmentAsTempFile

It's getting stuck on the "strFilePath = strTempDir & rstChild.Fields("FileName").Value" line. The debugger says that there is no reord. The strTempDir is correct. I am not the original writer of this code so I don't understand it 100%. I am assuming that the issue has to with linking to the database. Any help please?

 

[way2bord]

My database was working fine until I split the database and now I'm having issues with this function:

Public Function OpenFirstAttachmentAsTempFile(ByRef rstCurrent As DAO.Recordset, ByVal strFieldName As String) As String
 
    Dim rstChild As DAO.Recordset2
    Dim fldAttach As DAO.Field2
    Dim strFilePath As String
    Dim strTempDir As String
 
    'On Error GoTo ErrorHandlingCode
 
    '-------------------------Export Attached Text File to Temp Folder-------------------------------
 
    strTempDir = Environ("Temp") ' Get the Temp directory from the environment variable.
 
    If Right(strTempDir, 1) <> "\" Then strTempDir = strTempDir & "\" ' Make sure the path always ends with a backslash.
        [B]Set rstChild = rstCurrent.Fields(strFieldName).Value[/B] ' the .Value for a complex field returns the underlying recordset.
        strFilePath = strTempDir & rstChild.Fields("FileName").Value ' Append the name of the first (and only) attached file to temp dir.
        If Dir(strFilePath) <> "" Then ' the file already exists--delete it first.
        VBA.SetAttr strFilePath, vbNormal ' remove any file attributes (e.g. read-only) that would block the kill command.
        VBA.Kill strFilePath ' delete the file.
    End If
 
    Set fldAttach = rstChild.Fields("FileData") ' The binary data of the file.
    fldAttach.SaveToFile strFilePath
    rstChild.Close ' cleanup
    'VBA.Shell "Explorer.exe " & Chr(34) & strFilePath & Chr(34), vbNormalFocus ' Use Windows Explorer to launch  the file.
    OpenFirstAttachmentAsTempFile = strFilePath 'Return temp file path
    Exit Function
 
ErrorHandlingCode:
    MsgBox ("There was a problem in reading the EEPROM text attachment. Please contact your administrator.")
 
End Function 'OpenFirstAttachmentAsTempFile

It's getting stuck on the "strFilePath = strTempDir & rstChild.Fields("FileName").Value" line. The debugger says that there is no reord. The strTempDir is correct. I am not the original writer of this code so I don't understand it 100%. I am assuming that the issue has to with linking to the database. Any help please?

Your set is strangely declared.
Set rstChild = rstCurrent.Fields(strFieldName).Value ' per comment, this returns a recordset? I'm not so sure...

strFilePath = strTempDir & rstChild.Fields("FileName").Value


Try commenting out rstChild and use

strFilePath = strTempDir & rstCurrent.Fields(strFieldName).Value

I believe the first set should be a "Let"

Also, double check and make sure you're not sending "currentdb" for the rstCurrent recordset function parameter, without linking all appropriate tables.

 

[sirhannick]

    If Right(strTempDir, 1) <> "\" Then strTempDir = strTempDir & "\" ' Make sure the path always ends with a backslash.
    
        'Set rstChild = rstCurrent.Fields(strFieldName).Value ' the .Value for a complex field returns the underlying recordset.
        
        'strFilePath = strTempDir & rstChild.Fields("FileName").Value ' Append the name of the first (and only) attached file to temp dir.
        
        strFilePath = strTempDir & rstCurrent.Fields(strFieldName).Value
        
        If Dir(strFilePath) <> "" Then ' the file already exists--delete it first.
            VBA.SetAttr strFilePath, vbNormal ' remove any file attributes (e.g. read-only) that would block the kill command.
            VBA.Kill strFilePath ' delete the file.
    End If

I'm getting a Run-time error '13': Type mismatch on the line we added:
strFilePath = strTempDir & rstCurrent.Fields(strFieldName).Value

strTempDir = "C:\Users\me\AppData\Local\Temp\" - this is correct
strFieldName = "EEPROM" - this is correct, EEPROM field is an attachment type

I'm not so sure what you mean by this:

Also, double check and make sure you're not sending "currentdb" for the rstCurrent recordset function parameter, without linking all appropriate tables.

Also, is there an End If missing in the function that I am using or is it just me??

 

[sirhannick]

I also tried:
strFilePath = strTempDir & rstCurrent.Fields("strFieldName").Value
and got Run-time error '3265': Item not found in collection.

 

[sirhannick]

Now I can post links!
Here's the original source for the code:
http://blogs.office.com/b/microsoft...-in-the-new-access-2007-attachment-field.aspx

 

[sirhannick]

It appears some other people have struggled with this code, but no solutions that I have found:
http://www.utteraccess.com/forum/Code-handle-attachment-t-t1954563.html
http://www.tek-tips.com/viewthread.cfm?qid=1703840
Any ideas?

 

[sirhannick]

I have solved the issue. It is a bit odd though that this occurred only after splitting the database. I had to add:

DoCmd.RunCommand acCmdSaveRecord

Other than that nothing was wrong with my original code. For some reason the code would not see the newly attached text file unless I saved the record before executing my VBA. It makes sense of course in retrospect, but why it wasn't required before splitting the database it odd. Perhaps this should be considered good etiquette before DAO.Recordset operations?