O'Reilly logo

Access Data Analysis Cookbook by Wayne S. Freeze, Ken Bluttman

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Altering the Structure of a Table

Problem

How can I programmatically change the structure of an existing table? How do I add fields, drop fields, or just change the data types for existing fields?

Solution

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:

Adding new fields

The only restriction is that you cannot add an AutoNumber field to a table that already has such a field. Only one AutoNumber field is allowed per table.

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.

Deleting fields

Aside from any issues involved in deleting data from a table that participates in a relationship, the obvious caution to heed is that you will permanently lose the data in the deleted fields.

Changing a field type

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.

Programmatically adding and deleting a field

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:

	'.Columns.Delete ("Comments")

Changing a field's data type

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

Discussion

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.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required