If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Error 3290 on CREATE TABLE query
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 |
#2
|
|||
|
|||
Error 3290 on CREATE TABLE query
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_)) -- Good Luck BS"D "Darizotas" wrote: 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 |
#3
|
|||
|
|||
Error 3290 on CREATE TABLE query
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. "Ofer Cohen" wrote: 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_)) -- Good Luck BS"D "Darizotas" wrote: 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 |
#4
|
|||
|
|||
Error 3290 on CREATE TABLE query
"Darizotas" .(donotspam) wrote in message ... 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. |
#5
|
|||
|
|||
Error 3290 on CREATE TABLE query
Thank you, that was I suspected. What I don't understand is why it is
documented in JET SQL 4.0 Reference Guide. Dario. "Chris2" wrote: "Darizotas" .(donotspam) wrote in message ... 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. |
#6
|
|||
|
|||
Error 3290 on CREATE TABLE query
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] "Darizotas" .(donotspam) ha scritto nel messaggio ... Thank you, that was I suspected. What I don't understand is why it is documented in JET SQL 4.0 Reference Guide. Dario. |
#7
|
|||
|
|||
Error 3290 on CREATE TABLE query
"Darizotas" .(donotspam) wrote in message ... "Darizotas" .(donotspam) wrote in message ... Removing WITH COMP from the query works fine. But the text fields of the resulting table don't have checked (activated) Unicode compression property. snip Sincerely, Chris O. Thank you, that was I suspected. What I don't understand is why it is documented in JET SQL 4.0 Reference Guide. Dario. "Chris2" wrote: 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. |
#8
|
|||
|
|||
Error 3290 on CREATE TABLE query
"Chris2" 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 ---- bye -- Giorgio Rancati [Office Access MVP] |
#9
|
|||
|
|||
Error 3290 on CREATE TABLE query
"giorgio rancati" wrote in message ... "Chris2" 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. |
#10
|
|||
|
|||
Error 3290 on CREATE TABLE query
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 "giorgio rancati" wrote: 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] "Darizotas" .(donotspam) ha scritto nel messaggio ... Thank you, that was I suspected. What I don't understand is why it is documented in JET SQL 4.0 Reference Guide. Dario. |
Thread Tools | |
Display Modes | |
|
|