none
Error 3828 Work around RRS feed

  • Question

  • I use the transfer spreadsheet method to create many spreadsheets from my Access 2010 database.  These procedures worked just fine until I added an Attachment field to one of the tables. Now, I get this "Error 3828: Cannot reference a table with a multi-valued field using an IN clause that refers to another database."

    Here is the line of code that sets it off:

    DoCmd.TransferSpreadsheet acExport, 10, "qryMORUpdatedwithRespExecsmktbl2", strWorksheetPath, True, ""

    Here is the SQL of the query:

    SELECT ([tblfindings].[RPM ID] & " " & [tblfindings].[Concern]) AS [RPM ID/Concern], tblfindings.[RPM ID] AS [RPM ID#], tblfindings.Concern, tblfindings.[Draft Distribution List] AS [Bus Unit], QryExamExp.[Exam SourceID] AS Agency, QryExamExp.[Exam Name], tblfindings.[Finding Name], tblfindings.Finding, QryExamExp.[SL Letter Received] AS [Issue Date], QryExamExp.[SL #], tblfindings.[Finding Typelst] AS Type, Trim([tblfindings].[Responsible Executive]) & ("/"+[Responsible Executive 2]) & ("/ "+[Responsible Executive 3]) & ("/ "+[Responsible Executive 4]) & ("/ "+[Responsible Executive5]) AS [Executive(s)], tblfindings.[Responsible Executive], tblfindings.[Responsible Executive 2], tblfindings.[Responsible Executive 3], tblfindings.[Responsible Executive 4], tblfindings.[Responsible Executive5], Trim([tblfindings].[Exam Coordinator]) & ("/"+[Exam Coordinator 2]) & ("/ "+[Exam Coordinator 3]) & ("/ "+[Exam Coordinator 4]) & ("/ "+[Exam Coordinator5]) AS Owner, tblfindings.[Exam Coordinator], tblfindings.[Exam Coordinator 2], tblfindings.[Exam Coordinator 3], tblfindings.[Exam Coordinator 4], tblfindings.[Exam Coordinator5], tblfindings.CPGDueDate AS [CPG Due Date], tblfindings.[Planned Completion date], tblfindings.[Revised Completion  Date], tblfindings.[Is Updated?] AS [Status Color], tblChalCommitteeResults.[CC Results], tblChalCommitteeResults.[Date Validated by IA] AS [IA Date], tblfindings.Update, tblChalCommitteeResults.[Regulator Status], tblChalCommitteeResults.[2ndStatus], tblChalCommitteeResults.[2ndCCResults], tblChalCommitteeResults.[Critical MRA], tblChalCommitteeResults.[2ndIAValidation], tblChalCommitteeResults.CAPPIP, tblChalCommitteeResults.Model, tblfindings.HasMultConcerns AS [Multiple Concerns], tblfindings.Category
    FROM (tblRPMKey RIGHT JOIN (tblfindings INNER JOIN tblChalCommitteeResults ON (tblfindings.[RPM ID] = tblChalCommitteeResults.[RPM ID]) AND (tblfindings.Concern = tblChalCommitteeResults.Concern)) ON tblRPMKey.[RPMID#] = tblfindings.[RPM ID]) LEFT JOIN QryExamExp ON tblRPMKey.[ExamRecord#] = QryExamExp.[Exam Record Number]
    WHERE (((tblfindings.[Responsible Executive])=[Forms]![frmExecLkUpIIBUNewMORMktbl]![Combo13]) AND ((tblfindings.[Finding Open?])=True) AND ((QryExamExp.[Exam Type])="Regulatory" Or (QryExamExp.[Exam Type])="TDS")) OR (((tblfindings.[Responsible Executive 2])=[Forms]![frmExecLkUpIIBUNewMORMktbl]![Combo13]) AND ((tblfindings.[Finding Open?])=True) AND ((QryExamExp.[Exam Type])="Regulatory" Or (QryExamExp.[Exam Type])="TDS")) OR (((tblfindings.[Responsible Executive 3])=[Forms]![frmExecLkUpIIBUNewMORMktbl]![Combo13]) AND ((tblfindings.[Finding Open?])=True) AND ((QryExamExp.[Exam Type])="Regulatory" Or (QryExamExp.[Exam Type])="TDS")) OR (((tblfindings.[Responsible Executive 4])=[Forms]![frmExecLkUpIIBUNewMORMktbl]![Combo13]) AND ((tblfindings.[Finding Open?])=True) AND ((QryExamExp.[Exam Type])="Regulatory" Or (QryExamExp.[Exam Type])="TDS")) OR (((tblfindings.[Responsible Executive5])=[Forms]![frmExecLkUpIIBUNewMORMktbl]![Combo13]) AND ((tblfindings.[Finding Open?])=True) AND ((QryExamExp.[Exam Type])="Regulatory" Or (QryExamExp.[Exam Type])="TDS")) OR (((tblfindings.[Draft Distribution List])=[Forms]![frmExecLkUpIIBUNewMORMktbl]![Combo9]) AND ((tblfindings.[Finding Open?])=True) AND ((QryExamExp.[Exam Type])="Regulatory" Or (QryExamExp.[Exam Type])="TDS"))
    ORDER BY IIf([Draft Distribution List]=[Forms]![frmExecLkUpIIBUNewMORMktbl]![Combo9],0,1), tblChalCommitteeResults.[RPM ID], tblfindings.[Draft Distribution List] DESC;

    I've read many postings, but have not found a solution.  Any thoughts or ideas?


    Dean J. Waring

    Friday, March 17, 2017 3:58 PM

All replies

  • Is the Attachment column in your query? AFAIK, Excel doesn't have an equivalent to Attachment fields.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Saturday, March 18, 2017 1:56 PM
  • You can try creating a Query that does not include that attachment field, and use that in your SQL above instead of the table. As Paul said, you cannot output an Attachment field to Excel, since it has not idea what to do with it. Attachment fields are specific to the ACE format only - no other database engine (or spreadsheet engine) would recognize them.


    -- Scott McDaniel, Microsoft Access MVP

    Saturday, March 18, 2017 3:02 PM
  • Hi DeanJW2006,

    As per the documentation below , "Graphical elements (such as logos, contents of OLE object fields, and attachments that are part of the source data) are not exported. Add them to the worksheet manually after you complete the export operation."

    Reference:

    Export data to Excel

    this documentation is for manual approach , same thing will be applied for the codding approach.

    so it is now cleared that you cannot export attachment field to Excel file.

    so as a workaround you can try to use VBA code to loop through all the attachment field in table and save them in folder.

    below is an example of that.

    Function AttachmentSave()
    im rs As Recordset
    Set rs = CurrentDb.OpenRecordset("Your_Query", dbOpenSnapshot) 
    FilePath =rs.Filename
    With rs
     .MoveFirst
     Set RSAttachments = rs.Fields("Attachment").Value
     If (FileExists(FilePath)) Then
     Kill FilePath ‘deletes an old one if it exists
     End If
     RSAttachments.Fields("FileData").SaveToFile FilePath
    
    End With
     RSAttachments.Close
     rs.Close: Set rs = Nothing
    End Function

    Reference:

    Export Attachments To Folder

    you can modify the code above and try to save the path / link of attachment file to the Excel Sheet.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, March 20, 2017 6:24 AM
    Moderator
  • The actual attachment field is not in the query itself.  It is in one of the tables.  This is why I'm a little perplexed why it is happening.

    Dean J. Waring

    Tuesday, March 21, 2017 11:59 AM
  • The attachment field is not in the query.

    Dean J. Waring

    Tuesday, March 21, 2017 12:01 PM
  • Hi DeanJW2006,

    from your last reply , I understand that you try to add the attachment field in table but did not add that field in query.

    it means that the result of the query is as it is as it was before.

    I try to test the same thing on my side with dummy table contains attachment field.

    I find that , when I did not include the attachment field in the query then the data transfer correctly.

    when I include attachment field in the query , I got error below.

    so I suggest you to try to check the output of your query again and check whether there is some change in output or not.

    if possible try to post the result of your query here with dummy data so that we can try to have a look.

    it is possible that maybe you change something else in your query and you get this error.

    I try to check your query.

    SELECT ( [tblfindings].[rpm id] & "" & [tblfindings].[concern] ) 
           AS 
           [RPM ID/Concern], 
           tblfindings.[rpm id] 
           AS [RPM ID#], 
           tblfindings.concern, 
           tblfindings.[draft distribution list] 
           AS [Bus Unit], 
           qryexamexp.[exam sourceid] 
           AS Agency, 
           qryexamexp.[exam name], 
           tblfindings.[finding name], 
           tblfindings.finding, 
           qryexamexp.[sl letter received] 
           AS [Issue Date], 
           qryexamexp.[sl #], 
           tblfindings.[finding typelst] 
           AS Type, 
           Trim([tblfindings].[responsible executive]) & 
           ( "/" + [responsible executive 2] ) & 
           ( "/" + [responsible executive 3] ) & ( 
           "/" + [responsible executive 4] ) & ( "/" + [responsible executive5] ) 
           AS 
           [Executive(s)], 
           tblfindings.[responsible executive], 
           tblfindings.[responsible executive 2], 
           tblfindings.[responsible executive 3], 
           tblfindings.[responsible executive 4], 
           tblfindings.[responsible executive5], 
           Trim([tblfindings].[exam coordinator]) & ( "/" + [exam coordinator 2] ) & 
           ( "/" 
           + [exam coordinator 3] ) & ( "/" + [exam coordinator 4] ) & ( "/" + 
           [exam coordinator5] ) 
           AS Owner, 
           tblfindings.[exam coordinator], 
           tblfindings.[exam coordinator 2], 
           tblfindings.[exam coordinator 3], 
           tblfindings.[exam coordinator 4], 
           tblfindings.[exam coordinator5], 
           tblfindings.cpgduedate 
           AS [CPG Due Date], 
           tblfindings.[planned completion date], 
           tblfindings.[revised completion  date], 
           tblfindings.[is updated?] 
           AS [Status Color], 
           tblchalcommitteeresults.[cc results], 
           tblchalcommitteeresults.[date validated by ia] 
           AS [IA Date], 
           tblfindings.update, 
           tblchalcommitteeresults.[regulator status], 
           tblchalcommitteeresults.[2ndstatus], 
           tblchalcommitteeresults.[2ndccresults], 
           tblchalcommitteeresults.[critical mra], 
           tblchalcommitteeresults.[2ndiavalidation], 
           tblchalcommitteeresults.cappip, 
           tblchalcommitteeresults.model, 
           tblfindings.hasmultconcerns 
           AS [Multiple Concerns], 
           tblfindings.category 
    FROM   (tblrpmkey 
            RIGHT JOIN (tblfindings 
                        INNER JOIN tblchalcommitteeresults 
                                ON ( tblfindings.[rpm id] = 
                                   tblchalcommitteeresults.[rpm id] ) 
                                   AND ( tblfindings.concern = 
                                         tblchalcommitteeresults.concern )) 
                    ON tblrpmkey.[rpmid#] = tblfindings.[rpm id]) 
           LEFT JOIN qryexamexp 
                  ON tblrpmkey.[examrecord#] = qryexamexp.[exam record number] 
    WHERE  ( ( ( tblfindings.[responsible executive] ) = 
                        [forms] ! [frmexeclkupiibunewmormktbl] ! [combo13] ) 
             AND ( ( tblfindings.[finding open?] ) = true ) 
             AND ( ( qryexamexp.[exam type] ) = "regulatory" 
                    OR ( qryexamexp.[exam type] ) = "tds" ) ) 
            OR ( ( ( tblfindings.[responsible executive 2] ) = 
                         [forms] ! [frmexeclkupiibunewmormktbl] ! [combo13] ) 
                 AND ( ( tblfindings.[finding open?] ) = true ) 
                 AND ( ( qryexamexp.[exam type] ) = "regulatory" 
                        OR ( qryexamexp.[exam type] ) = "tds" ) ) 
            OR ( ( ( tblfindings.[responsible executive 3] ) = 
                         [forms] ! [frmexeclkupiibunewmormktbl] ! [combo13] ) 
                 AND ( ( tblfindings.[finding open?] ) = true ) 
                 AND ( ( qryexamexp.[exam type] ) = "regulatory" 
                        OR ( qryexamexp.[exam type] ) = "tds" ) ) 
            OR ( ( ( tblfindings.[responsible executive 4] ) = 
                         [forms] ! [frmexeclkupiibunewmormktbl] ! [combo13] ) 
                 AND ( ( tblfindings.[finding open?] ) = true ) 
                 AND ( ( qryexamexp.[exam type] ) = "regulatory" 
                        OR ( qryexamexp.[exam type] ) = "tds" ) ) 
            OR ( ( ( tblfindings.[responsible executive5] ) = 
                         [forms] ! [frmexeclkupiibunewmormktbl] ! [combo13] ) 
                 AND ( ( tblfindings.[finding open?] ) = true ) 
                 AND ( ( qryexamexp.[exam type] ) = "regulatory" 
                        OR ( qryexamexp.[exam type] ) = "tds" ) ) 
            OR ( ( ( tblfindings.[draft distribution list] ) = 
                         [forms] ! [frmexeclkupiibunewmormktbl] ! [combo9] ) 
                 AND ( ( tblfindings.[finding open?] ) = true ) 
                 AND ( ( qryexamexp.[exam type] ) = "regulatory" 
                        OR ( qryexamexp.[exam type] ) = "tds" ) ) 
    ORDER  BY Iif([draft distribution list] = [forms] ! [frmexeclkupiibunewmormktbl] 
                                              ! 
                                                        [combo9], 0, 1), 
              tblchalcommitteeresults.[rpm id], 
              tblfindings.[draft distribution list] DESC; 
    

    I find that it is a complex query contains several tables.

    please try to check the all the tables and check whether you make any change in that or not may solve your issue.

    check all the values from all the references and controls get fetched successfully.

    if we see the error then it says that it not able to reference the multivalue field using in clause from another database.

    I did not find any in clause in your query, are you trying to reference a table in other database?

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 22, 2017 1:33 AM
    Moderator
  • Let's see if this helps at all. I copied the above SQL to a New qry and called it Query4. I then ran "frmExecLKUpIIBUNewMORMktbl!Combo13 and populated the field "Combo13". I left the form open and manually ran Query4.  It ran perfectly.  Next, I stepped through the original code only to find this as the error message again:

    Error No: 3828;Description:  Cannot reference a table with a multi-valued field using an IN clause that refers to another database.

    I then stepped through the code and the offending line was:  DoCmd.TransferSpreadsheet acExport, 10, "query4", strWorksheetPath, True, ""

    I went back and re-ran the query manually with no issues and then manually exported those  results to Excel with no problems.  it appears that the issue is with the transferspreadsheet method.


    Dean J. Waring

    Wednesday, March 22, 2017 1:36 PM
  • Hi DeanJW2006,

    I have question, did you modify this field in query? or it is also there before you make some changes in that table.

    if it is there then query was working fine with this combo13 field and able to reference the value.

    so I think that you need to check something else like combo9.

    also I want to suggest you , just for testing purpose try to remove that attachment field from the table and try to run this query.

    if query again start working then we can say that it's because of that attachment field, but if it still give error then it possible that attachment field is not the cause for this error.

    I still have doubt that attachment field is not the reason for this error.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 23, 2017 7:07 AM
    Moderator
  • Attachment fields (and multi-value fields) are Access-specific and as mentioned in several earlier comments, are not compatible with other products. 

    Most developers will store the path to the file in a text field rather than storing the file itself as an attachment.  A path in a text field can readily be exported and used by Excel, upsized to SQL Server etc..


    Miriam Bizup Access MVP




    • Edited by mbizup MVP Thursday, March 23, 2017 9:57 AM
    Thursday, March 23, 2017 9:50 AM
  • I am not using the attachment field in the query itself.  The attachment field is in tblExam which is one of the tables used in the query, but the attachment field is not part of the query.  I am not trying to export attachments.

    Dean J. Waring

    Thursday, March 23, 2017 8:36 PM
  • When I replace combo9 and 13 with actual choices, everything runs perfectly well. My big problem here is that I have 15 different combo choices that I run each month and the ability to use one query is convenient and efficient. It used to work perfectly well, for years, with the two combo fields as lookups.  The only difference is that an attachment field was added to one of the tables recently.  The attachment field is NOT part of the query being executed.  I am NOT trying to export attachments.

    Dean J. Waring

    Thursday, March 23, 2017 8:41 PM
  • Hi DeanJW2006,

    did you try to implement the suggestion that I gave you last time?

    I suggest you to rollback the changes of adding attachment field for testing purpose and try to run the query and let me know about the results.

    so that we can say that it is because of that attachment field.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 24, 2017 6:31 AM
    Moderator
  • As it turns out, my test database does not have any attachment fields.  As in the past, the procedure runs without a hitch.

    Dean J. Waring

    Friday, March 24, 2017 5:09 PM
  • Hi DeanJW2006,

    it's strange for me, I did not seen this issue before and also not able to reproduce on my side.

    another thing I notice that you are trying to use the numbers for the fields in query.

    are those exact the same numbers as in table? or something get changed when you add a new field in table?

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 27, 2017 8:37 AM
    Moderator
  • Do you mean Exec..... 2, Exec.... 3 ...Coordinator...2 , 3, etc?  These are all fields in the table.

    Dean J. Waring

    Tuesday, March 28, 2017 1:16 PM
  • Hi DeanJW2006,

    if possible then try to post your database.

    we will try to look in to that and try to test your query.

    if any solution is available then we will try to provide you to solve the issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 29, 2017 5:34 AM
    Moderator
  • This will take some time, so please have patience. I will need to make a miniature version to post.  Once again, many thanks for your time and efforts.

    Dean J. Waring

    Wednesday, March 29, 2017 8:40 PM