How many times have you tried to add a new field to an existing table and had the dreaded “not enough memory to complete this operation” error message? If the answer is none then you’ve been lucky – so far, but chances are that you’ll have it at some point, so this article will explain how you can add a new field – even when Access doesn’t think it can…
Table changes – without the headaches
I’ve lost count of the number of times I’ve tried to make a change to a table, whether it’s adding a new field, changing the type of an existing field, or deleting a field from a table in the Table Design View, only to be greeted by an error message telling me there’s not enough memory to complete the task.
As well as being a sign that the database (or table) might be getting a little on the large side which should be handled before I’m left trying to repair mdb files, it can also be a sign that Access just can’t do it while Design View is active. In which case there’s only one alternative – do it from code!
The code we’ll use in this article will be pretty basic and you’ll almost certainly want to add to it to make it a more powerful code snippet, but the following example will give you the skeleton code needed to add a new field to an existing table – an explanation of the code will follow:
Sub AddFieldToTable(ByVal tableName As String, fieldName As String, fieldType As Integer) Dim objDB As Database Dim objTableDef As TableDef Dim objField As Field Set objDB = CurrentDb Set objTableDef = objDB.TableDefs(tableName) 'add the new field With objTableDef Set objField = .CreateField(fieldName, fieldType) .Fields.Append objField 'You can also set other properties for the field (such as null values allowed etc) here If fieldType = dbText Then .Fields(fieldName).AllowZeroLength = False End If End With Set objField = Nothing Set objTableDef = Nothing Set objDB = Nothing End Sub
Explaining the code
So, the first thing to note is that the minimum amount of information needed to add a new field to an existing table is: The table name, the field name, and the field type. All of which we’re asking for as a parameter to the subroutine. There are of course many other settings that you may need to add (such as field size etc), but for now, the code above will work “as-is” on your database so let’s explain the basics of how it’s doing that.
Firstly the code gets a reference to the current database (objDB), and the table definition for the existing table we want to change (objTableDef).
Once we have the table definition it’s simply a matter of calling the “CreateField” method with the supplied name and type for the field. You can find a list of the field types in the Access help file under “Field.Type” topic, or through the help screen for the “CreateField” method, but for your ease of reference I’ve also listed them below:
dbBigInt, dbBinary, dbBoolean, dbByte, dbChar, dbCurrency, dbDate, dbDecimal, dbDouble, dbFloat, dbGUID, dbInteger, dbLong, dbLongBinary, dbMemo, dbNumeric, dbSingle, dbText, dbTime, dbTimeStamp, dbVarBinary
You can set additional properties (with one exception) after the “CreateField” call – we’ve set the field to allow zero length entries in the code above.
The exception to this is if you want to set the size of the field – that must be done when you initially create the field, so if you want to alter the code so you can also set the field size, you should change the CreateField call to the following “.CreateField(fieldName, fieldType, fieldSize)”.
Mitchell Pond is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair SQL error and excel recovery software products. For more information visit www.datanumen.com