Access 2021 JET SQL: CREATE TABLE with DEFAULT constraint and COMPRESSION in TEXT columns?

Question

I'm trying to create a new table using the Database.Execute method:

CREATE TABLE [Test]

  ( [Name] TEXT(50) WITH COMPRESSION NOT NULL

      CONSTRAINT [PK_Appliances] PRIMARY KEY

, [IP] TEXT(15) WITH COMPRESSION NOT NULL

      CONSTRAINT [UK_Appliances_IP] UNIQUE

, [Port] SMALLINT DEFAULT 502 NOT NULL, [TimeoutMS] SMALLINT DEFAULT 1000 NOT NULL , [ProtocolType] TEXT(3) DEFAULT 'TCP' NOT NULL

      REFERENCES [ProtocolType].[Name]

)

I'm getting a syntax error for the "WITH COMPRESSION" and "DEFAULT" clauses.

What am I doing wrong?

Answer 1

**Note **The WITH COMPRESSION and WITH COMP keywords listed in the previous SQL statements can be executed only through the Jet OLE DB provider and ADO. They will result in an error message if used through the Access SQL View user interface.

Sorry,I guess you are using Access SQL View which is DAO connection rather than vba Ado。

as to Ado connection，the new features are available。

Answer 2

Hi,

Ah, the good old JET 4 SQL documents are still available online. I did some presentations about the 3 of them a 20 years ago. :-)

The doc states in the introduction and other places:

It is very important to note that some of the new SQL syntax is available in code only when you use ActiveX® Data Objects (ADO) and the Jet OLE DB provider, and is not currently available through the Access SQL View user interface or DAO. This article points out when a certain SQL command is available only through the Jet OLE DB provider and ADO.

Well, "not currently available" was an understatement. ;-)

DEFAULT WITH COMPRESSION

were such "new" keywords in JET 4 that still only work when you call them with ADO methods, i.e. build the SQL string in VBA and then execute it like

CurrentProject.Connection.Execute (strSQL)

I'm also not sure if

REFERENCES [ProtocolType].[Name]

will work like this. If it doesn't then try:

REFERENCES [ProtocolType] ([Name])

Servus

Karl

Answer 3

Thank a lot, Karl, for enlightening me!! 👍

It's actually been 20 years that I've been moving from MS Access to SQL Server. Returning from SQL Server to Access today appears to be quite a bit of a leap now.

Answer 4

This is strange … The following Microsoft documentation page tells different:

https://learn.microsoft.com/en-us/previous-versions/office/developer/office2000/aa140015(v=office.10)?redirectedfrom=MSDN#data-types

Do I miss something?

Answer 5

DEFAULT WITH COMPRESSION

these Keywords are not supported by jet engine。