How to Quickly Execute SQL Commands Without Building a Query in Microsoft Access
ฝัง
- เผยแพร่เมื่อ 14 ต.ค. 2024
- In this Microsoft Access tutorial I'm going to teach you how to quickly run an SQL statement without having to open the QBE (Query-by-Example) grid designer and build a query.
Jacob from Tacoma, Washington (a Platinum Member) writes: Is there any way to quickly run a query without using the QBE (Query-by-Example) grid designer?
Silver Members and up get access to an Extended Cut of this video. Members will learn how to run SELECT statements to view data. We will create a temporary query on the fly using VBA and something called a QueryDef.
MEMBERS VIDEO:
• Quickly Run SQL Statem...
BECOME A MEMBER:
TH-cam: / @599cd
or My Site: 599cd.com/THMe...
LEARN MORE:
599cd.com/Quic...
PREREQUISITES:
Intro to VBA: 599cd.com/Intr...
SQL with Access: 599cd.com/SQLw...
LINKS:
InputBox: 599cd.com/Inpu...
RECOMMENDED COURSES:
SQL Seminars: 599cd.com/SQL
Access Developer 37: 599cd.com/ACD37 - QueryDefs
FREE TEMPLATE DOWNLOADS:
TechHelp Free Templates: 599cd.com/THFree
Blank Template: 599cd.com/Blank
Contact Management: 599cd.com/Cont...
Order Entry & Invoicing: 599cd.com/Invo...
More Access Templates: 599cd.com/Acce...
ADDITIONAL RESOURCES:
FREE Access Beginner Level 1: 599cd.com/Free1
How to Use Access in 30 Minutes: 599cd.com/30Mi...
$1 Access Level 2: 599cd.com/1Dollar
Donate to my Tip Jar: 599cd.com/TipJar
Get on my Mailing List: 599cd.com/YTML
Contact Me: 599cd.com/Contact
TechHelp: 599cd.com/Tech...
Consulting Help: 599cd.com/DevNet
Twitter: / learningaccess
en.wikipedia.o...
products.offic...
microsoft.com/...
KEYWORDS:
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Docmd.runsql, currentdb.execute, run query, create query, openquery, deleteobject, querydef, createquerydef
QUESTIONS:
Please feel free to post your questions or comments below. Thanks.
Hello Richard, you're the man! I have been waiting for the longest for this particular feature using Access since I am a fond user of SQL. Thought that this would be more complicated than what you explain in your video. Very handy tool to have. Thanks for sharing.
Welcome
2:47" Be Gone - Wrong Peasant" hahahaha 😄
:)
Hi. which is better
Runsql or execute sql
Execute is DB level. Does not show warnings. RunSql is docmd object level, will need to turn warnings on/off for action queries, as desired, unless disabled in settings.
^ What he said. I've got a video coming out on this topic soon.
Hi Mr Ross, how can one become member of your class?
599cd.com/Join
Dear Mr. Richard
I have a problem with form I have built that is driving me crazy
So, I build my apps as single multi-tabbed main form (pop up and modal), and so I hide everything underneath, tables, menu bars, …
I normally handle all the functionality through VBA, and everything was fine until I needed to query my tables and open the results in a DS view
So, basically each time I need to query a different table with a different column count and that is why I need to create a query when I run the SQL, view the results, and then delete it after that
Here are the steps I did:
1. I created a text field to type the syntax of the query I need to run
2. Then, a button that will run SQL for whatever text fin that text field
3. And a button to clear the form and delete the query
4. And a macro, to open (reopen) my main form in normal view
Here is how it works
1. After I type my SQL query in the text field, and hit the button “openDSQuery”, the following code runs:
Private Sub openDSQuery_Click()
On Error GoTo openDSQuery_Click_Err
If Not IsNull(DLookup("Name", "MSysObjects", "Name='QueryAllDB' And Type In (1,4,5,6)")) Then
DoCmd.DeleteObject acQuery, "QueryAllDB"
End If
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim QueryName As String
QueryName = "QueryAllDB"
strSQL = Forms!Main!sqlQuery
Set qdf = CurrentDb.CreateQueryDef(QueryName, strSQL) ' create query
DoCmd.openQuery QueryName ' open query as datasheet
Me.Visible = False ' set my main form invisible
'Here I view the results and work with them , and then as I close the datasheeet ,
'I call mcrReopenPopUp to reopen the main form
Application.Screen.ActiveDatasheet.OnClose = "mcrReopenPopUp"
openDSQuery_Click_Exit:
Exit Sub
openDSQuery_Click_Err:
errorMsg.Visible = True
Me.errorMsg = Error$
Resume openDSQuery_Click_Exit
End Sub
2. As you can see that I am deleting the query before I hit the button. And I have also added a clear button, that run the following code to delete the query
Private Sub clear_Click()
If Not IsNull(DLookup("Name", "MSysObjects", "Name='QueryAllDB' And Type In (1,4,5,6)")) Then
DoCmd.DeleteObject acQuery, "QueryAllDB"
End If
MsgBox "QueryAllDB was deleted successfully"
End sub
3. Everything works fin the first time I execute the SQL for the first time but that very next time, and as long as my main form is open, I can run as many queries as I need and for any table, but what happens when I close the form and reopen it (relaunch the app), I keep getting the following errors:
a. Run time error 7874: Microsoft access cannot find the object “QueryAllDB”
b. I then click on the clear button to delete the query and the msgbox says that it was deleted successfully
c. I then again, and I get the same error message and after than I close the form and check my shutter bar , and I find that query “QueryAllDB” already exists
Appreciate Your help , what am I doing wrong ?
A gentle reminder
599cd.com/ask
hi i got one question i have one single form contacts and i want open specific record for contact parent in singel form how do that i dont want continious form
599cd.com/Ask
After watching this video, I run A couple of action queries by a click of button. But one of them runs by chance. any help.
Do you really think that you've given me enough information to be of any help?