none
MS Access 2010 Run-Time Error '3828' when Exporting to CSV from Query with (Form Fields) Parameters

    Question

  • Hi MS Access Experts,

    We have a small Access DB, which was upgraded Access from 2007 to 2010. Everything seemed to work ok except for when attempting to export data from a query we started getting 3828 error: "Cannot reference a table with a multi-valued field using and IN clause that refers to another database". The query uses 2 parameters, which are passed from a form (and worked fine in 2007). I tried removing the parameters and the export works fine (with hard-coded values). I also tried making query-level parameters but get the same error (3828), which led me to believe the issue is whith passing the parameters when exporting in 2010.

    The command used for the export is: DoCmd.TransferText acExportDelim, "Custom_Specs", "Qry_Name", "\\Share_Name\Export.csv", False

    I can rewrite the code to use static table by pre-filling it with results, however I would like to be able to find the proper way to use parameterized queries for exporting data in Access 2010.

    Will be happy to provide any further info if required and any assitance in resolving this issue will be highly appreciated!

    PS: using MS Access 2010 on Win XP Pro (SP3)

    Regards,

    Penu

     

    • Edited by Penu_Work Sunday, February 27, 2011 8:00 PM add info
    Sunday, February 27, 2011 7:56 PM

All replies

  • Hi Penu,

    if you are using 

    MyTable IN 'C:\MyDB.mdb'

    then try to use this one instead:

    [C:\MyDB.mdb].MyTable

    Could you also provide an SQL code of your query? Although it worked with A2007, there won't be obvious reason I suppose, but maybe some workarounds would be possible.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Sunday, February 27, 2011 8:20 PM
  • Hi Andrey,

    Thank you for the suggestion however I don't use it in my query. I'm attaching a sample of the query which returns the error:

    PARAMETERS [Forms]![MyForm]![Month] Short, [Forms]![MyForm]![Year] Short;
    SELECT [MyTable].[CompanyID], [MyTable].[Total]
    FROM [MyTable]
    WHERE Month([MyDate])=Int([Forms]![MyForm]![Month]) AND Year([MyDate])=Int([Forms]![MyForm]![Year]);

    Also the Access 2010 file ext is now "accdb" so I guess you are suggesting to do something like:

    PARAMETERS [C:\Mydb.accdb]![Forms]![MyForm]![Month] Short, [C:\Mydb.accdb]![Forms]![MyForm]![Year] Short;
    (I tried and it does not like it...)

    Looking forward any further info/suggestions!

    Thanks in advance,

    Penu

     

     

     

    Monday, February 28, 2011 2:06 PM
  • Hm,

    it's interesting that it worked for you in A2003. I couldn't make it working with A2003. It easy refers to a table in an external db but not to a form.

    So, you provide a sample where there are neither IN clause nor referring to another db. Is it right? Do you really see the error that has nothing in common with your case?

    I can only guess that if the query runs fine "AS-IS" you provided it above

    PARAMETERS [Forms]![MyForm]![Month] Short, [Forms]![MyForm]![Year] Short;
    SELECT [MyTable].[CompanyID], [MyTable].[Total]
    FROM [MyTable]
    WHERE Month([MyDate])=Int([Forms]![MyForm]![Month]) AND Year([MyDate])=Int([Forms]![MyForm]![Year]);
    
    

    then maybe there is something with these annoying trusted locations.

     

     


    Andrey V Artemyev | Saint-Petersburg, Russia
    Monday, February 28, 2011 3:07 PM
  • Yes, you are correct, there are no IN clauses, no cross DB references and the query works without the parameters perfectly.

    I actually have not tried it in A2003, we did A2007 (worked fine there) and then converted it to A2010 and it stopped working...

    I really think the issue is with A2010 not able to handle the parameterized query properly.. (I even tried it with local query parameters, and not the form ones - still the same error)

    Any thoughts?

    Monday, February 28, 2011 3:51 PM
  • Frankly, I can't see any sense in PARAMETERS clause in this case.

    Does it work with just this?

    SELECT [MyTable].[CompanyID], [MyTable].[Total]
    FROM [MyTable]
    WHERE Month([MyDate])=Int([Forms]![MyForm]![Month]) AND Year([MyDate])=Int([Forms]![MyForm]![Year]);
    
    
    And even Int() could be ommited, I think.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Monday, February 28, 2011 4:25 PM
  • Are you exporting the query via the user interface?  If so try calling the TransferText method in code.  The following function works (in Access 2007 at least) when a query which references a control as a parameter is passed into it, regardless of whether the parameters are declared or not:

    Public Function ExportToDelim(strQuery As String, strExportTo As String, Optional blnHasFieldnames = True)

        DoCmd.TransferText acExportDelim, _
            TableName:=strQuery, _
            FileName:=strExportTo, _
            HasFieldNames:=blnHasFieldnames

    End Function

    If all else fails an alternative would be to build the SQL statement in the code, concatenating the values of the controls into the string rather than referencing the controls as parameters; then assign the string expression to the SQL property of the querydef object before exporting it.


    Ken Sheridan, Stafford, England
    Monday, February 28, 2011 5:01 PM
  • @Andrey: I tried removing params and Int and still the same error...

    @Ken: yes, I'm calling it from the code (could try to see if it will give the error from the UI though)..

    The command used for the export is: DoCmd.TransferText acExportDelim, "Custom_Specs", "Qry_Name", "\\Share_Name\Export.csv", False (I provided some mroe details in my original post above)

    It was working just fine in Access 2007 but it stopped in Access 2010...

    ...any ideas?

     

    Monday, February 28, 2011 6:05 PM
  • All I can suggest is concatenating the values of the controls into the SQL statement as a string expression and assigning that to the querydef object's SQL property before you export it.  You won't need any parameters in the query then.


    Ken Sheridan, Stafford, England
    Monday, February 28, 2011 9:43 PM
  • "\\Share_Name\Export.csv" - is it a newtork shared folder? I'll try to reproduce it tomorrow. Since there are no differences between having PARAM clause and not etc., the error could arise anywhere.
    Andrey V Artemyev | Saint-Petersburg, Russia
    Monday, February 28, 2011 9:51 PM
  • You also converted it to an accdb format.  Does the mdb format work in Access 2010?

    The accdb format supports multi-value fields and attachment fields -- both of which can wreck havoc with queries that worked fine before.  For example, adding an attachment field to a table will cause a delete that uses an inner join to threw an error.

    You are probably right that Access 2010 is not able to handle the parameterized query and is probably interpreting the form references as an external database references.

    Tuesday, March 01, 2011 4:08 AM
  • @saberman:

    yes, I'm inclined to agree - I guess for now we will have to change it to static working table (and/or query) which is populated by a SQL statement...

    Again, if someone has come across this issue and resolved it  - I would love to hear it!

    Friday, March 04, 2011 9:48 PM