Results 1 to 6 of 6
  1. #1
    Middlemarch is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Mar 2015
    Posts
    340

    How to display a Select query from a macro

    I have built an sql string and it works fine if I paste it into the query designer.



    But if I try

    Code:
    DoCmd.OpenQuery (sql)
    This brings up Run-time error '7874':
    Microsoft Office Access can't find the object .. and my query is shown as text.

    Thanks for any solution.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You don't use SQL, save the SQL to a query,then....
    docmd.openQuery myQry

  3. #3
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    along with R256 input; you did dim MyQry (or SQL in your original example) as a string? not sure if that is taken for granted or not....

    having a senior moment and can't remember if SQL is a reserved term.... MyQry would be safer....

    I believe using VBA one needs quotes: docmd.openQuery "myQry" have fuzzy memory lapse at the moment though..... but if you are using a Macro as the title of your post implies then you'll be offered the choice of queries that reside in the navigation pane.....

  4. #4
    Middlemarch is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Mar 2015
    Posts
    340
    Right, I have figured out something.. guess you must have an existing query. no other way ? Seems "sql" is ok (I have use it lots)

    Code:
    Dim sql As String, MyQuery As QueryDef
    
    sql = some sql
    
    DoCmd.Close acQuery, "qryShow"
    Set MyQuery = CurrentDb.QueryDefs("qryShow")
    MyQuery.sql = sql
    DoCmd.OpenQuery "qryShow", acViewNormal, acEdit
    Set MyQuery = Nothing
    Is there any way to custom size of the query ?

  5. #5
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    well your post stated - macro ; so that's a defined object concept in Microsoft and the Docmd for Open Query is a defined object concept. I myself prefer working with defined query objects available in the navigation pane because I find it easier to debug. One can run pure sql vba using the RunSQL method.... plenty of online examples of that.....

  6. #6
    Middlemarch is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Mar 2015
    Posts
    340
    Not sure what you're getting at, but I have a solution now. Although not for a constant size/format each time the query opens. Using Access 2003 here - much prefer that to the navigation
    pane/ribbon where you spend more time looking for stuff than getting anything done !

Please reply to this thread with any new information or opinions.

Similar Threads

  1. combobox to drive select macro
    By xopherira in forum Macros
    Replies: 12
    Last Post: 07-30-2015, 12:57 PM
  2. Replies: 8
    Last Post: 06-23-2014, 12:13 PM
  3. Macro code: Automate Select Query Sequence
    By Ace2014 in forum Modules
    Replies: 2
    Last Post: 06-13-2014, 09:21 AM
  4. Select a record and display an image
    By Chris G in forum Forms
    Replies: 5
    Last Post: 11-01-2012, 04:38 PM
  5. Select a field in a row and display data
    By Darkladymelz in forum Reports
    Replies: 15
    Last Post: 03-13-2012, 03:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums