Error 3290 on CREATE TABLE query

[Darizotas]

Hello,

Whenever I try to include the restriction WITH COMP to a field table in MS
Access 2000, it raises a 3290 Error indicating an error syntax at CREATE
TABLE query.

And my query is as simple as follows:

CREATE TABLE Feature (
Feature_ TEXT(32) NOT NULL WITH COMP,
Component_ TEXT(78) NO NULL WITH COMP,
CONSTRAINT MyPrimaryKey PRIMARY KEY (Feature_, Component_))

I've also swapped clauses 'NOT NULL' and 'WITH COMP' but with the same result.

Can anybody show me where is the problem?

Thanks in advance,

Dario

 

[Ofer Cohen]

Try removing WITH COMP and changing to NOT

CREATE TABLE Feature (
Feature_ TEXT(32) NOT NULL ,
Component_ TEXT(78) NOT NULL ,
CONSTRAINT MyPrimaryKey PRIMARY KEY (Feature_, Component_))

 

[Darizotas]

Removing WITH COMP from the query works fine. But the text fields of the
resulting table don't have checked (activated) Unicode compression property.

Take a look to the table. And my intention is to allow Unicode compression.
Do you know if I should include a specific reference or something similar??

Thanks,

Dario.

 

[Chris2]

Removing WITH COMP from the query works fine. But the text fields of the
resulting table don't have checked (activated) Unicode compression property.

Take a look to the table. And my intention is to allow Unicode compression.
Do you know if I should include a specific reference or something similar??

Thanks,

Dario.

Dario,

WITH COMP is not available in SQL in MS Access 2000 as far as I can determine, despite
being documented in the CREATE TABLE section of the JET SQL 4.0 Reference Guide.

Try:

Sub SetUnicodeCompression()

Dim db As DAO.Database

Set db = CurrentDb()

'The following assumes as table named Test with a column named "ItemName"
' that has has Unicode Compression set to No through the GUI.

db.TableDefs("Test").Fields("ItemName").Properties("UnicodeCompression") = True

' Or set to False if you want to turn it off.

Set db = Nothing

End Sub


Sincerely,

Chris O.

 

[Darizotas]

Thank you, that was I suspected. What I don't understand is why it is
documented in JET SQL 4.0 Reference Guide.

Dario.

 

[giorgio rancati]

Hi Dario,

It works with Jet.OLEDB.4.0 provider.
try this
----
Dim strSql As String

strSql = "CREATE TABLE Feature (" + _
"Feature_ TEXT(32) WITH COMP NOT NULL," + _
"Component_ TEXT(78) WITH COMP NOT NULL," + _
"CONSTRAINT MyPrimaryKey PRIMARY KEY (Feature_, Component_))"

CurrentProject.Connection.Execute strSql

 

[Chris2]

Thank you, that was I suspected. What I don't understand is why it is
documented in JET SQL 4.0 Reference Guide.

Dario.

:

Dario,

You are welcome.

ON CASCADE, ON DELETE, and CHECK are the same, all cannot be used directly through MS
Access QueryDef objects for DDL SQL in MS Access 2000.

In one of the later versions, the option to allow SQL-92 compatible syntax is introduced.

As for why? I believe it's because whatever part of the user interface that checks SQL
syntax was never updated to accept them. Why was that? I don't know for certain.

I can say that I have always found it to be beyond ridiculous.

giorgio rancati's code, shown in the other branch off of your last post, is another way of
setting with "WITH COMP". It will not work for CHECK.


Sincerely,

Chris O.

 

[giorgio rancati]

"Chris2" <[email protected]> ha scritto nel
messaggio
[CUT]

giorgio rancati's code, shown in the other branch off of your last post,
is another way of
setting with "WITH COMP".
It will not work for CHECK.

umm.. why not ?
----
Dim strSql As String

strSql = "CREATE TABLE Feature (" + _
"Feature_ TEXT(32) WITH COMP NOT NULL," + _
"Component_ TEXT(78) WITH COMP NOT NULL," + _
"MyNumber BYTE Not Null," + _
"CONSTRAINT CK_MyNumber CHECK(MyNumber Between 1 AND 5)," + _
"CONSTRAINT MyPrimaryKey PRIMARY KEY (Feature_, Component_))"

CurrentProject.Connection.Execute strSql

 

[Chris2]

"Chris2" <[email protected]> ha scritto nel
messaggio
[CUT]

giorgio rancati's code, shown in the other branch off of your last post,
is another way of
setting with "WITH COMP".
It will not work for CHECK.

umm.. why not ?
----

Probably because I introduced an error into the SQL I was using to test out adding a CHECK
constraint. :O


Sincerely,

Chris O.

 

[Howard in Brisbane]

Hello Giorgio & Dario

The following code is frustrating me. I cannot get WITH COMPRESSION to work
in either ODBC or DAO:

// Rebuilds the local database i.e. Access file
bool CDBDibl::RecreateDB(const CString &sDriver, const CString &sDsn)
{
bool bSuccess = false;
const CString sAttributes = "CREATE_DB=." + g_strDBFile + " General\0";
CDatabase dbTemp;

try
{
/* CDaoDatabase dbDAO;
dbDAO.Create( "." + g_strDBFile, dbLangGeneral, dbVersion40 );
dbDAO.Execute( "CREATE TABLE tblDrillHoles " \
"(ID AUTOINCREMENT PRIMARY KEY, " \
" Hole TEXT(7) WITH COMPRESSION, " \
" Type TEXT(1) WITH COMPRESSION)" );
dbDAO.Close();
bSuccess = true;*/
if ( SQLConfigDataSource( NULL, ODBC_ADD_DSN, sDriver, sAttributes ) )
{
dbTemp.OpenEx( sDsn, CDatabase::noOdbcDialog);
dbTemp.ExecuteSQL( "CREATE TABLE tblDrillHoles " \
"(ID AUTOINCREMENT PRIMARY KEY, " \
" Hole TEXT(7) WITH COMP, " \
" Type TEXT(1) WITH COMP)" );
dbTemp.Close();
bSuccess = true;
}
}
catch (System::Exception^) { }

return bSuccess;
}

The parameters sDriver = "MICROSOFT ACCESS DRIVER (*.mdb)" and
sDsn = "Driver={MICROSOFT ACCESS DRIVER (*.mdb)};DBQ=."+g_strDBFile
and the static variable g_strDBFile can be any Access file name preceded by
a \ .

I would be happy to try OLEDB , but all the sample code is in VB or C#, not
VC++! Does anybody know of any references for using OLEDB with VC++?

--
Cheers
Howard T

Hi Dario,

It works with Jet.OLEDB.4.0 provider.
try this
----
Dim strSql As String

strSql = "CREATE TABLE Feature (" + _
"Feature_ TEXT(32) WITH COMP NOT NULL," + _
"Component_ TEXT(78) WITH COMP NOT NULL," + _
"CONSTRAINT MyPrimaryKey PRIMARY KEY (Feature_, Component_))"

CurrentProject.Connection.Execute strSql
----

bye
--
Giorgio Rancati
[Office Access MVP]

Thank you, that was I suspected. What I don't understand is why it is
documented in JET SQL 4.0 Reference Guide.

Dario.