通过

Let Access do the work of writing SQL statements for you

  • 项目

One of the views associated with creating queries in the Query designer in Access is the SQL window. The SQL window displays SQL statements that are created behind the scenes when you create a query. However, the SQL window doesn't display just SQL statements. Once you see how it works with the Access Query Design window, you'll find that this window can help you in other ways. For example, you can use the SQL window to create SQL statements that you can then paste into Visual Basic for Applications (VBA) modules. Likewise, you can take SQL statements from VBA procedures and use the SQL window to troubleshoot them for you. Because the SQL windows is designed to work with SQL statements, it provides much more meaningful error messages than the generic messages that come from the VBE.

The query design window is a great visual way to create SQL queries that you can then copy and paste into your VBA procedures. You can use the visual aspect of the query design window to easily create your SQL statement. For example, you add the tables to the design surface of the query design window, drag the fields from the table(s) to the query grid, specify any filtering and sort criteria, execute the query, perform any troubleshooting needed, open the SQL window, and there you have a working SQL statement.

Just as a refresher, SQL statements are made up of the following components:

  |Keyword - Instructs the query to retrieve data|
         |*************Field List**************|
  SELECT Employees.[FirstName], Employees.[LastName]
  FROM Employees |**Source of the data**|
  WHERE Employees.Title = "Sales Representative" |**Filter Criteria**|
  ORDER BY Employees.LastName; (**semicolon is required**|
  |ORDERBY clause specifies the sort order|

#Access uses parentheses to enclose the various parts of the WHERE clause but these are optional.
#Whenever a field is specified you have the option to append the table name, separating the two with a dot.If your query refers to more than one table you must include the table name along with the field name.
#When supplying values in a SQL statement, such as in the query criteria, the data type must be correctly defined by a "qualifier". This is done by enclosing the value between a pair of appropriate characters. For example, single (') or double quotes (") for Strings, pound sign (#) for dates, and no qualifier for numbers.
#Square brackets are only required around field names when they contain spaces but it's good practice to include them.
#Dates in SQL must be written in the US date format (month/day/year).
#It is a good practice to write SQL statement in uppercase so you can distinguish SQL Statement from VBA statements. It's also good practice to write each SQL clause on a separate line.

Once you have a working SQL statement, you can then execute it in VBA. VBA and SQL are totally two different types of languages. VBA is a programming language that you can use to get Access (and other Microsoft Office programs) to do what you want. SQL is a language used exclusively to manipulate the data and structure of a database.
VBA has available a large number of objects, properties, methods, functions and constants to construct the sometimes complex statements used to control the program. SQL uses a limited range of "keywords" combined with information you supply, such as table names, field names, criteria, and sort order.

There are different ways to execute SQL in VBA code. The easiest way is by using the DoCmd object's RunSQL method. Something like this:
 Dim strSQL As String
 strSQL = "... THE SQL STATEMENT GOES HERE ...”
 DoCmd.RunSQL strSQL

The RunSQL method takes two arguments, the SQL Statement itself which must be supplied as a string (i.e. enclosed in quotes) and Use Transaction which is optional and assumes True if you omit it. When transaction processing is applied to a query, Access first performs a "dry run" of the query during which it writes all the changes to a temporary log file but does not make any permanent changes to the database. If the query finishes its task without any problems, the changes noted in the log file are applied and the job (the transaction) is completed. If, however, Access encounters problems while executing the query, the transaction terminates and the log file is discarded without any changes being made. Transaction processing is a very useful safeguard for your data and should always be applied, unless you have a particular reason not to do so.

The SQL window in the Access Query Designer is also the easiest way to debug SQL statements. Most SQL errors are caused by missing keywords. SQL statements in Access are interpreted by the Jet database engine that runs in the background while Access is running. A missing keyword might generate a generic error:
"Syntax error (missing operator) in query expression SELECT...."
Or a more specific error as you narrow down to the faulty part:
"Syntax error in the ORDER BY clause."
If you misspell the name of a field or refer to one that doesn't exist:
"The Microsoft Jet database engine does not recognize LastNme as a valid field name."
Or you might get an 'Enter Parameter Value' dialog box asking you to entry a value for the misspelled name. This type of error almost always refers to a typo in a field name. General steps to troubleshoot this and most types of errors include:
* Open the query in design view and make sure that all references to fields are spelled correctly,
* Check any fields that you recently renamed in the table. This can cause a problem in criteria expressions.
* If you still can't find the problem, open the data source (usually one or more tables or other queries), and check all of the field names, and then recheck your query.

Errors generated by faulty SQL statements in VBA are usually not as helpful or specific as those generated in the Query Designer which is why it's best to create and troubleshoot statements in the Query Designer.

If you want to play around with creating and troubleshooting SQL statements in Access, a good way to do this is to use the Northwind sample database that comes with Access.

Comments