logo
vbRad Home
Source Code
Book Reviews
Forum
Links
About Us
Contribute

Compare Databases with SQL Effects Clarity
 
 ALTER TABLE and CREATE TABLE gotchas in Access/DAO/ADO
Posted on
2/18/2001
 Author:
Robert Gelb
 Email:
Not Shown
Applies To OS:
All
 Product:
5, 6



There is a truly bizarre bug when executing the ALTER TABLE statement against Access 97 database (basically an .MDB file).  Say you want to create a table with 100 char(255) fields.  The help file talks about how the max length of the record is about 2k (around 1900 bytes, actually) and this is mentioned in the description for DAO error 3047.  One way to beat this limit is to use ALTER TABLE statement and add each field individually.  Oddly enough, it works.  However, you have to be aware of idiosyncrasy described below.

When you are crossing or after you've crossed the 2k limit, the following code will not work, even though it is syntactically correct:

Connection.Execute "ALTER TABLE myTable ADD COLUMN textField20 CHAR(255) NULL;"

This code will work however, when you are not crossing the 2k limit,

Each of the code lines below will work in any situation:

Connection.Execute "ALTER TABLE myTable ADD COLUMN textField20 VARCHAR(255) NULL;"
Connection.Execute "ALTER TABLE myTable ADD COLUMN textField20 STRING(255) NULL;"
Connection.Execute "ALTER TABLE myTable ADD COLUMN textField20 ALPHANUMERIC(255) NULL;"
Connection.Execute "ALTER TABLE myTable ADD COLUMN textField20 CHARACTER(255) NULL;"

This method also works when using CREATE TABLE statement.  Basically just avoid usage of CHAR data type and use its synonyms. 

So to wrap up, CHAR data type works fine in Data Definition Language as long as you are not crossing the 2k limit.

This whackyness has been tried and confirmed on DAO 3.51, 3.60 and ADO 2.5 with JET OLEDB 4.0 provider.

 
Add Your Comment  

Name: Email Address: all fields optional
Notify me via email when someone responds to this message (valid email required).

Enter the word:
 



Comments
#1. By Kapil. Posted on 1/12/2007 6:09:35 AM
how to alter a table's field (of access mdb), say the field is text (10) and I want to change it to text(255)? without using Acccess.

How to do it using ASP ? any code ideas are welcome @ Y! sinkg2002

#2. By Anonymous. Posted on 1/18/2007 10:43:08 PM
Answer to #1: Open a connection to the MDB from the ASP page and execute ALTER TABLE myTable ADD COLUMN textField20 VARCHAR(255) NULL;

#3. By Anonymous. Posted on 4/15/2007 3:24:22 PM
ALTER TABLE TableName ALTER COLUMN FieldName TEXT(255);

#4. By Andrew Wade. Posted on 6/25/2007 7:38:58 PM
"One way to beat this limit is to use ALTER TABLE statement and add each field individually. Oddly enough, it works."
Despite what the documentation implies, Access appears to do the sensible thing and allocate only the space needed to store the actual values in a Text field. (E.g., "abc" takes the same amount of space whether it's stored in a TEXT(255) or a TEXT(3).) In all likelyhood you're storing mostly small strings and nulls in the fields and not actually hitting the 2k limit. I can't explain the difference between CHAR and the other synonyms; according to the documentation they all map onto Access's text data type.

#5. By NPATEL. Posted on 4/3/2008 6:08:04 PM
How to change the default value of the column?

ALTER TABLE myTable ADD COLUMN intField20 INTEGER NULL;

I wanna change the default value of this column to 0.

Thanks,
NPATEL