rlwhitney
asked on
Microsoft Access - VBA - Create Table WITH COMPRESSION failes
I'm using Access 2003. I need to create temporary tables "on the fly", so use a CREATE TABLE SQL statement in my VBA code. I need the text fields to have UNICODE COMPRESSION set to TRUE. But when I run the CREATE TABLE statement, I get a syntax error. This example shows a sample of what fails:
CREATE TABLE TEST_TABLE (Field1 char(100) WITH COMPRESSION)
When I attempt to execute the query, it fails, highlighting WITH. I've also trid WITH COMP (which is also supposed to be valid). It also failes. I've found no help via Google, and have checked all the obvious things (the table does not already exist - if I run the command without the WITH COMPRESSION statement, it works. I even tried creating the table with out the WITH COMP qualifier, then tried an ALTER statement, using WITH COMP. It also throws a syntax error.
What's going on? Am I up against some type of known bug? Is there an alternate syntax which will get me there? Is there an application option which will set UNICODE COMPRESSION to ON by default?
Thanks!
CREATE TABLE TEST_TABLE (Field1 char(100) WITH COMPRESSION)
When I attempt to execute the query, it fails, highlighting WITH. I've also trid WITH COMP (which is also supposed to be valid). It also failes. I've found no help via Google, and have checked all the obvious things (the table does not already exist - if I run the command without the WITH COMPRESSION statement, it works. I even tried creating the table with out the WITH COMP qualifier, then tried an ALTER statement, using WITH COMP. It also throws a syntax error.
What's going on? Am I up against some type of known bug? Is there an alternate syntax which will get me there? Is there an application option which will set UNICODE COMPRESSION to ON by default?
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Or perhaps it is in some way...?
See this statement from here:
http://www.grahamwideman.com/gw/tech/accessjet/access2kunicode.htm
CREATE TABLE tblCompressedCustomers (
CustomerID INTEGER CONSTRAINT
PK_tblCompCustomers PRIMARY KEY,
[Last Name] TEXT(50) WITH COMP NOT NULL,
[First Name] TEXT(50) WITH COMPRESSION NOT NULL,
Phone TEXT(10),
Email TEXT(50),
Address TEXT(40) DEFAULT Unknown)
Let's see what other Experts post
See this statement from here:
http://www.grahamwideman.com/gw/tech/accessjet/access2kunicode.htm
CREATE TABLE tblCompressedCustomers (
CustomerID INTEGER CONSTRAINT
PK_tblCompCustomers PRIMARY KEY,
[Last Name] TEXT(50) WITH COMP NOT NULL,
[First Name] TEXT(50) WITH COMPRESSION NOT NULL,
Phone TEXT(10),
Email TEXT(50),
Address TEXT(40) DEFAULT Unknown)
Let's see what other Experts post
ASKER
This solved the problem completely and easily.
see here:
http://www.codeproject.com/KB/reporting-services/UnicodeInSQLServer.aspx