Problem using vba SQL to create a table

Question

I'm trying to create a table with vba using DoCmd.RunSQL "CREATE TABLE".. and fill it with some records using the corresponding recordset.

For example: DoCmd.RunSQL "CREATE TABLE Cars ([model] CHAR(100), [owner] CHAR(100)"

I create the recordset and add a new record, for example: rst!model="Toyota", rst!owner="John Smith".

So far, so good... The problem is that when I open the records in a form, the length of the actual model and owner is always 100, because Access complete the field with blank spaces until 100. That is, the field with "Toyota" has been loaded as Toyota +94 blank spaces.

This doesn't happen if I create the table from scratch using the ribbon command.

I suppose I'm doing something wrong, but I don't know what.

Any help would be much appreciated

Answer 1

Char(100) creates a fixed width field of 100 characters or spaces, regardless of how many characters are entered.

VarChar(100) creates a variable width field of up to 100 characters.

Answer 2

Try use VARCHAR rather than CHAR.