1

How can you increase the size of a MDB field size using DAO?

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
Paul Lassiter
  • 2,641
  • 5
  • 22
  • 25

2 Answers2

3

You can do this much more easily with DDL:

Set db = CurrentDb

sSQL = "ALTER TABLE table1 ALTER Column atext text(150)"
db.Execute sSQL, dbFailOnError

Apparently, this is not available in MS Access 97, but in any version in the 15 year since, I would suggest that DDL is the simplest approach.

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • DAO returns a syntax error when I try that. Maybe I am using an older version of DAO. – Paul Lassiter Sep 12 '12 at 10:05
  • Which line returns a syntax error? I tested the code and it should work back as far as Access 2000. Where did you run the code? – Fionnuala Sep 12 '12 at 10:06
  • You may even be able to paste `ALTER TABLE table1 ALTER Column atext text(150)` into the query design window in sql view and run from there. – Fionnuala Sep 12 '12 at 10:08
  • I don't know what the exact version is but I think it 1997. The help file describes `ALTER TABLE` as follows: `ALTER TABLE table {ADD {COLUMN field type[(size)] [NOT NULL] [CONSTRAINT index] | CONSTRAINT multifieldindex} | DROP {COLUMN field I CONSTRAINT indexname} }` – Paul Lassiter Sep 12 '12 at 10:12
  • Looks like you can only add columns and not change their size. – Paul Lassiter Sep 12 '12 at 10:13
  • Can you post the code line you used? I am surprised that you do not have ALTER COLUMN `ALTER TABLE table {ADD {COLUMN field type[(size)] [NOT NULL] [CONSTRAINT index] | ALTER COLUMN field type[(size)] | CONSTRAINT multifieldindex} | DROP {COLUMN field I CONSTRAINT indexname} }` – Fionnuala Sep 12 '12 at 10:18
  • I tried `ALTER TABLE testtable ALTER Column test text(150)` in the the query window. – Paul Lassiter Sep 12 '12 at 10:20
  • There is no `ALTER COLUMN`. That must have been brought in later. – Paul Lassiter Sep 12 '12 at 10:21
  • 1
    +1 because this is the best way if you are using a 21st century version of Access :) @PaulLassiter Exactly, it's because you're using an older version of Access. Access 97 doesn't support ALTER TABLE ALTER COLUMN. If you were using a later version of Access you would be able to issue that SQL through DAO – MarkJ Sep 12 '12 at 10:22
2

From http://www.freevbcode.com/ShowCode.asp?ID=4599:

Public Sub change_field_size(DBPath as string, _
  tblName As String, fldName As String, fldSize As Integer)
    ' this routine changes the field size

    Dim db As Database
    Dim td As TableDef
    Dim fld As field

    On Error GoTo errhandler

    Set db = OpenDatabase(DBPath)
    Set td = db.TableDefs(tblName)

    If td.Fields(fldName).Type <> dbText Then
        ' wrong field type
        db.Close
        Exit Sub
    End If

    If td.Fields(fldName).size = fldSize Then
        ' the field width is correct
        db.Close
        Exit Sub
    End If

    ' create a temp feild
    td.Fields.Append td.CreateField("temp", dbText, fldSize)
    td.Fields("temp").AllowZeroLength = True
    td.Fields("temp").DefaultValue = """"""

    ' copy the info into the temp field
    db.Execute "Update " & tblName & " set temp = " & fldName & " "

    ' delete the field
    td.Fields.Delete fldName

    ' rename the field
    td.Fields("temp").Name = fldName
    db.Close

'======================================================================
Exit Sub

errhandler:
MsgBox CStr(Err.Number) & vbCrLf & Err.Description & vbCrLf & "Change Field Size Routine", vbCritical, App.Title

End Sub
Paul Lassiter
  • 2,641
  • 5
  • 22
  • 25
  • +1 Haven't tested, but from memory that looks right. That's the trick to alter a column in Access 97. Groan. – MarkJ Sep 12 '12 at 10:24
  • Can we just stress **Access 1997** here? This is a version of Access that is 15 years old. – Fionnuala Sep 12 '12 at 10:25