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

Question

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 use UTF-8 encoding throughout, which is what 'Codepage = 65001' does, then if you input Cyrillic with a form, it will be it will be output as Cyrillic on your webpages, but it may look strange within Access, eg "Ласкаво просимо до Львова" might look like "Ëàñêàâî ïðîñèìî äî Ëüâîâà" The issue is, can you use UTF-8 within Access. This question may help - http://stackoverflow.com/questions/5681078/utf-8-character-encoding-in-sql - alternatively, you could look at setting up a Classic ASP page to export an Excel spreadsheet — John, Feb 27 '14 at 11:59
@John As long as they insert the data as `adVarWChar` ADO should handle the conversion to unicode. — user692942, May 03 '14 at 17:37

Gord Thompson · Accepted Answer · 2017-01-02T16:34:52.603

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

Slight typo should be `` it is missing the `@` [ASP processing directive](http://msdn.microsoft.com/en-us/library/ms524720(v=vs.90).aspx). — user692942, May 03 '14 at 17:03
@Lankymart Thanks very much for the correction! I have updated my answer. — Gord Thompson, May 03 '14 at 17:30

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

1 Answers1

Important Note

Linked