Persian problem in linked access app to sql server

[AkramKar]

Hello,
I have an already made ms access app which work fine with a linked ms access database on a workgroup network.
But there is a concern after deleting the shared database by users. So I decided to convert database to sql server and link my access app to sql server. I successfully did the link, but in forms that performs querys with Persian language parameters (unicode) returns nothing.
How can I solve this?
I already corrected collation of sql server db.

[madpiet]

Hello,
I have an already made ms access app which work fine with a linked ms access database on a workgroup network.
But there is a concern after deleting the shared database by users. So I decided to convert database to sql server and link my access app to sql server. I successfully did the link, but in forms that performs querys with Persian language parameters (unicode) returns nothing.
How can I solve this?
I already corrected collation of sql server db.

What field type are you using or text? VARCHAR or NVARCHAR? You should be using NVARCHAR.

[AkramKar]

What field type are you using or text? VARCHAR or NVARCHAR? You should be using NVARCHAR.

I am using nvarchar.

[Gicu]

Did you refresh the links after changing the collation in the SQL Server? Are you using the latest SQL Server ODBC driver? Can you try running a pass-through query and see if it returns the desired records?

Cheers,

[AkramKar]

Did you refresh the links after changing the collation in the SQL Server? Are you using the latest SQL Server ODBC driver? Can you try running a pass-through query and see if it returns the desired records?

Cheers,

Hi,
I did refresh the link. My odbc driver is ODBC driver 17 for SQL server as I see in ODBC Data Source Administrator in win 10. I'm using sql server 2008 an access 2016. It didn't work with pass-through query either. When I use query with parameters in English everything is fine.

[AkramKar]

I tested pass-through query with english parameters, it didn't work too. but normal query is ok with english criteria values.

[NoellaG]

try to run the query with the Persian criteria from the management studio. You can add the COLLATE instruction to the query

example

SELECT field1 FROM MyTableWHERE Field2 COLLATE Latin1_General_CI_AS LIKE'C%'ASC; GO

of course change the collation to the correct one

[madpiet]

Not sure which is Persian (Iraq?)
But have a look here: Collation and Unicode Support - SQL Server | Microsoft Learn

[AkramKar]

Hello and thanks for the replies. I had put the sql server connection apart, since I couldn't find right solution. I will try your suggestions at the time. Specially the one NoellagG said. Thanks.