Access query Link Tables in SQL Server with Chinese character

Question

I have a customer table with several fields store Chinese characters in SQL database.
I use Linked Table to connect
In Access, I use Linked Table to connect it, and I enter SELECT * FROM customer WHERE [PriceType]="上路" in the Query Designer. It returns no record.
But I browse the customer table in Access, I can see all Chinese characters.
The PriceType is nvarchar(10) data type.
how to build the query statement? or other setting I missed?

Answer 1

Try using below query..

SELECT * FROM customer WHERE [PriceType]=StrConv("上路",64)

---

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

Answer 2

Thanks VaibhavChaudhari
sorry. tried. not work. still no record return.

Answer 3

If you do not find a solution, then consider an alternative: “Pass-Through Queries”, and add ‘N’:

 SELECT * FROM customer WHERE [PriceType] = N'上路'

See: https://support.microsoft.com/en-us/office/create-a-pass-through-query-b775ac23-8a6b-49b2-82e2-6dac62532a42. Copy the connection string from Linked Table Manager or DSN file as explained in documentation.

Probably it is advantageous because the large tables will not be transferred to Access before applying the filters.

Answer 4

Warning! I don't know anything about Access. But for this query to return data, this must be the query that is sent to SQL Server:

SELECT * FROM customer WHERE [PriceType]=N'上路'

That is, you need to use a single quote and not a double quote, and there must be an N in front. But I can't say if Access will accept this query.

Answer 5

Hi,@Admin UOG
Change to the following statement to query the results.

SELECT * FROM customer WHERE [PriceType]=N'上路'   

N'xxx' indicates that the xxx in the single quotation mark is forced to be converted into a Unicode-encoded character, without garbled characters in Chinese.

Because ASCII code is commonly used in English (one character occupies one byte), and the Chinese character takes up two bytes, so use N'xxx' to force the conversion to Unicode-encoded characters, so that single quotation marks Whether it is an English character or a Chinese character, it takes up two bytes. When unified, there will be no garbled characters.

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.