CODEWITHSUNDEEP
sqlvbaexcelado
evabb
evabb

Reputation: 405

How to call the field name and table name with two words in ado-SQL?

select officer code ,total from [$staff record ]

Here is the sample SQL.

Officer code is one of the field name in excel with two words.

Staff record is one of the table name in excel with two words .

**What is the correct syntax to call these two items without changing the excel structure ?enter image description here

update

It is not working for using [officer code]

Upvotes: 0

Views: 2474

Answers (1)

Axel Richter
Axel Richter

Reputation: 61915

It is not clear from the picture, what content cell A1 actually contains. It is formatted as "Wrap Text". So it can contain officer code or officer code or officer code with 1, 2, 3, ... blanks between officerand code. The cell wraps this text if it is small enough. Or it can contain officer\ncode or officer\n code or officer \ncode in any possible combinations. So you should detect at first what content cell A1 actually contains.

If it is officer\ncode:

You really should not have field names containing line feeds with ADO. But if you have you can use an underscore instead of the line feed within the field name.

Example:

If the Excel sheet name is staff record and the column header is 

officer
code

where there is a line feed between officer and code,

then the following select should work:

Select [officer_code] from ['staff record$']

The annoying thing with this is that one cannot see in the view whether there are not even more weird things. For example if there is a space after officer before the line feed, then it is [officer _code].

So, you really should not have field names containing line feeds.

Upvotes: 2

Related Questions