You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Today’s Power Tip comes from Brandon with OpenGate Software, offering Microsoft Access products for Access users of ever experience level, including UI Builder, Designer, and Dashboard Builder for Microsoft Access.
Here are two tricks in Access to make SQL easier. The background context is that often it makes more sense to put queries in code rather than save them as query objects, particularly if you don’t want users to have access to them, or don’t want to manage hundreds of queries.
Tip #1: Use the Query Design wizard to create your SQL statement. Rather than memorize all the nuance of SQL to write Docmd.RunSQL statements, you can use the Access Query Designer to visually design your most complicated queries, then copy the resulting SQL into your code. Here’s how:
a. Create a new query in Access using the wizard, or just the Query Design view.
b. If you need to replace a portion of the SQL with a variable from code, you can put a placeholder in the query design. As shown below, we’ve put 9999 in the Criteria for CustomerID. We’ll replace that later with a variable in code.
c. Select “SQL” from the list of options at the bottom right of the screen.
d. Now we’ve got our very long and ugly SQL statement. Part of the benefit is we’re selecting on the fields we need, which means Access will run more efficiently if you’re running the query over a network to a remove database on a file or SQL server.
e. Copy the SQL statement and paste into your VB Docmd.RunSQL statement. You’ll receive a number of error messages just because you need to be sure to correctly format the statement. Wherever there is a line break, add the end quote, an underscore, and then a beginning quote for the next line. Note that if you have any criteria with quotes, you’ll need to add another quote. So "Active" would become ""Active"" as shown below.
f. Lastly, if you added a placeholder parameter like we did in step b above, you can now introduce your variable reference. As shown below, we have a parameter lngCustomerID that is passed into the function. In the example, we replaced 9999 with " & lngCustomerID & "
Tip #2: Use the Query Design view to troubleshoot SQL in code. The reverse of #1, I often have times where I’ve written some SQL, and make a change that breaks it. One of the easiest ways to diagnose the problem is to copy the SQL from code, paste it into the Query Designer, and see what may be the problem. For action queries (make table, append, update, delete), change them to Select Queries in the Query Designer to see whether or not your query will actually return any rows, and find the root cause of the problem.
Comments: (13) Collapse
Great tip. One thing I frequently do when including strings instead of double quote (e.) is use the apostrophe. Access recognizes that the same way and makes the code look a little cleaner.
True, but learning SQL is a skill that is far more important than saving a few minutes typing them out. I used the editor to learn SQL, but now I know that I can type SQL faster. Plus, Access has a habit of adding table names. It makes the statement way too long.
My EzySQL Access add-in among many other features automates this process, creating of the formatted SQL string, and the building of variable and form parameters: www.aadconsulting.com/ezysql.html
A few weeks back, I blogged (gpgonaccess.blogspot.com) about my introduction to, and adoption of, this technique a few years ago, while I was working as a sub-contractor for some friends doing Access FE--> SQL Server development. It seems to me to be a very logical way to handle SQL and I now use it in a lot of other circumstances where I want to create "custom" parameterized queries. Among other things, it allows me to create a Master, or Shell, Pass-thru and re-use it, with appropriate, customized, SQL. I'm still looking for the down-side to this technique, although I am sure there are some. George
I'm not sure if it is any less performant, but another way of accomplishing the sql string in code is to first declare a string variable to hold your sql (ie. Dim strSQL as String). Then after pasting all the sql into the code window, you simply type the following in front of the first bit of sql: strSQL = strSQL & ". Take that set of characters (including the quote) and paste it in front of each line in the code and voila, Access puts the closing quote at the end of each line all the way down as you are pasting your snippet at the front of each line. The other benefit of having the SQL in a variable is that when you are debugging, it is easy to see whether the sql is formatted as you expected, because you can hover over the variable name in debug mode, create a watch variable for it, etc.
Great tips. However, you cannot use the designer to write your most complicated queries (at least if you are me). There will come a time when you need to know SQL.
I also prefer to use the apostrophe rather than the double quote for string criteria, however a word of warning... In most instances they make the embedded SQL statement clearer to read, however, if you are searching on a name field, beware! You will get names like O'Brien that will cause your embedded SQL to fall over. In such cases I tend to use: ...WHERE Surname = " & Chr(34) & strSurname & Chr(34) as the other alternative (multiple double quotes) can become harder to read and maintain. NOTE: Chr(34) inserts a " into the string - 34 is the ASCII code for the double quote.
Allen Browne has a useful utility that builds on this idea and sorts out the line breaks and double quotes for you: www.allenbrowne.com/ser-71.html
For Access users and developers, the query designer should be the first way to write queries rather than handcoding the SQL syntax in code. Not only is it easier to create, it's easier to test, and more importantly, for someone else to support. The example provided for justifying why the SQL should be converted to module code is rather weak. If criteria needs to be set with a variable in code, a parameterized query should be used. The query can still be saved as a query rather than converted to a convoluted string in code. An example of this is in my query paper: www.fmsinc.com/.../index.html As for the conversion of SQL syntax to VBA code, the challenges of getting the quotes and wordwrapping correct is significant. The SQL Text/Long Text builder in our Total Visual CodeTools product handles this right within the VBA IDE from any saved Access query: www.fmsinc.com/.../CodeBuilders Hope this helps. Luke
In line with / expanding on Gregory's comment: One additional technique I employ when using the example code:- 1. Set up a string in your VBA that contains the SQL code, including the variables being parsed in.
i.e. strSQL = " ......." 2. Use a QueryDef, pointing to a query object in your mdb (say the initial query design that as been saved to a named query) parse the new string statement to the QueryDef using a with statement ... i.e. Set dbsDb = CurrentDb
Set qdf = dbsDb.QueryDefs("YourQueryName") With qdf .SQL = strSQL .Close End With 3. Just use the DoCmd to exexcute the query This has the advantage of; being able to go look at the query in design mode, maintainable, debug etc, ... and may even execute quicker ? Hope this is helpful, and apologies if a little verbose John L
Watch out when parameterizing dates in code. They have to be mm/dd/yyyy in the SQL string. Is parameterizing a word?
Correct Marco, they need to be in that format. Also, be sure to prefix & suffix with # signs.
Here's the syntax I use: & "#" & "" & Format(Your Date, "mm-dd-yyyy") & "#" Trust this is of some help, John L
John Lockyer, I think that you are wrong... queries run faster if they have firstly been saved and then called from the code than if they have been written inside the code...
Comments: (loading) Collapse