Pass-Through Query Problem RRS feed

  • Question

  • I have an Access application I designed over 10 years ago and has been in constant use since then.  I recently received a replacement laptop running Windows 7 and Office 2010.  The application uses a few pass-through queries which access data stored in an Oracle database.  This is mostly chemistry results and there are units of measure micrograms per liter (µg/L).  If I bypass the menu forms and just open the query directly, the units show correctly and any work done in the menu forms after that will produce tables with the correct units as shown.

    If, however, I start using the menu forms first (which I should be able to do), the "µ" character is replaced by "?" and the tables come out wrong.  At this point, if I open the query itself, the units will be "?g/L" and nothing I can do other than closing the application will help me to correct the problem.  The query itself has not changed and is not the problem.  If I re-open the application and open the query directly, the units will be correct again.  Within the initial menu form, I've been able to pin down the problem to the Open statement below:

        With cnnOracle
            .ConnectionTimeout = 60
            .Open "Provider=OraOLEDB.Oracle;Data Source=" & _
                txtSERVER & ";User Id=" & _
                txtUser & ";Password=" & _
        End With

    The connection object is declared as a public variable in another area of the code:

       Public cnnOracle As New ADODB.Connection

    I also tried upgrading the database from Access 2000/2003 to 2010 with no fix.  Can you help?



    Wednesday, May 7, 2014 8:40 PM


  • Make sure you use a Unicode-aware font to display the unit, for example Arial Unicode.

    -Tom. Microsoft Access MVP

    • Marked as answer by Marvin_Guo Friday, May 23, 2014 1:47 AM
    Thursday, May 8, 2014 2:05 AM