Announcement

Collapse
No announcement yet.

MS Access and Unicode

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • MS Access and Unicode

    I have been trying to troubleshoot a problem for a SQL Tools customer, and I feel like I am spinning my wheels. He has an mdb file that contains wide-character data (for example I see a Chinese glyph when I look at the data in the Access development environment) but we can't seem to retrieve it with SQL_ResColWString. And when trying to UPDATE a field (using a bound ? character), the wrong characters get inserted.

    Is there anybody out there with recent experience with this combination: Microsoft Access, mdb file, Access 2000 file format, ODBC, reading/writing wide strings.

    -- Eric

    "Not my circus, not my monkeys."

  • #2
    Guess that SQL_ResColWString calls SQLGetData, but I don't know how you are calling the later.
    Forum: http://www.jose.it-berater.org/smfforum/index.php

    Comment


    • #3
      Yeah, I should have been more specific. Long day.

      SQL Tools has been working fine for (literally) years, happily retrieving Wide strings from various DBMSs. At least I have never had any reports of problems until now. For some reason I can't seem to get it to work in this case. I think I am just of of practice or I am forgetting some kind of Access-specific quirk. I will post more details tomorrow after I have had some sleep, but in the meantime I'm hoping that somebody will post something like "With Access you have to remember to..." and I will slap my forehead.

      No, not SQLGetData, just plain old SQLFetch on a VARCHAR(32) column. In Access I see the data is a Chinese glyph plus the letter A, but the buffer says "?A". Obviously an ANSI/Unicode issue but I can't find it.
      "Not my circus, not my monkeys."

      Comment


      • #4
        It could have something to do with "Unicode Compression"

        Look at the problem text field's properties in table design and see if this is turned on?

        http://www.grahamwideman.com/gw/tech...s2kunicode.htm

        Comment


        • #5
          Also take a look at the ODBC driver version. Problem with strings in Access 2000 and early ODBC versions discussed here:

          https://groups.google.com/forum/#!to...de/0AbwnUOjcQ4

          I had the following experiement : on a site, most of the field were seen as
          twice their actual size from the BDE, even if the "Unicode compression" was
          activate in the Access table.
          This caused a string field of 200 characters to be seen as a memo.

          On my own computer, all the fields were seen with the proper size. This time
          the 200 chars field was seen as a StringField...

          The difference ? Just that my computer had the latest ODBC dll and drivers,
          where as the other were out-dated.

          Comment


          • #6
            Obviously an ANSI/Unicode issue but I can't find it.
            Mmm,,, maybe.

            Silly question, but is that what the data are SUPPOSED to be? The chinese glyph plus the letter 'A' ?? That would be a weird data item if you asked me.

            Maybe you are doing everything perfectly but the data itself are corrupt?

            {ADDED]
            MS-Access is not known as the paragon of data integrity. That's why MS markets SQL/Server (which is pretty good).
            Last edited by Michael Mattias; 2 Jul 2020, 10:44 AM. Reason: Added the "[ADDED}"
            Michael Mattias
            Tal Systems Inc. (retired)
            Racine WI USA
            [email protected]
            http://www.talsystems.com

            Comment


            • #7
              > weird data item

              That is a shortened version of the test string supplied by the customer.
              "Not my circus, not my monkeys."

              Comment


              • #8
                Originally posted by Eric Pearson View Post
                No, not SQLGetData, just plain old SQLFetch on a VARCHAR(32) column. In Access I see the data is a Chinese glyph plus the letter A, but the buffer says "?A". Obviously an ANSI/Unicode issue but I can't find it.
                Where is the VARCHAR() specified. In Access, a TEXT field is WSTRING
                VARCHAR() is usually single byte, NVARCHAR() is Wide. (or in ODBC: SQL_wVARCHAR not SQL_VARCHAR)

                Comment


                • #9
                  This is a report on an an 'MDB 'file via ODBC..

                  Code:
                  Connection String Attributes:
                  DSN=CLOYES_TPPC
                  DBQ=D:\Clients\Cloyes Gear\NEW_EDI_PC_MARCH_2010\Tppc.mdb
                  DriverId=281
                  FIL=MS Access
                  MaxBufferSize=2048
                  PageTimeout=5
                  UID=admin
                  
                  
                  Data Source Name :CLOYES_TPPC
                  Database Name :D:\Clients\Cloyes Gear\NEW_EDI_PC_MARCH_2010\Tppc
                  DBMS Name :ACCESS
                  DBMS Version :03.50.0000
                  Driver Name :odbcjt32.dll
                  Driver ODBC Ver :03.51
                  Environment ODBC Ver : 3(ODBC 3)
                  
                  Supported Capabilities
                  Transactions (DML) : Y
                  CREATE TABLE : Y
                  DROP TABLE : Y
                  ALTER TABLE : N
                  CREATE INDEX : Y
                  DROP INDEX : Y
                  Nullable Columns : N
                  Max Table Name Size : 64
                  Max Column Name Size : 64
                  Max Concurrent Stmt : 0 (Unable to determine)
                  Datatypes Supported
                  
                  DATA_TYPE SQL Type DBMS Literal Column Size MinScale MaxScale CreateParams
                  --------- -------- ------------ ----------- --------- -------- ------------
                  -11 -11 GUID 36 0 0
                  -7 -7 BIT 1 0 0
                  -6 -6 BYTE 3 0 0
                  -4 -4 LONGBINARY 1073741823 0 0
                  -3 -3 VARBINARY 255 0 0 length
                  -2 -2 BINARY 255 0 0 length
                  -1 -1 LONGCHAR 2147483647 0 0
                  1 1 CHAR 255 0 0 length
                  2 2 CURRENCY 19 4 4
                  4 4 INTEGER 10 0 0
                  4 4 COUNTER 10 0 0
                  5 5 SMALLINT 5 0 0
                  7 7 REAL 24 0 0
                  8 8 DOUBLE 53 0 0
                  12 12 VARCHAR 255 0 0 length
                  93 9 DATETIME 19 0 0
                  
                  *** END OF REPORT ***
                  I see no references to differences between WideChar and SBCS... I wonder if there might not be a "DBMS Version" issue?

                  Or ...

                  I think I might run an SQLColumns on a 'SELECT ' statement to see what data type the driver is seeing when it tries to return the column. Maybe you are not getting back a VARCHAR but instead one of the other highlighted types, requiring some alternate conversion. .

                  But you have been doing this so long I have to consider the data suspect. Maybe its something as simple as using a different code page for the WideChar data.


                  MCM

                  Eric if you want the program (source (PB Win) and EXE) which created this report drop me a note. It will save you some work
                  Michael Mattias
                  Tal Systems Inc. (retired)
                  Racine WI USA
                  [email protected]
                  http://www.talsystems.com

                  Comment


                  • #10
                    Originally posted by Stuart McLachlan View Post
                    Where is the VARCHAR() specified. In Access, a TEXT field is WSTRING
                    VARCHAR() is usually single byte, NVARCHAR() is Wide. (or in ODBC: SQL_wVARCHAR not SQL_VARCHAR)
                    Justt to expand. Access 2000 was the first version to store text as WSTRING in an mdb . So it is likely that It will require SQL_wVARCHAR while a "97" file with the same extension will not.

                    Note that Office 2013 and up can't open an Access97 mdb but it can open an Access 2000 mdb !
                    (A problem encountered with legacy systems where the FE has been updated ,but the BE is still an old Access 97 mdb. They suddenly fail when the user is updated to Office 2013 or above)

                    Comment


                    • #11
                      Originally posted by Michael Mattias View Post
                      Maybe its something as simple as using a different code page for the WideChar data.
                      WideChar/UTF-16 doesn't use code pages. It uses Unicode code points.

                      Comment


                      • #12
                        Originally posted by Michael Mattias View Post
                        This is a report on an an 'MDB 'file via ODBC..
                        What version .mdb? 97 or 2000 ?

                        Edit:
                        No need to answer that, I looked it up. It's Access 97.

                        Ver 3.5 is Access 97. Access 2000 is Ver 4

                        Can you run your report on an Access 2000 format file and report whether there is a change in the "CHAR", "LONGCHAR" and "VARCHAR" entries?

                        Comment


                        • #13
                          Can you run your report on an Access 2000 format file and report whether there is a change in the "CHAR", "LONGCHAR" and "VARCHAR" entries?
                          That can be run on any ODBC data source. I have saved reports on Access (multiple versions), MySql, Paradox MS SQL/Server and several others.

                          HIghest version of Access I have report for is 4.0:
                          Code:
                          06-04-2001 ODBC DRIVER/DBMS REPORT 15:19:18
                          
                          Connection String Attributes:
                          DSN=RA206
                          DBQ=C:\Software_Development\Testdata\remitaid\ra206.mdb
                          DriverId=25
                          FIL=MS Access
                          MaxBufferSize=2048
                          PageTimeout=5
                          UID=admin
                          
                          
                          Data Source Name:RA206
                          Database Name :C:\Software_Development\Testdata\remitaid\ra206
                          DBMS Name :ACCESS
                          DBMS Version :04.00.0000
                          Driver Name :ODBCJT32.DLL
                          Driver ODBC Ver :03.51
                          
                          Supported Capabilities
                          Transactions (DML) : Y
                          CREATE TABLE : Y
                          DROP TABLE : Y
                          ALTER TABLE : N
                          CREATE INDEX : Y
                          DROP INDEX : Y
                          Nullable Columns : N
                          Max Concurrent Stmt: 0 (Unable to determine)
                          
                          Datatypes Supported
                          SQL Type DBMS Literal Column Size
                          -------- ------------ -----------
                          -11 GUID 36
                          -7 BIT 1
                          -6 BYTE 3
                          -4 LONGBINARY 1073741823
                          -3 VARBINARY 510
                          -2 BINARY 510
                          -1 LONGCHAR 1073741823
                          1 CHAR 255
                          2 CURRENCY 19
                          4 INTEGER 10
                          4 COUNTER 10
                          5 SMALLINT 5
                          7 REAL 7
                          8 DOUBLE 15
                          9 DATETIME 19
                          12 VARCHAR 255
                          
                          *** END OF REPORT ***
                          It is using the "SqlGetTypeInfo()" function from the ODBC API. If you are an SQL Tools user I am certain Eric would have included a function to do this (report all the data types supported by the DBMS/Driver combo) .

                          it's not such a big deal to me anymore, as I have begun using OLE Access with ADO for DML and ADOX for catalog info. But when I started programming DBs, PB had no native support (VARIANT data types and a COM Interface) for OLE but now it does and I have found OLE (ADO) more reliable than ODBC. YMMV.

                          MCM
                          Michael Mattias
                          Tal Systems Inc. (retired)
                          Racine WI USA
                          [email protected]
                          http://www.talsystems.com

                          Comment


                          • #14
                            MM,
                            Thanks for that, your report appears to rule out A2000 and A97 andSQL_wVARCHAR v SQL_VARCHAR as the problem.

                            Comment


                            • #15
                              Originally posted by Michael Mattias View Post

                              {ADDED]
                              MS-Access is not known as the paragon of data integrity. That's why MS markets SQL/Server (which is pretty good).
                              That may have been true for the first 16-bit Access versions. But I and my users used Access for decades and if there were issues with data integrity it could always be traced back to something outside of Access, e.g. user error, programmer's fault (aka "bug"), hardware/network issues.

                              Comment


                              • #16
                                Originally posted by Knuth Konrad View Post

                                That may have been true for the first 16-bit Access versions. But I and my users used Access for decades and if there were issues with data integrity it could always be traced back to something outside of Access, e.g. user error, programmer's fault (aka "bug"), hardware/network issues.
                                I second that - and I've got quite few large Access applications out there with clients that have been running for many years.

                                Comment


                                • #17
                                  [Data integrity problems] may have been true for the first 16-bit Access versions.
                                  Sayeth the famous writer Billy from Stratford-on-Avon England:..

                                  The evil men do lives after them,
                                  The good is oft interred with the bones.
                                  I guess the modern version would be, "If that first impression is bad, it won't be forgotten for a very long time."

                                  MCM
                                  Michael Mattias
                                  Tal Systems Inc. (retired)
                                  Racine WI USA
                                  [email protected]
                                  http://www.talsystems.com

                                  Comment


                                  • #18
                                    Originally posted by Michael Mattias View Post

                                    Sayeth the famous writer Billy from Stratford-on-Avon England:..
                                    That's "Stratford-Upon-Avon" if you're talking about Bill's town. (Stratford-on-Avon" is the district it's in)

                                    Comment

                                    Working...
                                    X