Here are the online docs from Access2000 regarding Alter Table Statements...
There doesn't seem to be a way to alter the table and set the columnt to
accept zero length strings. The issue at hand is that by default a text or
memo column is created with Allow Null = yes and Allow Zero Length = False.
Kinda foobar if you ask me. I'll see what else I can dig up, but trial and
error might be your best bet. Start trying some different syntaxes, and if
all else fails, you might want to try CDO and see what's available there.
HTH,
Jeff Garza
====================================================================
Syntax
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} }
The ALTER TABLE statement has these parts:
Part Description
table The name of the table to be altered.
field The name of the field to be added to or deleted from table. Or, the
name of the field to be altered in table.
type The data type of field.
size The field size in characters (Text and Binary fields only).
index The index for field. For more information on how to construct this
index see CONSTRAINT Clause.
multifieldindex The definition of a multiple-field index to be added to
table. For more information on how to construct this index see CONSTRAINT
Clause.
indexname The name of the multiple-field index to be removed.
Remarks
Using the ALTER TABLE statement you can alter an existing table in several
ways. You can:
Use ADD COLUMN to add a new field to the table. You specify the field name,
data type, and (for Text and Binary fields) an optional size. For example,
the following statement adds a 25-character Text field called Notes to the
Employees table:
ALTER TABLE Employees ADD COLUMN Notes TEXT(25)
You can also define an index on that field. For more information on
single-field indexes see CONSTRAINT Clause.
If you specify NOT NULL for a field then new records are required to have
valid data in that field.
Use ALTER COLUMN to change the data type of an existing field. You specify
the field name, the new data type, and an optional size for Text and Binary
fields. For example, the following statement changes the data type of a
field in the Employees table called ZipCode (originally defined as Integer)
to a 10-character Text field:
ALTER TABLE Employees ALTER COLUMN ZipCode TEXT(10)
Use ADD CONSTRAINT to add a multiple-field index. For more information on
multiple-field indexes see CONSTRAINT Clause.
Use DROP COLUMN to delete a field. You specify only the name of the field.
Use DROP CONSTRAINT to delete a multiple-field index. You specify only the
index name following the CONSTRAINT reserved word.
----------------------------------------------------------------------------
----
Notes
You cannot add or delete more than one field or index at a time.
You can use the CREATE INDEX statement to add a single- or multiple-field
index to a table, and you can use ALTER TABLE or the DROP statement to
delete an index created with ALTER TABLE or CREATE INDEX.
You can use NOT NULL on a single field or within a named CONSTRAINT clause
that applies to either a single field or to a multiple-field named
CONSTRAINT. However, you can apply the NOT NULL restriction only once to a
field. Attempting to apply this restriction more than once restuls in a
run-time error.
----------------------------------------------------------------------------
----
Jeff Garza
Web Developer/Webmaster
Spectrum Astro, Inc.
480.892.8200
[EMAIL PROTECTED]
http://www.spectrumastro.com
-----Original Message-----
From: Lee Fuller [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 30, 2001 12:00 PM
To: CF-Talk
Subject: RE: How do you to set allow zero length in access 2000 for memo
field using sql
Is this kinda like?:
"How do you carry a table in one hand, bounce a ball off your nose, rattle a
chain on your ear, push your favorite pita neighbor into the street in front
of a racing car, and eat a pb&j sandwich.. all at the same time?"
Lee
-----Original Message-----
From: Mike [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 30, 2001 9:31 AM
To: CF-Talk
Subject: How do you to set allow zero length in access 2000 for memo
field using sql
How do you to set allow zero length in access 2000 for memo field using sql
statement when adding column using alter table ?
Thanks
Mike
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
