SQL and Embedded Quotes - MS Access

Embedded single quotes occur in proper names, such as O’Reilly, and in other parts of text, and it is only once you start to manipulate SQL in code that these names prove to be a serious problem and can cause code to fail. The problem is rooted in the WHERE clause, and how this clause is constructed to handle a text string comparison in which you need to have embedded single quotes surrounding the text value. When you have an additional embedded quote in the criteria, the quotation marks then become unbalanced (for every open quote, there should be a close quote).

There are two ways to solve this problem. Either you need to replace a single embedded quote with two embedded quotes, or you need to use double quotes as delimiters, as shown in the following code (the results are presented in Figure ):

Searching for embedded quotes in strings.

Searching for embedded quotes in strings.

In the preceding example, the first attempt to handle the embedded quote causes the program to fail. (For the purposes of demonstration, we have added an On Error Resume Next, to prevent this from stopping the code execution) The second part of the code, which uses a replace function to replace a single quote with two single quotes, is a solution to this problem.

The third solution—using outer double quotes—works, but it will fail if it encounters text containing an embedded double quote. In this case, you need to replace an embeddeddouble quote with two double quotes.

Therefore, the rule is that if you delimit with single quotes, double up any embedded single quotes, and if you delimit with double quotes, then double up any embedded double quotes.

DFirst and DLast functions can give inconsistent results after compacting a database

DFirst and DLast can in certain circumstances have an unusual side effect; after you compact and repair a database, you can get a different answer when using these functions (DMin and DMax do not have this problem). Figure shows a new date value entered in the table.

Entering a new date value record to the end of the table.

The table in Figure defines the ADate field as the primary key. After typing in a new record DLast, it will give the last entered record. Figure shows the value returned in the Immediate window.

DLast returning the last record.

DLast returning the last record.

If you close and re-open the table, you see the records re-ordered, as shown shown in Figure , but the value given by DLast is unchanged (because it represents the last record added).

Records are now re-ordered as the date field is part of the primary key.

Records are now re-ordered as the date field is part of the primary key.

However, after we compact and repair the database, the value for DLast will then be changed (because a side effect of compaction is to re-order the physical rows by primary key), the result of which is shown in Figure, where DLast now returns a different value.

Following compaction, the value that DLast returns will now be different.

Following compaction, the value that DLast returns will now be different.

A database function that can return different results depending on whether the database has been compacted could cause problems in your program code.

Using VBA Functions in Queries

One advantage that functions have over subroutines is that they can be incorporated directly into the Query grid. This means that you can directly call both custom and built-in functions from the Query grid in expressions to create columns, and in Where clauses to filter data.

As demonstrated in the code example that follows, when using a function in an expression on the Query grid, it will be called for every record returned in the grid; as long as the function is not too complicated, acceptable performance should be achieved:

When designing this function, we chose to define the parameters to be of a Variant data type to allow for the possibility of null values in the fields (this is often a very important point to consider). The function is shown in Figure, used as an expression on the Query grid. Suitable VBA functions can also be used in criteria on the grid.

Adding a call to a custom VBA function on the Query grid.

Adding a call to a custom VBA function on the Query grid.

Another circumstance for which this approach can be effective is when you have very complex expressions using nested IIF functions; if the logic of the nested IIF functions is difficult to follow, the functions can be simplified by using VBA conditional logic.

The Eval Function

The Eval function allows you to build VBA expressions dynamically at runtime and then execute the expression, which can return a string or numeric (Eval can also return a date data type):

Shell and Sendkeys

Use the Shell command to start an external program asynchronously (control might return to your program before the program you start has finished execution).

With the SendKeys action, you can send a series of keystrokes to the active window. SendKeys can be used in conjunction with the Shell command to send keystrokes to an external application, as shown in the following example:

This second example starts a copy of Access, which opens a database with the Decompile command switch:

The DoEvents Command

The DoEvents command instructs the operating system to process other events and process any keystrokes generated by the SendKeys action before then returning execution to your program. This command is useful when you want to ensure that time is being allowed for repainting and updating information being displayed to the user, and to allow other applications time to undertake processing operations.

To see how this works, open the frmDoEvents form, which displays a status message text box and has a button to update the status messages. If you click the button, you will see the status message “Phase 1 completed” displayed. Now if you comment out the DoEvents statements, when you click the button, you only see the last message displayed; with the DoEvents command enabled, you see the status message change as the processing progresses:

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

MS Access Topics