Can't change text field size using DAO; can't specify size using ALTER TABLE SQL

[Mark Martel]

I can't change a text field's size using DAO references in
VB6/Access 2000. While I am able to create a field
dynamically with a specified field size, when I attempt to
change the field size using the .Size property of a DAO
Field, I get the following error:

Run-time error '3219':
Invalid operation.

I have also noticed that using the ALTER TABLE command in
SQL and specifying a field length does not work properly
when setting text field lengths. Instead, it appends text
columns to a table with the default text field size,
regardless of what size you specify in the SQL string.

The only workaround I have been able to produce is:
1) to copy the field properties (name and type) of the
field whose size I want to change
2) delete the field from the table
3) create the field again using the <TableDef>.CreateField
command and pass it the correct field size.

Of course, this also wipes out any data I had in the
table's recordset. I would have to take the extra steps
of copying the data I wanted to preserve to an array from
the recordset, then re-querying the Recordset, then
copying the data I wanted to preserve back to the new
field I created with the correct size.

What a pain! Why doesn't the <Field>.Size property work
in VBA?

[Michael (michka) Kaplan]

This has always been the case. You must create a new column, append all data
to it, then delete the old column.

A pain? Maybe. But it helps developers to try to make the value right the
first time? Or to work in Access, where the UI does all that for you?

--
MichKa

Michael Kaplan
(principal developer of the MSLU)
Trigeminal Software, Inc. -- http://www.trigeminal.com/
the book -- http://www.i18nWithVB.com/

"Mark Martel" <mma...@immigrationlaw.com> wrote in message
news:194b501c13a3a$16845510$9ee62ecf@tkmsftngxa05...

[Jon]

In article <194b501c13a3a$16845510$9ee62ecf@tkmsftngxa05>, "Mark Martel"
<mma...@immigrationlaw.com> writes:

Mark

The Field.Size property is read/write until the field is appended to the table,
at which point it becomes read only.

The answer to your solution is to emulate what really happens in Access when
you manually change a field's length, which is to create a new field, add the
data to that field, delete the old field, and then rename the new field. Below
is some sample code that uses SQL statements to perform this:

Sub sChangeField(strTableName As String, strFieldName As String, strFieldType
As String)
Dim db As Database
Dim strSQL As String
Set db = CurrentDb
' Add a new field, called TempField, of the correct type in the table
strSQL = "ALTER TABLE [" & strTableName & "] ADD COLUMN [TempField] " &
strFieldType & ";"
db.Execute strSQL
' Copy the data from the existing column to the new column
strSQL = "UPDATE DISTINCTROW [" & strTableName & "] SET [" & strFieldName &
"]=[TempField];"
db.Execute strSQL
' Delete the existing field from the table
strSQL = "ALTER TABLE [" & strTableName & "] DROP COLUMN [" & strFieldName
& "];"
db.Execute strSQL
' Change the new field name back to the existing field name
db.TableDefs(strTableName).Fields("TempField").Name = strFieldName
Set db = Nothing
End Sub

You can then call the sub like:

Call sChangeField("tblName","fldName","TEXT(100)")

Note that if the field is included in relationships, these will need to be
deleted and then recreated, and that also SQL statements can't offer all of the
options available through DAO.

Jon

Access tips & tricks - http://www.applecore99.com
Microsoft Access webring -
http://nav.webring.yahoo.com/hub?ring=microsoftaccess

[david @ epsom dot com dot au]

Are you using DAO 3.6, or dao 3.5? On my reading of the
help files I thought that DAO 3.6/ JET 4 supported this. (Not
JET 3.5)

<Adam Dawes> in microsoft.public.access.queries just told
me that:

-----snip------
You can execute the following, for example, to change a text field to a memo
field:

ALTER TABLE MyTable
ALTER COLUMN MyColumn memo

There are various other modifications that can be made, but making fields
smaller will result in errors if the data becomes too large to fit in the
new field type, and changing to incompatible field types will probably cause
problems too.

------end------

"Mark Martel" <mma...@immigrationlaw.com> wrote in message news:194b501c13a3a$16845510$9ee62ecf@tkmsftngxa05...

[Mark Martel]

DAO 3.6

My reading of the help led me to believe it could be done
using the Size property; apparently JET does not have the
ability to do this "behind the scenes" from VBA; only from
the Access table editor environment.

Thanks to Jon for your expert help and knowledge!

Sincerely,
Mark

>.
>

[Michael (michka) Kaplan]

Sorry, DAO does no t support this at all.

Jet 4.0 supports it, if you use ADO, though.

--
MichKa

Michael Kaplan
(principal developer of the MSLU)
Trigeminal Software, Inc. -- http://www.trigeminal.com/
the book -- http://www.i18nWithVB.com/

"Mark Martel" <mma...@immigrationlaw.com> wrote in message
news:0a0501c13c03$dcd5eba0$3def2ecf@TKMSFTNGXA14...

[david @ epsom dot com dot au]

> Jet 4.0 supports it, if you use ADO, though.

?? So does ADO capture the sql rather than passing it to the JET dll's ?? or does DAO reject the SQL before it is passed to the JET
dll's ?? Is all DDL SQL done by DAO and ADO and Access, rather than by JET?

(david)

"Michael (michka) Kaplan" <forme...@spamfree.trigeminal.nospam.com> wrote in message news:urNAEyAPBHA.2048@tkmsftngp05...

[Michael (michka) Kaplan]

"david @ epsom dot com dot au" <david@epsomDOTcomDOTau> wrote in message
news:#lzwz3zPBHA.1860@tkmsftngp05...

> > Jet 4.0 supports it, if you use ADO, though.
>
> ?? So does ADO capture the sql rather than passing it to the JET dll's ??

No, it does not. This is the Jet OLE DB provider, which "flips the switch"
to support the new syntax.

> or does DAO reject the SQL before it is passed to the JET dll's ??

Well, they just DON'T flip the switch.

> Is all DDL SQL done by DAO and ADO and Access, rather than by JET?

No, its all done by Jet (if it is turned on). This only happens in ADO via
the Jet OLE DB provider and Access 2002 is you turn on the "Ansi92
extensions".