VBA: SQL Query in Translation Function

J

JEH

I am working on an MS Access database which takes names type and Arabic and
translates them into English but am stumped by an inexplicable failure to
solve what appears to be a relatively simple problem.

The database consists of two tables:
tblMAIN, which holds the Arabic names and other personnel data
ICS Naming Standards, which holds the Arabic names and their English
translations.

I am using a function called ICS LookUp(), to pass an arabic string
(strName_A) to an SQL Query which executes an SQL query on an ADODB recordset
from the ICS Naming Standards table to return the English translation. The
SQL query, which works fine as an MS Access query, fails to find the
corresponding record in the ICS table.

Public Function ICS_LookUp(strName_A As Variant) As Variant

On Error GoTo Err_ICS_LookUp

Dim adoRst As ADODB.Recordset ' Recordset for Arabic name
look up
Dim strSQL As String

strName_A = Trim(strName_A)

' Translate
strSQL = "SELECT [ICS Naming Standards].Arabic, [ICS Naming
Standards].ICS FROM [ICS Naming Standards] WHERE ((([ICS Naming
Standards].Arabic)= '" & strName_A & "' ))"

' Lookup Arabic Value (Arabic) in ICS table and return English translation
(ICS)

Set adoRst = New ADODB.Recordset
adoRst.ActiveConnection = CurrentProject.Connection
adoRst.Open strSQL, , adOpenDynamic, adLockOptimistic
ICS_LookUp = adoRst("ICS")
adoRst.Close
Set adoRst = Nothing

Exit_ICS_LookUp:
Exit Function

Err_ICS_LookUp:
ICS_LookUp = "[Not Found]"
MsgBox Err.Description
Resume Exit_ICS_LookUp

End Function


I suspect that the issue has something to do which the way in which SQL
handles foreign languages, specifically Arabic, versus how MS Access handles
the same. An MS Access query immediately returns the correct English value.

Any suggestions?

JEH
 
J

JEH

Alex,

Here are the immediate results of the function, where "ÇÃãÃ" is an Arabic
word known to be in the table ICS Naming Standards....

ICS_LookUp("ÇÃãÃ")
SELECT [ICS Naming Standards].Arabic, [ICS Naming Standards].ICS FROM [ICS
Naming Standards] WHERE ((([ICS Naming Standards].Arabic)= 'ÇÃãÃ' ))

Error Message: "Either BOF or EOF is True, or the current record has been
deleted. Requested operation requires a current record." [Means that the
record was not found, right?]

When I pasted the SQL statement into the SQL window, Access curiously
changed the Arabic script to the same non-Arabic 'ÇÃãÃ'. Naturally, the
query failed to return any results. When I went back into the ICS table and
copied a known arabic word and replaced "ÇÃãÃ" with it (it pastes in as
arabic script rather than the western unicode version), the query
successfully returned a result. Therefore, the problem probably lies in how
MS Access VBA handles foreign scripts with SQL queries.

Any suggestions?

JEH



Alex Dybenko said:
Hi,
before opening recordset add
debug.print strSQL
and then paste result in a query - will it return any value?


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


JEH said:
I am working on an MS Access database which takes names type and Arabic and
translates them into English but am stumped by an inexplicable failure to
solve what appears to be a relatively simple problem.

The database consists of two tables:
tblMAIN, which holds the Arabic names and other personnel data
ICS Naming Standards, which holds the Arabic names and their English
translations.

I am using a function called ICS LookUp(), to pass an arabic string
(strName_A) to an SQL Query which executes an SQL query on an ADODB
recordset
from the ICS Naming Standards table to return the English translation.
The
SQL query, which works fine as an MS Access query, fails to find the
corresponding record in the ICS table.

Public Function ICS_LookUp(strName_A As Variant) As Variant

On Error GoTo Err_ICS_LookUp

Dim adoRst As ADODB.Recordset ' Recordset for Arabic name
look up
Dim strSQL As String

strName_A = Trim(strName_A)

' Translate
strSQL = "SELECT [ICS Naming Standards].Arabic, [ICS Naming
Standards].ICS FROM [ICS Naming Standards] WHERE ((([ICS Naming
Standards].Arabic)= '" & strName_A & "' ))"

' Lookup Arabic Value (Arabic) in ICS table and return English translation
(ICS)

