Is it possible to have a SQL statement in Microsoft Access that can disable the unicode compression property on a column ?
Something in the lines of :
ALTER TABLE MyTable
ALTER COLUMN MyColumn DISABLE UNICODE COMPRESSION
views:
100answers:
1
+1
A:
Yes. You simply need to omit the WITH COMPRESSION keywords when you call your alter statement. Thus, the following would add Unicode compression:
ALTER TABLE [Table1] ADD COLUMN Col1 TEXT(100) WITH COMPRESSION NOT NULL;
If after executing the above, you turned around and executed the following:
ALTER TABLE [Table1] ALTER COLUMN Col1 TEXT(100) NOT NULL;
It would remove Unicode compression.
Thomas
2010-07-23 22:45:22
That gives an error col1 already exists.
Remou
2010-07-23 22:50:40
@Remou - Sorry. Typo. It should say "ALTER COLUMN" not "ADD COLUMN". I've updated my post.
Thomas
2010-07-23 23:01:53
Remou
2010-07-23 23:04:54
@Remou - Correct. It cannot be executed via DAO; it must use an ADO connection. In fact, can any DDL SQL can be executed via DAO? I don't think it can.
Thomas
2010-07-23 23:46:41
You can execute *some* DDL with DAO, but ADO offers more capabilities. Furthermore, statements can have different results with the 2 object models. For example, `CREATE TABLE foo (bar TEXT);` From DAO, bar is a text field with size 255. But with ADO, bar will be a memo field.
HansUp
2010-07-24 02:07:47
The different DDL support between DAO and ADO is annoying legacy of MS's ill-fated ADO-everywhere campaign from c. 2000. I would hope that sooner or later, the ACE version of DAO will be updated to include support in DDL for all features of ACE tables/fields. DAO has almost always been preferred to DDL for handling structural changes to Jet/ACE database precisely because it is much more complete than DDL (except for the things the MS intentionally crippled in the DAO/ADO wars such that ADO supports them and DAO does not; that's not about DDL, of course).
David-W-Fenton
2010-07-24 21:12:24