Others

Resolve Access Jet SQL Syntax Errors for DEFAULT and COMPRESSION

Kushani Nimanthika Oct 22, 2026 868 views

Question details

The user is encountering syntax errors when trying to execute a CREATE TABLE statement containing DEFAULT or WITH COMPRESSION clauses in Microsoft Access.

Fixing Access Jet SQL Syntax Errors with DEFAULT and COMPRESSION Clauses

Product: Microsoft Access
Device & OS: not provided
Scenario: Executing a CREATE TABLE query using Jet SQL in Microsoft Access.
Observed behavior: Microsoft Access reports a syntax error because the interface being used (Access SQL View or DAO) does not support the extended Jet 4 SQL syntax for these specific clauses.

Before you start

Ensure your Microsoft Access database is backed up and open the VBA Editor to review how your SQL execution commands are currently structured.

Solution 1Recommended

Execute the SQL Statement using an ADO Connection

Switching from DAO or the Access SQL View to an ActiveX Data Objects (ADO) connection allows the use of extended Jet 4.0 SQL syntax, including DEFAULT and WITH COMPRESSION.

The Jet 4 SQL documentation specifies that certain newer SQL syntax is strictly available through ActiveX Data Objects (ADO) and the Jet OLE DB provider. Native Access interfaces like the SQL View or Data Access Objects (DAO) do not support these extended clauses.

Open the VBA Editor

Press ALT + F11 in Microsoft Access to open the Visual Basic for Applications (VBA) editor.

Locate the SQL Execution Code

Find the module, form, or macro where your CREATE TABLE SQL statement is currently being executed via DAO or DoCmd.

Replace with ADO Execution

Modify your execution line to use the ADO connection property. For example, replace your existing execution command with: CurrentProject.Connection.Execute strSQL

Check REFERENCES Syntax

If your CREATE TABLE statement includes foreign keys, ensure the references clause uses proper bracketed formatting, such as: REFERENCES [TableName] ([FieldName])

Execute the SQL Statement using an ADO Connection

Syntax Support: Using CurrentProject.Connection routes the command through the Jet OLE DB provider, successfully processing the DEFAULT and WITH COMPRESSION keywords.

Free Microsoft Office alternative

Looking for a Lightweight Microsoft Office Alternative?

While WPS Office does not include a direct equivalent to Microsoft Access database management, it provides a highly compatible, free, and lightweight suite for your everyday document, spreadsheet, and presentation needs.

1. Download the Installer: Visit the official WPS Office website and download the free installation package.
2. Install the Suite: Run the downloaded installer and follow the on-screen instructions to set up WPS Office on your device.
3. Start Creating: Open WPS Writer, Spreadsheets, or Presentation to easily manage your standard Microsoft Office files without a subscription.

Free and lightweight office suite with rapid startupHigh format compatibility with Microsoft Word, Excel, and PowerPointFamiliar tabbed user interface for a seamless transitionBuilt-in robust PDF editing and conversion tools

microsoft office alternative - wps office

Frequently Asked Questions

Why does DEFAULT syntax work in ADO but fail in Access SQL View?

Access SQL View and DAO utilize an older SQL parsing engine. ADO uses the Jet OLE DB provider, which is required to interpret and process extended Jet 4.0 SQL syntax like DEFAULT and WITH COMPRESSION.

How do I format foreign keys in Jet SQL to avoid reference errors?

When creating foreign keys using ADO, ensure you format the REFERENCES clause using brackets to explicitly define the protocol and name. The proper format is REFERENCES [TableName] ([FieldName]).

Can I enable WITH COMPRESSION directly in the Access query designer?

No, the native Access query design window (SQL View) does not natively support the WITH COMPRESSION keyword. You must execute this specific SQL command programmatically via VBA using an ADO connection.

Office Quick Solutions