Set adoRst = New ADODB.Recordset
adoRst.ActiveConnection = CurrentProject.Connection
adoRst.Open strSQL, , adOpenDynamic, adLockOptimistic
ICS_LookUp = adoRst("ICS")
adoRst.Close
Set adoRst = Nothing

Exit_ICS_LookUp:
Exit Function

Err_ICS_LookUp:
ICS_LookUp = "[Not Found]"
MsgBox Err.Description
Resume Exit_ICS_LookUp

End Function


I suspect that the issue has something to do which the way in which SQL
handles foreign languages, specifically Arabic, versus how MS Access
handles
the same. An MS Access query immediately returns the correct English
value.

Any suggestions?

JEH
Click to expand...
Click to expand...
 
A

Alex Dybenko

ok,
then try the following:
open control panel-regional and language options-advanced
and set language for non-unicode programs to one, which match to your Arabic


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


JEH said:
Alex,

Here are the immediate results of the function, where "ÇÃãÃ" is an Arabic
word known to be in the table ICS Naming Standards....

ICS_LookUp("ÇÃãÃ")
SELECT [ICS Naming Standards].Arabic, [ICS Naming Standards].ICS FROM [ICS
Naming Standards] WHERE ((([ICS Naming Standards].Arabic)= 'ÇÃãÃ' ))

Error Message: "Either BOF or EOF is True, or the current record has been
deleted. Requested operation requires a current record." [Means that the
record was not found, right?]

When I pasted the SQL statement into the SQL window, Access curiously
changed the Arabic script to the same non-Arabic 'ÇÃãÃ'. Naturally, the
query failed to return any results. When I went back into the ICS table
and
copied a known arabic word and replaced "ÇÃãÃ" with it (it pastes in as
arabic script rather than the western unicode version), the query
successfully returned a result. Therefore, the problem probably lies in
how
MS Access VBA handles foreign scripts with SQL queries.

Any suggestions?

JEH



Alex Dybenko said:
Hi,
before opening recordset add
debug.print strSQL
and then paste result in a query - will it return any value?


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com


JEH said:
I am working on an MS Access database which takes names type and Arabic
and
translates them into English but am stumped by an inexplicable failure
to
solve what appears to be a relatively simple problem.

The database consists of two tables:
tblMAIN, which holds the Arabic names and other personnel data
ICS Naming Standards, which holds the Arabic names and their English
translations.

I am using a function called ICS LookUp(), to pass an arabic string
(strName_A) to an SQL Query which executes an SQL query on an ADODB
recordset
from the ICS Naming Standards table to return the English translation.
The
SQL query, which works fine as an MS Access query, fails to find the
corresponding record in the ICS table.

Public Function ICS_LookUp(strName_A As Variant) As Variant

On Error GoTo Err_ICS_LookUp

Dim adoRst As ADODB.Recordset ' Recordset for Arabic
name
look up
Dim strSQL As String

strName_A = Trim(strName_A)

' Translate
strSQL = "SELECT [ICS Naming Standards].Arabic, [ICS Naming
Standards].ICS FROM [ICS Naming Standards] WHERE ((([ICS Naming
Standards].Arabic)= '" & strName_A & "' ))"

' Lookup Arabic Value (Arabic) in ICS table and return English
translation
(ICS)

Set adoRst = New ADODB.Recordset
adoRst.ActiveConnection = CurrentProject.Connection
adoRst.Open strSQL, , adOpenDynamic, adLockOptimistic
ICS_LookUp = adoRst("ICS")
adoRst.Close
Set adoRst = Nothing

Exit_ICS_LookUp:
Exit Function

Err_ICS_LookUp:
ICS_LookUp = "[Not Found]"
MsgBox Err.Description
Resume Exit_ICS_LookUp

End Function


I suspect that the issue has something to do which the way in which SQL
handles foreign languages, specifically Arabic, versus how MS Access
handles
the same. An MS Access query immediately returns the correct English
value.

Any suggestions?

JEH
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update table using textboxes from form 0
SQL QUERY in FUNCTION returns 0 value 1
Creating a select query using sql in VBA 6
VBA coding question.... Need expert help 0
VBA code issue...Would love expert help. 5
VBA - query reading newly added field as null 2
Trouble with SQL String in VBA 3
Translate SQL query 3

Top