UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Using Unicode Characters In Vba - Help, SQL Server 2012    
 
   
t.heisler
post Aug 16 2019, 10:35 AM
Post#1



Posts: 8
Joined: 28-January 19



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,

Go to the top of the page
 
GroverParkGeorge
post Aug 16 2019, 12:11 PM
Post#2


UA Admin
Posts: 36,084
Joined: 20-June 02
From: Newcastle, WA


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.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
t.heisler
post Aug 16 2019, 12:38 PM
Post#3



Posts: 8
Joined: 28-January 19



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.



This post has been edited by t.heisler: Aug 16 2019, 12:39 PM
Go to the top of the page
 
GroverParkGeorge
post Aug 16 2019, 12:41 PM
Post#4


UA Admin
Posts: 36,084
Joined: 20-June 02
From: Newcastle, WA


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.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Aug 16 2019, 12:42 PM
Post#5


UA Admin
Posts: 36,084
Joined: 20-June 02
From: Newcastle, WA


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.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
PhilS
post Aug 17 2019, 10:39 AM
Post#6



Posts: 640
Joined: 26-May 15
From: The middle of Germany


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.

--------------------
Go to the top of the page
 
t.heisler
post Aug 18 2019, 09:06 AM
Post#7



Posts: 8
Joined: 28-January 19



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
Go to the top of the page
 
t.heisler
post Aug 18 2019, 09:30 AM
Post#8



Posts: 8
Joined: 28-January 19



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.
Go to the top of the page
 
PhilS
post Aug 18 2019, 12:01 PM
Post#9



Posts: 640
Joined: 26-May 15
From: The middle of Germany


Hands off collations for a minute!

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

--------------------
Go to the top of the page
 
t.heisler
post Aug 18 2019, 12:47 PM
Post#10



Posts: 8
Joined: 28-January 19



Yep, nVarchar(50)
This post has been edited by t.heisler: Aug 18 2019, 01:03 PM
Go to the top of the page
 
t.heisler
post Aug 20 2019, 07:55 PM
Post#11



Posts: 8
Joined: 28-January 19



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.
Go to the top of the page
 
PhilS
post Aug 21 2019, 04:47 AM
Post#12



Posts: 640
Joined: 26-May 15
From: The middle of Germany


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?

--------------------
Go to the top of the page
 
t.heisler
post Aug 21 2019, 06:16 PM
Post#13



Posts: 8
Joined: 28-January 19



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/1002503...-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

This post has been edited by t.heisler: Aug 21 2019, 06:23 PM
Attached File(s)
Attached File  unicode_test.zip ( 48.34K )Number of downloads: 2
 
Go to the top of the page
 
PhilS
post Aug 22 2019, 06:17 AM
Post#14



Posts: 640
Joined: 26-May 15
From: The middle of Germany


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.

--------------------
Go to the top of the page
 
t.heisler
post Aug 22 2019, 07:40 AM
Post#15



Posts: 8
Joined: 28-January 19



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!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    21st November 2019 - 12:02 PM