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?
 
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 Access MVP)Database ArchitectCommented:


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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
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 Access MVP)Database ArchitectCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.