Others

Fix Trailing Spaces in Access VBA SQL: Use VARCHAR Instead of CHAR

Elise Williams Sep 25, 2026 869 views

Question details

The user needs to prevent text values from displaying with trailing spaces when creating an Access table programmatically using the DoCmd.RunSQL method.

Fix Trailing Spaces in Access VBA SQL Tables: VARCHAR vs CHAR

Product: Microsoft Access
Device & OS: not provided
Scenario: Creating a database table with text fields using VBA code and the DoCmd.RunSQL command.
Observed behavior: Text fields created with the CHAR(100) data type pad shorter values with trailing spaces, unlike tables created directly through the Access ribbon.

Before you start

Open your Microsoft Access database and access the VBA Editor by pressing ALT + F11. Locate the specific code module where your DoCmd.RunSQL statement is executed so you can review the table creation syntax.

Solution 1Recommended

Replace CHAR with VARCHAR in your VBA SQL Statement

Switch from fixed-width to variable-length data types in your SQL script to eliminate trailing spaces automatically.

The CHAR data type in Access SQL allocates a fixed amount of space. If you specify CHAR(100), the database engine will pad any text shorter than 100 characters with trailing spaces to fill the allocated block. Using VARCHAR(100) instead tells the database to store only the actual characters used, preventing unwanted padding and optimizing storage.

Locate the RunSQL command

In the VBA editor, find the module or form code containing the DoCmd.RunSQL or CurrentDb.Execute statement that generates your table.

Update the field data type

Change the field definition in your SQL string from CHAR(100) to VARCHAR(100). For example, modify your code to read: CREATE TABLE YourTable (YourField VARCHAR(100)).

Execute the code and verify

Run the VBA macro to create the new table. Open the table in Datasheet View, enter a sample value like "Toyota", and verify that it no longer contains trailing spaces.

Replace CHAR with VARCHAR in your VBA SQL Statement

Space Optimization: Using VARCHAR not only prevents frustrating trailing spaces but also reduces the overall database file size by only consuming necessary storage space.

Free Microsoft Office alternative

Looking for a Lightweight Office Suite for Data Management?

While Microsoft Access requires VBA and SQL knowledge for database creation, you can manage extensive datasets easily using WPS Spreadsheet. WPS Office offers a free, lightweight alternative to Microsoft Office with a highly familiar user interface and seamless file compatibility.

1. Download and Install: Get WPS Office from the official website and install the lightweight suite on your device.
2. Open Your Data Exports: Launch WPS Spreadsheet and easily open your existing .xlsx or .csv database exports.
3. Clean Data Effectively: Use built-in text tools and formulas to easily manage and clean your datasets without coding.

Highly compatible with Microsoft Excel (.xlsx, .csv) formats for seamless data imports and exports.Lightweight installation that runs smoothly on almost any computer.Built-in data validation and text-cleaning functions like TRIM() to manage text fields without writing SQL or VBA code.Free to use with a familiar tabbed interface, requiring zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why does creating a table from the Access Ribbon not cause trailing spaces?

When you create a table via the Access Ribbon in Design View, Access defaults to the "Short Text" data type. This inherently functions as a variable-length text field (VARCHAR) under the hood, meaning it does not pad the text with spaces.

How can I remove existing trailing spaces in my Access table?

You can use an Update Query combined with the Trim() function. Run a SQL statement such as `UPDATE TableName SET FieldName = Trim(FieldName);` to strip leading and trailing spaces from existing records in your database.

Is there a performance difference between CHAR and VARCHAR in Access SQL?

Yes, VARCHAR is generally more efficient for variable-length text because it uses less storage space, thereby reducing database bloat and disk I/O. The CHAR data type is only recommended for fields where every record is guaranteed to have the exact same length, such as a state abbreviation or a standardized hash code.

Office Quick Solutions