none
Runtime Error 3000 when using Crosstab Query as Recordset RRS feed

  • Question

  • Woring with Access 2007

    I have a table with one set of values, a table with a second set of values, and a table where I associate (on a many-to-many level) values from the two original tables (as look-ups).  This is to give me a pass-through for variables in VBA function arguments. That part works fabulously. It does exactly what I want.

    Additionally, I want to keep a matrix of the values in the many-to-many table, as a sort of quick-reference for which values are and are not associated.  That has been properly achieved by a cross-tab query, where the values from the second set of values are used as column headers, and the values from the first set of values is used as the first field in each record... the fields where these values intersect are "yes" where the value is not null, and "yes" where the value is null, after performing a count  function.  Without the substitution, the fields are blank where null (obviously) and "1" where not null (or would be "2" if there were duplicates, and so on).  This part also works fabulously. 

    My next step is to output the records from this query, in any way possible, to an external file.  I don't even care what the output is at this point, as I could get any of them working if I could get one of them working. But for now, I'm stumped.  I have stripped out every bit of extraneous code, and I am still unable to initialize the cross-tab query as a recordset.

    Here is my code:

    Function TestingRecordset()
    
    Dim dbs As Database
    Dim rst As Recordset
    
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT * FROM qryPermissionsMatrix")
    
    rst.MoveLast
    
    MsgBox ("done")
    
    Set rst = Nothing
    
    End Function

    I have also tried calling out one specific column that will always be the same, and it won't recognize that either. I've tried adding the "dbOpenDynaset." I've changed the name of the query name to another query name and made the message box display the value of a specific field of the last record and it worked without issue.

    I am receiving Run-Time Error 3000, Reserved Error 3087

    I have searched google for literally days now and can't find anything like this.  I have tried all kinds of other methods to achieve what I want to do.  I am even willing to write the data to a temporary table so that I can call that table rather than the cross-tab query in my VBA code... Any suggestions from how to fix this error to a better way to do what I'm trying to do would be greatly appreciated.

    Thanks so much!


    P.S. Please forgive my formatting, I'm not entirely sure what I did wrong at the beginning of this post.

    • Edited by LadyPhoenix Wednesday, December 12, 2012 6:07 PM Attempting to fix formatting
    Wednesday, December 12, 2012 6:03 PM

All replies

  • Not sure about exporting the crosstab, but export the query to Excel and create a pivot table in Excel. That will get you the same result in Excel from where you can export again if needed.

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Thursday, December 13, 2012 12:07 AM
  • I am able to export the crosstab query to excel without issue, I should be able to import it into access again to work with it, but this seems like a rather clunky way to do it. There's got to be a way for me to open this query as a recordset... Maybe I'm missing references I don't know about.

    Will let you know if this works, but hoping this is only a temporary solution. Would still really like to fix the problem rather than finding a resource-heavy work-around.

    Thursday, December 13, 2012 2:53 PM
  • I ran into the same issue trying to to a "SELECT * FROM qry_randomcrosstab" which for some reason always results in a -3087.

    If I remember correctly I used to be able to run a query of this type, but I cannot remember what was different in my setup, nor have I been able to find out anything about the reasons for this behaviour...

    BR, Johannes Engel

    Monday, December 17, 2012 2:04 PM
  • I ran into the same issue trying to to a "SELECT * FROM qry_randomcrosstab" which for some reason always results in a -3087.

    If I remember correctly I used to be able to run a query of this type, but I cannot remember what was different in my setup, nor have I been able to find out anything about the reasons for this behaviour...

    BR, Johannes Engel

    Monday, December 17, 2012 2:04 PM
  • At this point, I can't do anything at all with it except export it to excel like Rod Gill suggested.  The problem with this is, for me to utilize the data in Access (which is what I need to be able to do), I need to then re-import the data into access.  The process of importing a spreadsheet into a table appears to be very restricted.  I have to know exactly how many fields I'm going to have in each record and what those fields will be called in order to be able to import it. Every time I don't define them properly first, it fails.  This means I can create a solution that works for me now, but the second I add another variable (which will happen), the system breaks. I really need to find a less rigid solution to this problem. If I could simply export the crosstab query to a table, or utilize it as a recordset in VBA, this would be a super-simple thing to do.  

    I'll keep looking.  Thanks for your input so far, guys!

    Monday, December 17, 2012 2:11 PM