CODEWITHSUNDEEP
sql-serverms-accessodbc
Rob Bowman
Rob Bowman

Reputation: 8711

ODBC Select Column with Spaces - doesn't like square brackets

I have a query in Access97 that inserts into an Access97 table from Sql Server 2016 as follows:

INSERT INTO [alarm]
SELECT priority FROM [commonservices.alarm] IN '' 
[ODBC;DRIVER=ODBC Driver 13 for SQL Server;
 SERVER=.;Trusted_Connection=Yes;
 DATABASE=CommonServices];

This works fine.

My problem is, if I want to select a column that has a space in the name e.g "Alarm Gag", when I wrap with square brackets then I get the error "Circular reference caused by alias 'Alarm Gag' in query definition's SELECT list.

So, if I run:

INSERT INTO [alarm]
SELECT [alarm gag] FROM [commonservices.alarm] IN '' 
[ODBC;DRIVER=ODBC Driver 13 for SQL Server;
 SERVER=.;Trusted_Connection=Yes;
 DATABASE=CommonServices];

Then I get:

enter image description here

I have the same problem if I try with [priority]

Any ideas how I can work around this?

Upvotes: 0

Views: 544

Answers (1)

Erik A
Erik A

Reputation: 32642

Circular reference errors are caused by a column alias being the same as the column name.

They can be avoided by specifying the table name for the column causing the circular reference error.

INSERT INTO [alarm]
SELECT alarm.[alarm gag] FROM [commonservices.alarm] IN '' 
[ODBC;DRIVER=ODBC Driver 13 for SQL Server;
 SERVER=.;Trusted_Connection=Yes;
 DATABASE=CommonServices];

Upvotes: 1

Related Questions