Solved

view vba select query

Posted on 2011-09-15
9
631 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
  • 4
  • 3
  • 2
9 Comments
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility


DoCmd.OpenQuery "YourSelectQueryName"

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

mx
0
 

Accepted Solution

by:
vrosas_03 earned 0 total points
Comment Utility
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
Comment Utility
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
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 250 total points
Comment Utility
Don't you mean:


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

mx
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 20

Expert Comment

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

G.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
Comment Utility
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
Comment Utility
its asking me to explain why i accepted my own solution but I don't recall doing that.
0
 

Author Comment

by:vrosas_03
Comment Utility
can I delete my comments?
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

763 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now