MS Access bugs me with SQL designer

[Tommy DN]

I want to write this SQL-query in MS Access:

INSERT INTO functie ( [afdeling - id], naam )
VALUES (1, 'test');

But when I save it and I execute it, it doesn't work because Access
delete the spaces in my field.

INSERT INTO functie ( [afdeling-id], naam )
VALUES (1, 'test');

But my field name is "afdeling - id". The SQL works fine in VB.Net, but
cannot be used as a query in the database itself. Is there a solution
without renaming the field? I tried everything.

 

[Guest]

This would appear to work - try it

DoCmd.runsql "Insert into testTab ([test - id], [Desc]) values (1, 'Testit')"

 

[Dirk Goldgar]

I want to write this SQL-query in MS Access:

INSERT INTO functie ( [afdeling - id], naam )
VALUES (1, 'test');

But when I save it and I execute it, it doesn't work because Access
delete the spaces in my field.

INSERT INTO functie ( [afdeling-id], naam )
VALUES (1, 'test');

But my field name is "afdeling - id". The SQL works fine in VB.Net,
but cannot be used as a query in the database itself. Is there a
solution without renaming the field? I tried everything.

You're right -- the query designer is stupid. I hadn't seen this
before, because I never embed improper characters in a field name, but
it seems whenever the query designer interprets the SQL string for
display or saves the design view, it removes the spaces surrounding the
"-". Interestingly, a field name that contains multiple consecutive
spaces doesn't get modified to be just one; it seems to be the "-"
character that confuses the designer.

One workaround is to create and save the query in SQL view. If you
never switch to design view, the SQL is not modified.