| Action queries were discussed in some detail in Chapter 9, "Managing Database Objects," but from an interactive standpoint. There, in "Creating Queries," you saw how to use Update, Delete, and Append queries to change data, remove records, and add them to the underlying tables. The context was that of a user employing the queries directly from the database's user interface. But just as you can combine VBA with DAO or ADO recordsets to return data via Select queries, you can cause VBA to run action queries from the Excel platform, and thereby update, delete, and append records in a database. You can do this with queries that have been saved in the database or you can submit the queries' SQL along with the commands to execute them. The usual reason to execute an action query from Excel is that a user has done something in a workbook that requires something to occur in the database. For example, suppose that you want to replace a set of records in a table with new information. Particularly if others might be making use of that table, you need to be careful how you do this. You can't simply delete the table and rebuild it. No database worthy of the name would allow you to delete a table that another user has open, either directly or indirectly via a query or a form. You have two options. One is to compare the old data with the new, record by record and field by field, updating any records for which the data has changed. A much more straightforward approach is to simply delete all the old records from the table and then replace them with new records. The best way to do that is usually to put the new records into a temporary database table and execute a Delete query to remove the old records from the permanent table. Finally, you execute an Append query to move the new records into the permanent table from the temporary table. CAUTION A database will allow the deletion of records from an open table unless specific steps have been taken to prevent that action.
Using DAO to Execute an Existing Query Here's an example of how you might use DAO to execute a Delete query. In this case, the query already exists in the database, and it accepts a parameter for its ReservationDate field. Figure 12.1 shows the query in design view. Figure 12.1. If you run the query interactively, you're prompted for a date value. No prompt appears if you run it from code. 
You would place code similar to the following in an Excel VBE module, and establish a reference to a DAO library by selecting References from the VBE's Tools menu. The code passes a value to the Delete query's parameter. When the code then executes the query, it deletes all records with values on ReservationDate earlier than January 1, 2004. Sub DeleteOldRecords(FilePath As String) Dim dbReservations As DAO.Database Dim qdfDeleteRecords As DAO.QueryDef Set dbReservations = OpenDatabase("I:\RM_RES\Resources.mdb") Set qdfDeleteRecords = dbReservations.QueryDefs("DeleteOldReservations") With qdfDeleteRecords .Parameters("WhichDate") = DateValue("1/1/2004") .Execute End With Set qdfDeleteRecords = Nothing Set dbReservations = Nothing End Sub
Notice the use of the VBA DateValue function to convert the parameter value from a text value that specifies a date to a true date value. This is not required. The statement could be this one: .Parameters("WhichDate") = "1/1/2004"
because Access will constrain the value of the parameter to the type used by the criterion field, ReservationDate. You could also use this one: .Parameters("WhichDate") = #1/1/2004#
because Access recognizes a value surrounded by pound signs as a date. Using DAO to Define and Execute a Nonpersistent Query At times you want to use a query that doesn't exist in the database. This can occur when the user takes an action that occurs only occasionally for example, periodically clearing records out of a table because they're no longer needed. In a case like that, you might prefer to create the query in your VBA code, rather than storing in your database a query that's needed only infrequently. One of the most maddening sources of clutter in a database is users' tendency to create queries that are used only once, or only once a year. You wind up with a lengthy list of queries with names like Al's Query, and Test Query, and Jane Query Feb 2004, and Query1, and Query2, and so on. You have no idea, looking at the names of the queries, what they're intended to accomplish, and they make it hard to locate useful queries. So you sometimes create new queries in your code, rather than depending on the existence of a query in the database. (If things have gotten untidy enough, someone might have deleted all existing queries in exasperation.) To do that, you need to provide the SQL in your code, as in the following example: Sub DeleteOldRecordsWithString1(FilePath As String, _ FirstDate As String) Dim dbReservations As DAO.Database Dim qdfTempQuery As DAO.QueryDef Dim strQuery As String strQuery = "DELETE Reservations.*, " & _ "Reservations.ReservationDate " & _ "FROM Reservations " & _ "WHERE ((Reservations.ReservationDate) < " & _ FirstDate & ");" Set dbReservations = OpenDatabase(FilePath & "Resources.mdb") Set qdfTempQuery = dbReservations.CreateQueryDef("", strQuery) qdfTempQuery.Execute End Sub
There are several points to note in this code: The SQL needed to carry out the query is supplied to the code. In this instance, it's stored in the string variable strQuery. It uses the CreateQueryDef method of the database object to create a new query. This method takes two arguments: the query's name and its SQL. The query is given a null name, indicated by the empty quote marks. This prevents the query from being saved in the database. It lasts only as long as the code is running. The object is nonpersistent. The procedure that calls the subroutine should pass the value of FirstDate enclosed in pound signs: for example, #1/1/2004#. That ensures that the value will be interpreted as a date.
Specifying Parameters in SQL It's not necessary to declare a query's parameters in SQL, but it's not a bad idea, if only for purposes of documentation. An example follows: Sub DeleteOldRecords(FilePath As String) Dim dbReservations As DAO.Database Dim qdfDeleteRecs As DAO.QueryDef Dim strQuery As String strQuery = "PARAMETERS [WhichDate] Date;" & _ "DELETE Reservations.*, Reservations.ReservationDate " & _ "FROM Reservations " & _ "WHERE (((Reservations.ReservationDate)<[WhichDate]));" Set dbReservations = OpenDatabase(FilePath & "Resources.mdb") Set qdfDeleteRecs = dbReservations.CreateQueryDef _ ("DeleteOldRecs", strQuery) With qdfDeleteRecs .Parameters("WhichDate") = #1/1/2004# .Execute End With End Sub
Notice that the first clause in the SQL names the parameter and specifies its type as Date. Also notice that this query is saved in the database, with the name DeleteOldRecs. There would be little point in creating a nonpersistent query one that is not saved in the database that takes one or more parameters. The rationale for query parameters is that they enable users to execute the query repeatedly, with different values supplied to the parameter. For example, at the start of 2005, you might pass 1/1/2005 to the WhichDate parameter, calling for all records with a value prior to that date to be deleted. At the start of 2006, you could execute the same query, passing 1/1/2006 to the WhichDate parameter. The point is that after you've established the query, you can execute it repeatedly, with different parameter values to bring about different outcomes. If the query is nonpersistent, though, it won't be saved, and therefore it won't be sitting in the database, patiently waiting for you to execute it again with a different parameter value. So, although it's certainly legal to create a temporary, nonpersistent query that has a parameter, there's little point to doing so. Instead, you would just dispense with the parameter and put the criterion value directly into the WHERE clause, as shown here: "WHERE ((Reservations.ReservationDate) < #1/1/2004);"
and here: "WHERE ((Reservations.ReservationDate) < FirstDate);"
Using DAO to Create and Execute an Append Query Perhaps the fastest way to move data from an Excel worksheet to an Access table is by means of an Append query written in SQL. All other things being equal, SQL provides you the most efficient and speediest use of your system's resources for data manipulation. When you work directly in Access and design a new Append query, Access wants to know the table that contains the source data. The assumption is that you have data in a table named, say, RecordsFrom2004, and that you want to append its contents to a table named, say, AllRecords. That's not much help if you have data in an Excel worksheet and want to append it to an Access table. For that you need to find another way. DAO (and ADO) recordsets are one excellent method, but they're not as efficient as SQL. When you're not doing any fine-tuning, when all you want to do is stuff worksheet data into a database table as quickly and efficiently as possible, you want to use SQL. The solution is to establish a temporary Append query and include the values that you want to append into the query's SQL. To do so, you need to use a version of an Append query's syntax that Access doesn't typically generate. | 