When you don’t get it right the first time, you need to alter it later. Returning to a topic from his first SQL column Is SQL Better than DAO for Creating Tables and Indexes , Peter compares SQL and DAO when it comes time to make changes to your tables.
SOME months ago, I did an article comparing the performance of the SQL Create Table and Drop Table commands against using DAO to accomplish the same results. In general, I tried to show that the SQL commands were easier to code and faster to execute. This article is going to take the same approach to the Alter command, which allows you to change the structure of your table after it’s created. On the way, I’m going to whine about some deficiencies with Microsoft’s implementation of this command.
The Alter command in Access lets you add and delete columns or constraints from your table, which leads to my complaint: the changes you can make with the Access version of the Alter command are considerably more limited than the full ANSI SQL92-compliant version. In a full implementation of SQL, the Alter command includes a Modify keyword that allows you to change the characteristics of a column, not just add or delete it. This isn’t a trivial difference. In Access, if I want to convert a column’s data type from integer to double, I must go through this process:
•Add a new column to the table with the appropriate type.
•Move all the data from the old column to the new column.
•Delete the old column from the table.
Because both the old and new versions of the column exist simultaneously, they must have different names, so this is the final step:
•Change the name of the new column to the name of the old column.
When available, the Modify option of the Alter command boils this down to one step by allowing you to alter the type of a column without having to create a new one.
Doing it with DAO
Well, enough grousing about what I can’t have—here’s a look at the performance of what is available. If you want to change the data type of a column using DAO, you’ll need a routine with three separate parts. Here’s the first part:
Set dbs = CurrentDb()
Set tbl = dbs.TableDefs("tblChange") Set fld = tbl.CreateField fld.Name = "ChangedField" fld.Type = dbDouble tbl.Fields.Append fld
In this code, a table object is created and a new field with a dummy name is appended to it. The Type property of this new field is set to the data type to which you want to change the existing field.
The second part of the routine creates a recordset from the table and loops through it, transferring data from the old field to the new field:
Set rec = dbs.OpenRecordset("tblChange")
While Not rec.EOF rec.Edit
rec("ChangedField") = rec("ChangeField") rec.UPDATE rec.MoveNext Wend rec.Close
Finally, the last part of the routine deletes the old field and changes the name of the new field to the old field’s name:
tbl.Fields("ChangedField").Name = "ChangeField" dbs.Close
The number of records in the table controls the speed of this routine. On my old 486-33, even if there are just 256 records in the table, running the routine 20 times takes 15 seconds (see the routine ChangeTableWithDAO in the sample database).
Doing it with SQL
If nothing else, re-creating the routine with SQL simplifies the code. The routine still breaks down into three separate parts, but none of the parts is more than three lines long. The first part of the routine gets a reference to the database and uses the Alter Table command to add a new column to the table:
Set dbs = CurrentDb()
dbs.Execute ("Alter Table tblChange " & _ "Add Column ChangedField Double;")
The second part of the routine uses a SQL Update statement to set the new column’s data to the data in the old column:
dbs.Execute ("Update tblChange " & _ "Set ChangedField = ChangeField;")
The final three lines use the SQL Alter Table command to delete the old field, rename the column, and close the database (note that I have to use DAO to rename the column):
dbs.Execute ("Alter Table tblChange " & _
"Drop Column ChangeField;") dbs.TableDefs("tblChange"). _
Fields("ChangedField").Name = "ChangeField" dbs.Close
When I performed the time trials with the SQL routine, I ran into an interesting problem. While the SQL routine ran fine when executed alone, as soon as I tried to run it twice in a row I got the message "Item not found in this collection." I found that on the second pass through the routine, Access wouldn’t let me rename the work field. The error generated claimed that that my work field, "ChangedField", wasn’t in the table. Somewhere between using SQL to add and delete columns, and DAO to rename them, Access got confused. I eventually found that refreshing the Fields collection just prior to the rename enabled Access to recognize that ChangedField was, indeed, back in the table. As a result, the time trial version of the last part of the SQL routine looks like this:
dbs.Execute ("Alter Table tblChange " & _
"Drop Column ChangeField;") dbs.TableDefs("tblChange").Fields.Refresh dbs.TableDefs("tblChange"). _
Fields("ChangedField").Name = "ChangeField"
This Refresh wouldn’t be required in a real world program because you probably wouldn’t make the same change to the same table 20 times. Even with the overhead of the refresh statement, the SQL routine (ChangeColumnWithSQL from the sample database) finished its 20 iterations in 11 seconds, more than 30 percent faster than the pure DAO method.
My immediate suspicion as to why the DAO routine took so much longer was its loop through all the records in the table to update each one. SQL has a tremendous performance advantage over any procedural code when it comes to manipulating large amounts of data. To test this out, I removed all the records from the table and ran the time trials again. Sure enough, both the DAO and the SQL versions finished their 20 iterations in a dead heat at three seconds apiece.
The rest of the story
If you’ve read my previous articles on using SQL, you’ll know that I prefer the terser SQL routine to the more verbose DAO. While SQL allows me to do much of what DAO does, and with considerably less code, it would be unfair to leave the story without mentioning a few of DAO’s advantages. The first is that you can’t use Access’ Alter commands on any flat file database except Access. For example, you can’t issue an Alter against Paradox or dBase. You can also alter many more field properties with DAO than you can with SQL. However, if you want to keep your code portable, you should probably stay away from those properties anyway.
The beauty of Access is that you have these choices. And, with the advent of OLE DB and Advanced Data Objects, there’ll be even more choices. While this variety may seem bewildering, the programmer who takes the time to understand the costs and benefits of each method will have something very special: job security.