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

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?



Sat, 28 Feb 2004 04:49:30 GMT  
 Can't change text field size using DAO; can't specify size using ALTER TABLE SQL
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/


Quote:
> 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?



Sat, 28 Feb 2004 05:16:55 GMT  
 Can't change text field size using DAO; can't specify size using ALTER TABLE SQL

Quote:

>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?

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



Sat, 28 Feb 2004 13:31:11 GMT  
 Can't change text field size using DAO; can't specify size using ALTER TABLE SQL
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------

Quote:

> 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?



Sun, 29 Feb 2004 15:26:28 GMT  
 Can't change text field size using DAO; can't specify size using ALTER TABLE SQL
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

Quote:
>-----Original Message-----
>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
Quote:
>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------





- Show quoted text -

Quote:
>> 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

- Show quoted text -

Quote:
>> 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?

>.



Mon, 01 Mar 2004 11:26:23 GMT  
 Can't change text field size using DAO; can't specify size using ALTER TABLE SQL
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/


Quote:
> 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

> >-----Original Message-----
> >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------




> >> 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?

> >.



Mon, 01 Mar 2004 12:27:12 GMT  
 Can't change text field size using DAO; can't specify size using ALTER TABLE SQL

Quote:
> 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)


Quote:
> 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/



> > 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

> > >-----Original Message-----
> > >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------




> > >> 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?

> > >.



Fri, 05 Mar 2004 13:59:06 GMT  
 Can't change text field size using DAO; can't specify size using ALTER TABLE SQL


Quote:
> > 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.

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

Well, they just DON'T flip the switch.

Quote:
> 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".

--
MichKa

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



Fri, 05 Mar 2004 19:48:36 GMT  
 
 [ 10 post ] 

 Relevant Pages 

1. Using VBA to change the Text Size in Outlook's web browser

2. 'Canned' data in VB program

3. how to change field sizes and names using DAO

4. Changing 'field size' in existing table

5. Specifying field size when using CREATE TABLE

6. Change field's Size property via code

7. How to change a field's size?

8. changing field type/size in Access 97 using vb6

9. Change field size using VBA

10. Change field size in table using VBA?

11. Change Access 97 Field sizes using VB 6

12. Change field size with DAO?

 

 
Powered by phpBB® Forum Software