Solved

view vba select query

Posted on 2011-09-15
9
699 Views
Last Modified: 2012-05-12
I'm using Access and am learning VBA.  I want do a select query in vba preferably using DoCmd.RunSQL.  But I also want to view the results somehow. Preferably as a basic query table in vba.  I don't mean create a table from a query but just show the query in tabuilar form.
0
Comment
Question by:vrosas_03
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 75
ID: 36546264


DoCmd.OpenQuery "YourSelectQueryName"

RunSQL is only for Action queries (Delete, Append, Update, MakeTable)

mx
0
 

Accepted Solution

by:
vrosas_03 earned 0 total points
ID: 36546439
This opens a query created with query designer and doesn't let you alter it.
Private Sub run_query_Click()
DoCmd.OpenQuery "Query1"
End Sub

I want to be able to alter the SQL from VBA
like this:

Private Sub run_query_Click()
DoCmd.OpenQuery "Select * from Table1"
End Sub
0
 
LVL 20

Assisted Solution

by:GrahamMandeno
GrahamMandeno earned 250 total points
ID: 36546454
Create a query and save it.  Then alter its SQL before you open it:
Private Sub run_query_Click()
CurrentDb.QueryDefs("MyDynamicQuery").SQL = "Select * from Table1"
DoCmd.OpenQuery "MyDynamicQuery"
End Sub

Open in new window


Best wishes,
Graham
0
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 250 total points
ID: 36546483
Don't you mean:


  CurrentDb.CreateQueryDef "MyDynamicQuery", "Select * from Table1"
  DoCmd.OpenQuery "MyDynamicQuery"

mx
0
 
LVL 20

Expert Comment

by:GrahamMandeno
ID: 36546515
No, I meant create and save the QueryDef, then use it repeatedly, changing the SQL as required.

G.
0
 
LVL 75
ID: 36546551
Well, I guess if the OP want so create it in VBA .. and save it ... and reuse it ... there is now a way :-)

mx
0
 

Author Comment

by:vrosas_03
ID: 36546888
Thanks a lot.  those solutions will be useful to me in the project I'm working on.     In one you create the table and in the others the table must already exist.  I'm including my own experiments in case anyone might find them useful

'this creates an access query:

Private Sub run_query_Click()
  CurrentDb.CreateQueryDef "MyDynamicQuery", "SELECT * FROM Table1;"
  DoCmd.OpenQuery "MyDynamicQuery"
End Sub


'Alters a query already existing:

Private Sub run_query2_Click()
DoCmd.Close acQuery, "MyDynamicQuery3", acSaveYes
CurrentDb.QueryDefs("MyDynamicQuery3").SQL = "SELECT * FROM Table1;"
DoCmd.OpenQuery "MyDynamicQuery3"
End Sub



'Creates and opens the query this is what I created before I realized you had said to create the table:

Private Sub run_query4_Click()
Dim MyDynamicQuery3 As QueryDef
CurrentDb.CreateQueryDef ("MyDynamicQuery4")
DoCmd.Close acQuery, "MyDynamicQuery4", acSaveYes
CurrentDb.QueryDefs("MyDynamicQuery4").SQL = "SELECT * FROM Table1;"
DoCmd.OpenQuery "MyDynamicQuery4"
End Sub

Open in new window

0
 

Author Closing Comment

by:vrosas_03
ID: 36565544
its asking me to explain why i accepted my own solution but I don't recall doing that.
0
 

Author Comment

by:vrosas_03
ID: 36546958
can I delete my comments?
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question