VBA Access Table reference in SQL query

Question

I have been running into trouble executing SQL code in VBA Access when I refer to certain Table names.

For example,

INSERT INTO TempTable (ClientName) SELECT DISTINCT 1_1_xlsx.ClientName FROM 1_1_xlsx'<--does not work

The code works fine when I changed the Table name from 1_1_xlsx to Stuff.

INSERT INTO TempTable (ClientName) SELECT DISTINCT Stuff.ClientName FROM Stuff '<--works

I have no idea why the first query results in a syntax error and the second code is runs fine even when they refer to the same thing. I suspect it should be the naming conventions but I could not find any concrete answers.

Also, are there any ways that I could use 1_1_xlsx as my table name? Or am I just writing my query wrong?

If you have trouble with names, always start by bracketing them ([1_1_xlsx]) — Erik A
– Erik A, Commented May 31, 2018 at 7:31
Could be that xslx confuses the SQL parser to believe that this about some linked Excel file. — Gustav
– Gustav, Commented May 31, 2018 at 7:32

Gustav · Accepted Answer · 2018-05-31 07:33:44Z

1

try this:

INSERT INTO TempTable (ClientName) SELECT DISTINCT [1_1_xlsx].ClientName FROM [1_1_xlsx]

edited May 31, 2018 at 7:33

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

answered May 31, 2018 at 7:32

BData

1893 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Minty · Accepted Answer · 2018-05-31 08:03:53Z

0

In many SQL based databases you can't have a table name or field name that starts with a number.

I suspect this is the underlying reason for your problem. Although Access will allow it, I have seen it cause problems in the past.

answered May 31, 2018 at 8:03

Minty

1,6261 gold badge10 silver badges13 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2018-05-31 11:12:41Z

0

The problem is the number at the beginning of the table name. That is bad -- because it confuses the parser.

This is a bad table name, but SQL allows you to define table aliases. And, in this case, you don't even need to repeat the table name. So, here are two simple solutions:

INSERT INTO TempTable (ClientName)
    SELECT DISTINCT ClientName
    FROM 1_1_xlsx;

Or:

INSERT INTO TempTable (ClientName)
    SELECT DISTINCT t.ClientName
    FROM 1_1_xlsx as t

There is no reason to use the complete table name as an alias. That just makes the query harder to write and to read.

answered May 31, 2018 at 11:12

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

VBA Access Table reference in SQL query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related