You can carry out all of these tasks manually, in the design of a table, or programmatically, using DAO, ADOX, or SQL. Either way, each of these actions comes with some considerations:
If you add an AutoNumber field to a table that does not already have one, the existing records will be filled in with the sequential numbering scheme in the new field. This is helpful.
The success of this action depends on the actual data types in question. For example, an alphanumeric value that contains letters will not convert to a number type. You can convert a Text type to a numeric type, but you will lose your data in the process.
Also, you can't change a field to an AutoNumber type if there are any records in the table. The only way to get an AutoNumber field into a table with existing records is to add it as a new field. Then, if it makes sense, you can delete the field it was meant to replace.
Example 4-4 shows how to add and delete a field using DAO, ADOX, and SQL. There is a separate routine for each method that adds a Comments field to the Sales table and then deletes it. The Comments field is a Text data type, and is set at a size of 100 characters.
Example 4-4. Three methods to add and delete fields
Sub field_DAO( ) Dim db As DAO.Database Dim tbl As DAO.TableDef Dim fld As DAO.Field Set db = CurrentDb Set tbl = db.TableDefs("Sales") With tbl 'add new field .Fields.Append .CreateField("Comments", dbText, 100) 'delete field .Fields.Delete ("Comments") End With MsgBox "done" End Sub Sub field_ADOX( ) 'must set reference to 'Microsoft ADO Ext. 2.7 for DDL and Security Dim cat As New ADOX.Catalog cat.ActiveConnection = CurrentProject.Connection With cat.Tables("Sales") 'add field .Columns.Append "Comments", adVarWChar, 100 'drop field .Columns.Delete ("Comments") End With Set cat = Nothing MsgBox "done" End Sub Sub field_SQL( ) Dim conn As ADODB.Connection Set conn = CurrentProject.Connection Dim ssql As String ssql = "Alter Table Sales " & _ "ADD COLUMN Comments TEXT(100)" conn.Execute ssql ssql = "Alter Table Sales " & _ "Drop COLUMN Comments" conn.Execute ssql MsgBox "done" End Sub
Refer to Creating Tables Programmatically for instructions on how to create the Sales table, then use one of the approaches listed here to add and delete the Comments field. There's one caveat: because the field is added and then immediately deleted, you will not see it when viewing the Sales table. To work around this, comment out the code line that deletes the field. For example, in the
field_ADOX example, put an apostrophe in front of the line that deletes the field. The line will then look like this:
SQL provides an
Alter Column construct that's useful for changing a field's data type. The following code shows how the Alter statement is used to change the existing Comments field to the Integer type (in Access, this appears as the Long data type):
Sub alter_field_SQL( ) Dim conn As ADODB.Connection Set conn = CurrentProject.Connection Dim ssql As String 'alter field to be Integer (Long) ssql = "Alter Table Sales " & _ "Alter COLUMN Comments Integer" conn.Execute ssql conn.Close MsgBox "done" End Sub
Typically, you will be changing data types to accommodate larger data. For example, increasing the size of a text field from 50 to 100 characters makes sense, as does changing a data type from Long to Double to allow for large numbers with decimals.
Changing to a smaller data type can cause data loss. Make sure you have a justifiable need to alter a field to a smaller type or another type that will cause data loss, such as going from Text to a numeric type. Practical sense shows that being able to accommodate occasional larger pieces of data is better than trying to gain small optimizations by squeezing fields.
For example, you might expect a phone number field to need to accommodate only up to 14 characters for U.S./Canadian-style phone numbers in the format (111)-111-1111. Most of the time, that will be sufficient. But what happens if you need to enter a phone number that has an extension? The insert will bomb, or the data will be truncated. A lost phone number could cause more of a problem for a company than a tiny bit of extra required memory. With that in mind, it might make more sense to set the phone number field to a larger size—say, 30 characters—capable of accommodating occasional nonstandard phone numbers.