Search code examples
ms-accessvbscriptasp-classicms-access-2007cyrillic

Capture and insert Unicode text (Cyrillic) into MS access database

I inherited an old web application, that is writing data collected in a form into an Access 2007 database using classic ASP.

Now they need it to be able to collect input in the Cyrillic alphabet.

I'm completely unfamiliar with code page/ char sets, and working with non latin alphabets.

I've tried altering the charset on the entry form page to ISO-8859-1 , which does seem to store the ascii value of the characters (eg: #1076;). So that is interpreted and read by the browser fine, but is pretty much useless in terms of then exporting that data into excel to pass around to the departments that need it.

So my question is:

Is there an easy way to capture Cyrillic characters from the web form and insert them as Cyrillic characters into my access table?

or alternately

Is there a tool or setting within the access database that can convert the decimal values (#1076;) into Cyrillic characters within access itself.

Solution

  • If you stick with UTF-8 for your pages they should work (but see the Important Note below). While it is true that Access does not store Unicode characters internally as UTF-8 the Access OLEDB driver will take care of the conversions for you.

    Consider the following sample script (where 65001 is the "code page" for UTF-8):

    <%@ CODEPAGE = 65001 %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8" />
<title>Classic ASP Unicode Test</title>
</head>
<body bgcolor="white" text="black">
<%
Dim con, cmd, rst
Const adVarWChar = 202
Const adParamInput = 1
Set con = CreateObject("ADODB.Connection")
con.Mode = 3  ' adModeReadWrite
con.Open _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\_wwwdata\unicodeTest.mdb;"
If Len(Trim(Request.Form("word"))) > 0 Then
    Set cmd = CreateObject("ADODB.Command")
    cmd.ActiveConnection = con
    cmd.CommandText = "INSERT INTO vocabulary (word, language, english_equiv) VALUES (?,?,?)"
    cmd.Parameters.Append cmd.CreateParameter("?", adVarWChar, adParamInput, 255, Request.Form("word"))
    cmd.Parameters.Append cmd.CreateParameter("?", adVarWChar, adParamInput, 255, Request.Form("language"))
    cmd.Parameters.Append cmd.CreateParameter("?", adVarWChar, adParamInput, 255, Request.Form("english_equiv"))
    cmd.Execute
    Set cmd = Nothing
End If
%>
<h2>Word list:</h2>
<table border=1>
    <tr>
        <th>word</th><th>language</th><th>english_equiv</th>
    </tr>
<%
Set rst = CreateObject("ADODB.Recordset")
rst.Open _
        "SELECT * FROM vocabulary ORDER BY ID", _
        con, 3, 3
Do Until rst.EOF
    Response.Write "<tr>"
    Response.Write "<td>" & rst("word").Value & "</td>"
    Response.Write "<td>" & rst("language").Value & "</td>"
    Response.Write "<td>" & rst("english_equiv").Value & "</td>"
    Response.Write "</tr>"
    rst.MoveNext
Loop
Response.Write "</table>"
rst.Close
Set rst = Nothing
con.Close
Set con = Nothing
%>
<h2>Add a new entry:</h2>
<form action="<% Response.Write Request.ServerVariables("SCRIPT_NAME") %>" method="POST">
<table>
    <tr>
        <td align="right">word:</td>
        <td><input type="text" name="word"></td>
    </tr>
    <tr>
        <td align="right">language:</td>
        <td><input type="text" name="language"></td>
    </tr>
    <tr>
        <td align="right">english_equiv:</td>
        <td><input type="text" name="english_equiv"></td>
    </tr>
    <tr>
        <td></td>
        <td align="center"><input type="submit" value="Submit"></td>
    </tr>
</table>
</body>
</html>

    Starting with a table named [vocabulary] in the Access database

    AccessTableBefore.png

    when we load the ASP page we see

            AspPage1.png

    If we add a new entry for a Russian word

            AspPage2.png

    and click "Submit" the page will refresh with

            AspPage3.png

    and if we check the table in Access we see

    AccessTableAfter.png

    Important Note

    Be aware that you should NOT be using an Access database as a back-end data store for a web application; Microsoft strongly recommends against doing so (ref: here).