Data Management

Save time by using WordPad or Notepad for big SQL edits in Access 97

If you or your users use SQL to query Access 97 tables, Peter Nelson has a tip that's guaranteed to help save you time and trouble.

Access developers aren’t born; they have to learn by paying their dues. One time-saving tip that I like to teach students who are on the developer track with Access 97 is to have WordPad or Notepad ready for editing SQL strings. Here’s how it works.

Querying via SQL
Let’s assume that you now perform most of your queries via SQL code rather than by using an actual query object in the database window. Instead of having a query called “delInactiveClientsQ,” you now have the following SQL code behind a form button:
Public Sub PurgeInactiveClients()
'  Desc:  Purges Inactive clients as of a given date. 
'  Client must also have their [cStatus] set to 'I'
'  Author:  PJN
'  Date:  11/12/1999
'  Change log:
'  11/12/99 - Production
On Error GoTo err_PurgeInactiveClients
Dim db As Database, strSQL As String, strDate As String
Set db = CurrentDb
     strDate = InputBox("Please enter the client termination date cut-off point", "Purge Inactive Clients", Format(Now - 365, "mm/dd/yyyy"))
     If strDate = "" Then
         MsgBox "Cancelled Client Purge.", vbInformation + vbOK, "Cancel"
         Resume exit_PurgeInactiveClients
     End If
     strSQL = "DELETE * "
     strSQL = strSQL & "FROM tblClient "
     strSQL = strSQL & "WHERE cStatus='I' and "
     strSQL = strSQL & "cInactiveDate <= #" & CDate(strDate) & "#;"
     db.Execute strSQL
     MsgBox "Done purging clients for " & CDate(strDate) & ".  Have a nice day!", vbOK, "Done purging"
     Exit Sub
     MsgBox Err.Number & "  -  " & Err.Description
     Resume exit_PurgeInactiveClients
End Sub

If you ever need to retest this SQL string, it would be convenient to do it with a good ol’ Access query object rather than more code. With the extra “strSQL = & “ in the code, you can’t just cut and paste it back into an empty query, so here’s what I do:
  1. Copy the SQL string from the code module.
  2. Launch WordPad.
  3. Paste in the code.
  4. Select the text strSQL = strSQL &.
  5. Open Edit. Select Replace.
  6. The Find what prompt will have the selected text and the Replace with: prompt will be blank.
  7. Click the Replace All button.
  8. I then select strSQL & in the Find what box, and press [Ctrl][X] to “cut” what I don’t want from the string. Then I select Replace All again to remove the first part of my string builder.
  9. Type “ (the quotation mark character) in the Find what box and select Replace all to remove all of the double quotes.
  10. Copy and then paste back into the “SQL” view of a new, empty, query.
  11. If, as in this example, you have any type of parameter, you will have to edit that parameter to reflect your test.

The details are worth the time
While this approach may seem like a fair amount of work, it becomes more valuable as your SQL strings become more and more complex.

There are two secondary benefits to this approach. First, you have a place where you can paste the results of a debug window inquiry. Let’s say you type
? strSQL

in the Debug window and it’s a really long string. You won’t be able to see the entire string within the Debug window. You can, however, copy the entire string and then paste it into WordPad so that you can more easily read through the text.

Second, this approach allows you to build a reference document of your embedded SQL strings for documentation or later testing (as long as the SQL doesn’t change.) Simply copy and paste any or all parts of a function and you can refer to this document outside of Access.

Peter Nelson is the principal of NewMarket Technology in Saratoga Springs, NY. NewMarket provides customized software training and applications development to its clients. While the company’s client base includes manufacturing, government, and small business, NewMarket has a specific focus on brokerage and insurance markets. Follow this link to write to Peter or post a comment below.

Editor's Picks

Free Newsletters, In your Inbox