What to Do if You Get “Not Enough Memory” Error when Adding a Field via Access Design View

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

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)”.

Author Introduction:

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