Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Microsoft SQL Server _ Using Unicode Characters In Vba - Help

Posted by: t.heisler Aug 16 2019, 10:35 AM

I have just migrated an access database to SQL and I'm having an issue that wasn't present with the access backend mde.

When trying to do select statements using strings that contain unicode characters such as the "ohm" symbol, access is converting them in this case to "O" instead of Ω.

Access displays the symbol correctly in forms and in the tables, only in the vba code is where it gets manipulated.


I have tried using :

Pass through query
adodb recordsets
odbc connections

All of them do the same thing, the pass through query returns the correct symbol but if I try to use it in a record set the symbol gets changed.

Is there any way I can store Unicode values in strings such as Ω ?

Any help would be much appreciated.

Thanks,


Posted by: GroverParkGeorge Aug 16 2019, 12:11 PM

Welcome to UtterAccess.

Perhaps you can use the CHR() function to work with these symbols in VBA.

I can't find the one for the Ω symbol, but you should be able to work that out.

Posted by: t.heisler Aug 16 2019, 12:38 PM

Thanks for the reply.

To make this harder the Unicode symbols are in a nvarchar field and mixed in with regular non-Unicode characters.

Example :

k Ω

and say I have this code trying to use the unit of measurement in a form where the current records me.unit is = "k Ω".

dim unit as string

unit = Me.Unit

Access will set unit to "K O"

In this case I don't see I could used Char.

So then when I try to use the unit variable in another SQL statement it doesn't return what it should.



Posted by: GroverParkGeorge Aug 16 2019, 12:41 PM

Well, what I was thinking is that, in VBA, you could use Replace() on strings to get values you can work with and then convert them back for output.

Posted by: GroverParkGeorge Aug 16 2019, 12:42 PM

Another option, albeit less desirable, might be NOT to even bother storing non-standard characters in the tables, but use placeholders that you can convert to symbols as needed in reports, or in forms.

Posted by: PhilS Aug 17 2019, 10:39 AM

QUOTE
and say I have this code trying to use the unit of measurement in a form where the current records me.unit is = "k Ω".

dim unit as string
unit = Me.Unit

Access will set unit to "K O"

How did you arrive at this conclusion? - I'm afraid it is wrong. VBA Strings are in fact Unicode (UTF16) strings.
However, the VBA Editor does not support Unicode characters in code. So, if you Debug.Print unit to the Immediate Pane it will indeed be displayed as "k O", but it is only transformed in the Immediate Pane, not in the string it was stored in before.

Try this:
CODE
dim unit as string
unit = Me.Unit
Me.CopyOfUnit = unit

CopyOfUnit will contain the unmodified string including any Unicode character.

Posted by: t.heisler Aug 18 2019, 09:06 AM

That makes sense and explains why the message box text and immediate window never show unicode.

I just tested this with a new database, it works fine when using access tables but the same code with a linked SQL table doesn't.

Me.unit is getting unit string from form using local access table as source.

tblUnits is a table I'm trying to lookup the data for based on the unit, when tblUnits is a local access table this works and rs.eof = false.

If I create the same table in SQL this doesnt work and rs.eof is always true, I'm guessing its something to do with odbc or a SQL setting that causing this to happen.


Code:

Private Sub Command4_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim SQL As String
Dim unit As String

unit = Me.unit
Debug.Print unit
Debug.Print Me.unit

SQL = "Select * From tblUnits Where unit = '" & unit & "';"

Set db = CurrentDb

Set rs = db.OpenRecordset(SQL)

If Not rs.EOF Then
MsgBox "Unit found: " & rs!field1
Else
MsgBox "Unit not found"
End If
End Sub

Posted by: t.heisler Aug 18 2019, 09:30 AM

So I just changed the SQL database collation to Greek_100_CI_AI_KS_SC (from the default SQL_Latin1_General_CP1_CI_AS) and it works.

Now the question would be what other tables would get affected by changed the collation.

I didn't see any field specific collations with that name, hopefully I can get away with only changing the collation of certain fields that store Unicode.

Posted by: PhilS Aug 18 2019, 12:01 PM

Hands off collations for a minute!

You are using NVarchar data type on SQL Server for your Unicode strings, aren't you?

Posted by: t.heisler Aug 18 2019, 12:47 PM

Yep, nVarchar(50)

Posted by: t.heisler Aug 20 2019, 07:55 PM

I will probably just end up calling stored procedures every time I have to do a select statement with a where clause that contains unicode.

Posted by: PhilS Aug 21 2019, 04:47 AM

Unicode characters should not be that much of a problem.

QUOTE
When trying to do select statements using strings that contain unicode characters such as the "ohm" symbol, access is converting them in this case to "O" instead of Ω.

Can you post a simple example reproducing this issue?

Posted by: t.heisler Aug 21 2019, 06:16 PM

Here is an example, you will need a SQL db to test with.

Simply need to have tblUnits as a SQL table and the select statement will never work.

It works fine as long as tblUnits is a local table.

If only there was an equivalent to this in VBA:

https://stackoverflow.com/questions/10025032/what-is-the-meaning-of-the-prefix-n-in-t-SQL-statements


Script to create table in SQL:

USE [testDB]
GO

/****** Object: Table [dbo].[tblUnits] Script Date: 8/21/2019 7:19:24 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblUnits](
[ID] [int] NOT NULL,
[unit] [nvarchar](50) NULL,
[field1] [nvarchar](50) NULL,
CONSTRAINT [PK_tblUnits] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO


 unicode_test.zip ( 48.34K ): 2
 

Posted by: PhilS Aug 22 2019, 06:17 AM

QUOTE
Here is an example, you will need a SQL db to test with.
[...]
If only there was an equivalent to this in VBA:

Thanks, I can reproduce the issue. - As I guessed from the start, VBA has nothing to do with it whatsoever.

The problem is that Access and/or the ODBC-Driver fail to properly treat the criterion as Nvarchar. The SQL command arriving at SQL Server is:
CODE
SELECT "dbo"."tblUnits"."ID" FROM "dbo"."tblUnits" WHERE ("unit" = 'k Ω' )

Observe: Your Unicode character made it just fine to SQL Server.
The problem is, the criterion is not denominated correctly as Nvarchar. SQL Server will convert it down to a plain Varchar and that's were your precious Ω gets mangled.

QUOTE
I have tried using :

Pass through query
adodb recordsets
odbc connections

Try again! ;-)

ADODB Recordsets
Work! However, you either need to denominate your criterion explicitly as Nvarchar in the SQL String:
SQL = "Select * From tblUnits Where unit = N'" & Unit & "';"
Or you use a parameterized query and use an adVarWChar parameter data type.

Pass-Through-Query
I'm sure they work too with an explicit Nvarchar criterion.

ODBC Connections
? - You mean linked tables? Yeah, that's where it falls apart, as explained above.
It's quite disappointing that Access appears to be unable to handle Unicode correctly in this case. Unfortunately, I wasn't able to find any quick workaround.

Posted by: t.heisler Aug 22 2019, 07:40 AM

Yea when I started testing this I didn't know about the N prefix.

That combined with ADODB record set seems to do the trick.

Thanks for the help!