view vba select query

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.
vrosas_03Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
vrosas_03Connect With a Mentor Author Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:


DoCmd.OpenQuery "YourSelectQueryName"

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

mx
0
 
GrahamMandenoConnect With a Mentor Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Connect With a Mentor Commented:
Don't you mean:


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

mx
0
 
GrahamMandenoCommented:
No, I meant create and save the QueryDef, then use it repeatedly, changing the SQL as required.

G.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
vrosas_03Author Commented:
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
 
vrosas_03Author Commented:
its asking me to explain why i accepted my own solution but I don't recall doing that.
0
 
vrosas_03Author Commented:
can I delete my comments?
0
All Courses

From novice to tech pro — start learning today.