SQL "CREATE TABLE" command issues with table formatting

Could anyone suggest a reason why writing an SQL command "create table" in visual basic would successfully create a table *but* change the way the data is stored in the fields?

For example, if I created a table with one field:

And enter the data: "Test"

Why then would the whole 20 spaces be used, the first four with the letters I entered, and the rest with 16 blank spaces I never did? If I highlight them and delete them, then rexamine the record, they are still there.

Any suggestions appreciated!

When you create a table in VBA (btw - that DDL can be run directly from the QBE, you don't need VBA), you specify what data type you need. Using the data type Char, does not change how data is stored in a field, it creates a text field of the type referred to as "fixed width".

A fixed width text field expose the behaviour you describe.

When you need a fixed width text field, then you will have to use DDL, DAO or ADOX, because, as far as I know, you can't create fixed width text fields from the interface.

If you need "ordinary" text fields, use the interface, or change Char to for instance Text.

It would be as simple as changing char to text??! I'll give it a go. This will save me using a hell of a lot of Trim() functions later on.... cheers. I definitely don't need fixed width text fields, but I do have to use VBA because the table name must be dynamic and unique to the user who is logged into the database (so as not to confuse with data from other sessions). Thanks again!

Yup - Char -> fixed width, Text -> variable length