CODEWITHSUNDEEP
sqlms-accessvba
adam
adam

Reputation: 424

VBA Access Table reference in SQL query

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?

Upvotes: 0

Views: 394

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

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.

Upvotes: 0

Minty
Minty

Reputation: 1626

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.

Upvotes: 0

BData
BData

Reputation: 189

try this:

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

Upvotes: 1

Related Questions