Visualize SQL Query Result

[Garcimat]

Hi Guys
Is there a way to see the result of a SQL query on access like we have on the SQL Server ?
for example I have this query

Dim sql As String

sql = "SELECT * FROM tblOrder Where OrderStatus = ""In Progress"""

And I would like to see the result so I can make changes, sorry if it sounded confuse .

 

[arnelgp]

this is a simple demo.
if you like to incorporate to your db, copy form frmSqlEditor and the query, qryQueries
to your project db.

 

[Garcimat]

All credits to this guy....
Access - Visualize Query Results from VBA - Bing video

Found it...

------------ Module
Public Sub ShowdataSheet(ByVal sql As String)

Const Query_Name As String = "qry_dummy_display"
Dim db As DAO.Database
Dim qd As DAO.QueryDef

DropQuery Query_Name

Set db = CurrentDb
Set qd = db.CreateQueryDef(Query_Name, sql)

DoCmd.OpenQuery Query_Name

If MsgBox("Close Query?", vbYesNo) = vbYes Then
DoCmd.Close acQuery, Query_Name, acSaveNo
DropQuery Query_Name
End If

End Sub

Private Sub DropQuery(ByVal queryname As String)
On Error Resume Next
CurrentDb.QueryDefs.Delete queryname
End Sub

-----------Form
Private Sub Command0_Click()

Dim sql As String


sql = "SELECT * FROM tblData Where Section = ""City - West"""

ShowdataSheet sql

End Sub

 

[Garcimat]

this is a simple demo.
if you like to incorporate to your db, copy form frmSqlEditor and the query, qryQueries
to your project db.

Thank You it will be very useful

 

[Garcimat]

@arnelgp I am trying add more conditions on that query for overlapping that you helped me with....

 

[The_Doc_Man]

As I read your original question, you asked this as a contrast/comparison to SQL server. The only contrast I can assume would be native Access via JET or ACE DB engines. However, it is possible that I misread or misunderstood your question. I take it as you desiring to be able to reflect the current status of an active native Access query so that you could do a percent complete display or a progress bar display or something similar.

If in fact you wanted to see incremental query progress on a JET/ACE query, you cannot do that without a LOT of gyrations. Access is designed to be synchronous. You have to play with Win API calls to cause a process "fork" so that you can have a query active AND have a progress monitor running in parallel at the same time. When you trigger a simple query via VBA and it is not through an ODBC style of link, your program doesn't regain control until the query is complete because Access was not originally designed for parallel operations. That parallel operation, however, is what you would need to be able to catch the DB engine "in progress." You CAN trigger suitable Windows calls, but they can become difficult.

Note that if you were using recordset operations, the answer is very different because with recordset activity, you explicitly step to the next record and therefore can create some kind of progress indication as part of the stepping process. But because it would be via VBA, it would inherently be much slower.

Again, if I misread the question, I apologize - particularly since I have to suggest you can't do what you wanted if my interpretation was correct.

 

[isladogs]

@Garcimat
Excellent timing....
I have almost completed something similar which displays all 3 query views (SQL / Design / Datasheet) in one form.
The SQL is editable. The other 2 views are updated as the SQL is changed

Adding the design window was the tricky part.
Many thanks to @CJ_London for suggesting some code from his excellent Access Studio project which I was able to adapt for my needs

It works for all query types tested so far - just make table & passthrough still to test
It should be ready for release within the next week or so, but in the meantime, I'm going to study both your code and that in @arnelgp's example to see if either is simpler / better than what I already have.

Thanks also for the link to Philipp Stiefel's video (@sonic8) which I will watch later. His code is always excellent

Many thanks

 

[CJ_London]

there is also access studio which you can download from here

Access Studio - formatted SQL and more

Attached is a zip file containing 32 and 64bit versions of Access Studio as demonstrated at a number of Access user group meetings over the last few months. It is also available here https://accessusergroups.org/europe/ and also hosted here https://www.isladogs.co.uk/add-ins/index.html The zip...

www.access-programmers.co.uk

 

[Pat Hartman]

For your own purposes, you can do whatever you want.

However, if you are trying to make something that another person would use, opening a naked query is dangerous since you have no way to control what the user does. A better option would be to use a form to display the data so you can validate it before it gets saved or deleted. Use the form's BeforeUpdate event to handle adds and changes and the form's BeforeDelConfirm event to handle deletes.

Even for your own use, you might want to go with a form to prevent errors if possible.

 

[KitaYama]

this is a simple demo.
if you like to incorporate to your db, copy form frmSqlEditor and the query, qryQueries
to your project db.

@arnelgp that was a very nice utility.
I added a button to save the changes back to selected query.
Now I'm trying to find a way to show the result if the query is an action query.

 

[Pat Hartman]

@KitaYama It wasn't arnel that posted the utility, it was @isladogs

 

[KitaYama]

@KitaYama It wasn't arnel that posted the utility, it was @isladogs

@Pat Hartman I was referring to @arnelgp post above (#2).
@isladogs' utility is not published yet. It seems that he's working on pass through queries.

 

[arnelgp]

you can show "how many records" were affected (update query), inserted or deleted.
this can be done using a form that will display appropriate info when the Action query
is run. you then set the subform's SourceObject to this form.

see this demo.

 

[KitaYama]

you can show "how many records" were affected (update query), inserted or deleted.
this can be done using a form that will display appropriate info when the Action query
is run. you then set the subform's SourceObject to this form.

see this demo.

@arnelgp thanks for your effort, but unfortunately it's not what I meant.
I think the whole purpose of that form is to test if a sql is correct or not. Or at least it is how I'm planning to use it.
If each time I try to test an update/insert query the tables are affected, I'm in a mess.

AND

There's always a good chance I make a mistake and select a wrong query from list. Just imagine I want to select a SELECT query from the combo, but I click on a delete query.

For now I added a msgbox to warn an Action query will be run and only Yes button will continue the code.

When you write an update query, you can move between design and datasheet view to check actually which records will be effected.
I hopped it was possible in your demo.


Later this week, when I have enough time, I think I will update your code to add only select queries to the combo.
It's too dangerous to have an action query in that list in a real application.

Anyhow, the idea is still brilliant.
Thanks a lot.

 

[arnelgp]

Later this week, when I have enough time, I think I will update your code to add only select queries to the combo.

for "SELECT" only queries in combo, you will make your combo to use Value/list then add them manually.
you can open the querydef and pass to the function i made to check whether it is a select query or not.

 

[KitaYama]

Thanks. I think I will go with the second method.

 

[arnelgp]

well i experimented creating various queries and checking them in MsysObjects table.
i found out that you can get the query "type" by looking at the Flags field on this table:

MyysObjects "Flags"
0 = SELECT query
16 = Crosstab
32 = DELETE query
48 = UPDATE query
80 = INSERT INTO query

 

[KitaYama]

I think it's faster than looking at sql statement of all queries one by one.
I may add an option box to change the row source and fill the combo with a selected type of query (Select-Delete-Update & Insert)

 

[CJ_London]

i found out that you can get the query "type" by looking at the Flags field on this table:

that list is not complete

Query Type	Visible	Hidden
Append	64	72
Crosstab	16	24
DDL	96	104
Delete	32	40
Make Table	80	88
Pass Through	112	120
Select	0	8
Union	128	136
Update	48	56

Hidden objects are the visible flag+8 (same applies to tables, although not all tables have the hidden property)

 

[KitaYama]

What is DDL?