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.

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

when we load the ASP page we see

If we add a new entry for a Russian word

and click "Submit" the page will refresh with

and if we check the table in Access we see

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).

Important Note

Recommended topics

Hot tags